December 1, 2016 at 9:18 am
Hi,
i have the following code:
WHILE (SELECT COUNT(*) FROM tblSKUGrown) < 1000
BEGIN
INSERT INTO tblSKUGrown (SKU, OL)
(SELECT CONCAT(x.SKU,'_n'), x.OL FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY s.OL desc) AS Row,
s.SKU,
s.OL
FROM RAW_skus s
)x
WHERE x.[Row] = ROUND(RAND()*(12000-1)+1,0))
END
what the above is doing is uses a RAW_skus table that has 12000 skusIDs, selects a random number between 1-12000, finds that Row and inserts it into tblSKUGrown, with the sku id followed by "_n", to indicate its a new copied sku. this will continue until the count of 1000 is reached.
the result is that tblSKUGrown will have 1000 copied skus from the RAW_skus table.
when i run the procedure it takes around 25 seconds.
is there a way to speed the inserts, and bring the time down? 1000 is just a test but the real number is adding 50% skus, so 6000.
December 1, 2016 at 9:52 am
Talvin Singh (12/1/2016)
Hi,i have the following code:
WHILE (SELECT COUNT(*) FROM tblSKUGrown) < 1000
BEGIN
INSERT INTO tblSKUGrown (SKU, OL)
(SELECT CONCAT(x.SKU,'_n'), x.OL FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY s.OL desc) AS Row,
s.SKU,
s.OL
FROM RAW_skus s
)x
WHERE x.[Row] = ROUND(RAND()*(12000-1)+1,0))
END
what the above is doing is uses a RAW_skus table that has 12000 skusIDs, selects a random number between 1-12000, finds that Row and inserts it into tblSKUGrown, with the sku id followed by "_n", to indicate its a new copied sku. this will continue until the count of 1000 is reached.
the result is that tblSKUGrown will have 1000 copied skus from the RAW_skus table.
when i run the procedure it takes around 25 seconds.
is there a way to speed the inserts, and bring the time down? 1000 is just a test but the real number is adding 50% skus, so 6000.
INSERT INTO tblSKUGrown (SKU, OL)
SELECT TOP(1000)
SKU = SKU + '_n',
OL
FROM RAW_skus
ORDER BY NEWID()
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2016 at 10:05 am
I cant do that. thats assuming the top 1000 get added. a sku dataset may have 20000 and i need to randomly select 2000, not the first 2000 in the list.
December 1, 2016 at 10:12 am
Talvin Singh (12/1/2016)
I cant do that. thats assuming the top 1000 get added. a sku dataset may have 20000 and i need to randomly select 2000, not the first 2000 in the list.
ORDER BY NEWID()
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2016 at 10:24 am
oh cr*p, that has worked!!!
so ORDER BY NEWID() is a random function?
December 1, 2016 at 10:29 am
Talvin Singh (12/1/2016)
oh cr*p, that has worked!!!so ORDER BY NEWID() is a random function?
It's not a random function, but it creates a random value which is designed to be a uniqueidentifier. It will create a different value for each row (unlike RAND which needs a different seed to return different results in the same query). So when you order by it, the order becomes random.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply