November 19, 2008 at 12:23 am
Hi Guys/Girls
i have this sp:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE LrandomiseData --'cALLDISPOSITION', 'CALLDISPOSITIONID', NULL, null
@tablename VARCHAR(50),
@primarykeyname VARCHAR(50),
@subquery NVARCHAR(4000) = NULL
AS
BEGIN
IF OBJECT_ID('tempdb..#temp3', 'U') IS NULL
BEGIN
DECLARE @temp TABLE
(
id BIGINT IDENTITY(1, 1),
primarykey VARCHAR(50) NOT NULL
UNIQUE
)
IF @subquery IS NULL
BEGIN
--use standard select logic
INSERT INTO @temp ( primarykey )
EXEC
( 'SELECT DISTINCT ' + @primarykeyname
+ ' FROM ' + @tablename
)
END
ELSE
BEGIN
INSERT INTO @temp ( primarykey )
EXEC ( @subquery
)
--use subquery: what ever the subquery is it must only return one column that is the primary key....
END
DECLARE @temp2 TABLE
(
id BIGINT IDENTITY(1, 1),
primarykey VARCHAR(50) NOT NULL
UNIQUE
)
DECLARE @counter BIGINT
SET @counter = 1
DECLARE @maxid BIGINT
SET @maxid = ( SELECT MAX(id)
FROM @temp
)
DECLARE @randomid INT
DECLARE @primarykey-2 VARCHAR
WHILE @counter <= @maxid
BEGIN
SET @randomid = CONVERT(BIGINT, RAND() * ROUND(@maxid, LEN(@maxid) + 1 * -1))
SET @primarykey-2 = ( SELECT primarykey
FROM @temp
WHERE id = @randomid
)
IF @primarykey-2 IS NOT NULL
BEGIN
IF NOT EXISTS ( SELECT primarykey
FROM @temp2
WHERE primarykey = @primarykey-2 )
BEGIN
INSERT INTO @temp2 ( primarykey )
VALUES ( @primarykey-2 )
SET @counter = @counter + 1
END
END
END
CREATE TABLE #temp3
(
id BIGINT,
primarykey VARCHAR(50) NOT NULL
UNIQUE
)
INSERT INTO #temp3 ( id, primarykey )
SELECT id,
primarykey
FROM @temp2
WHERE primarykey NOT IN ( SELECT primarykey
FROM #temp3 )
END
--RETURN THE RANDOMISED RECORDS AS A TEMP COPY OF THE TABLE IN TABLENAME
EXEC
( 'SELECT a.* INTO ##RANDOMISEDDATA FROM ' + @tablename
+ ' a JOIN #temp3 b ON a.' + @primarykeyname
+ ' = b.primarykey ORDER BY b.id'
)
DROP TABLE #temp3
SELECT *
FROM ##RANDOMISEDDATA
END
GO
and i cant understand why it is so slow....
perhaps someone can help.
Thanks
Chris
November 19, 2008 at 1:04 am
Chris Morton (11/19/2008)
Hi Guys/Girlsi have this sp:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:????????
-- Create date:
-- Description:????
-- =============================================
CREATE PROCEDURE LrandomiseData --'cALLDISPOSITION', 'CALLDISPOSITIONID', NULL, null
@tablename VARCHAR(50),
@primarykeyname VARCHAR(50),
@subquery NVARCHAR(4000) = NULL
AS
BEGIN
IF OBJECT_ID('tempdb..#temp3', 'U') IS NULL
BEGIN
DECLARE @temp TABLE
(
id BIGINT IDENTITY(1, 1),
primarykey VARCHAR(50) NOT NULL
UNIQUE
)
IF @subquery IS NULL
BEGIN
--use standard select logic
INSERT INTO @temp ( primarykey )
EXEC
( 'SELECT DISTINCT ' + @primarykeyname
+ ' FROM ' + @tablename
)
END
ELSE
BEGIN
INSERT INTO @temp ( primarykey )
EXEC ( @subquery
)
--use subquery: what ever the subquery is it must only return one column that is the primary key....
END
DECLARE @temp2 TABLE
(
id BIGINT IDENTITY(1, 1),
primarykey VARCHAR(50) NOT NULL
UNIQUE
)
DECLARE @counter BIGINT
SET @counter = 1
DECLARE @maxid BIGINT
SET @maxid = ( SELECT MAX(id)
FROM @temp
)
DECLARE @randomid INT
DECLARE @primarykey-2 VARCHAR
WHILE @counter <= @maxid
BEGIN
SET @randomid = CONVERT(BIGINT, RAND() * ROUND(@maxid, LEN(@maxid) + 1 * -1))
SET @primarykey-2 = ( SELECT primarykey
FROM @temp
WHERE id = @randomid
)
IF @primarykey-2 IS NOT NULL
BEGIN
IF NOT EXISTS ( SELECT primarykey
FROM @temp2
WHERE primarykey = @primarykey-2 )
BEGIN
INSERT INTO @temp2 ( primarykey )
VALUES ( @primarykey-2 )
SET @counter = @counter + 1
END
END
END
CREATE TABLE #temp3
(
id BIGINT,
primarykey VARCHAR(50) NOT NULL
UNIQUE
)
INSERT INTO #temp3 ( id, primarykey )
SELECT id,
primarykey
FROM @temp2
WHERE primarykey NOT IN ( SELECT primarykey
FROM #temp3 )
END
--RETURN THE RANDOMISED RECORDS AS A TEMP COPY OF THE TABLE IN TABLENAME
EXEC
( 'SELECT a.* INTO ##RANDOMISEDDATA FROM ' + @tablename
+ ' a JOIN #temp3 b ON a.' + @primarykeyname
+ ' = b.primarykey ORDER BY b.id'
)
DROP TABLE #temp3
SELECT *
FROM ##RANDOMISEDDATA
END
GO
and i cant understand why it is so slow....
perhaps someone can help.
Thanks
Chris
Why are you using so many temporary tables? From a first look (sorry I don’t have time to analyze the code for a longer time) it seems that there is no need to create most of the temporary tables and there is no need to run in a loop. You can use the row_number() function and order the records by newid() function to return the data in randomized order.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 19, 2008 at 1:14 am
ok that sound good. will try that.
Thanks
November 19, 2008 at 7:06 am
this is super simple method of getting the same results:
SELECT * from customer order by newid()
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply