Increase speed of WHILE LOOP with INSERT

  • 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.

  • 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()

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

  • 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()

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • oh cr*p, that has worked!!!

    so ORDER BY NEWID() is a random function?

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply