locked
commit rows per batch RRS feed

  • Question

  • Hello Professional,

    I have written a code with insert/update logic, here I have two destination tables where tableX will have latest records and history_tableX will have old records of that particular record.

    For this I have use two execute sql tasks where i used joins. And now my colleague is telling me to use commit size as 7000 rows per batch.

    I know if it is a dft we will have options to set it. As it is execute sql task I dont know how to do it.

    Thanks in advance.

    Monday, June 17, 2013 12:58 PM

Answers

  • so here it is

    SET ROWCOUNT 7000
    
     
    
    WHILE (1 = 1)
    
      BEGIN
    
        BEGIN TRANSACTION
    
        
    
        UPDATE [Order Details]
    
        SET    Discount = 0.11
    
        WHERE  Discount != 0.11
    
        
    
        IF @@ROWCOUNT = 0
    
          BEGIN
    
            COMMIT TRANSACTION
    
            
    
            BREAK
    
          END
    
        
    
        COMMIT TRANSACTION
    
      END
    
     
    
    SET ROWCOUNT  0


    Arthur My Blog

    • Marked as answer by Murali dhar Monday, June 17, 2013 6:28 PM
    Monday, June 17, 2013 5:56 PM

All replies

  • Hi Murali,

    Why have you used Execute SQL Tasks? Have you thought about using OLEDB Sources and Destinations in a data flow? An OLEDB destination should allow you to control the size of the commit batch.

    Kind regards.

    M

    Monday, June 17, 2013 1:03 PM
  • yes. I know that. but because of perfomance issues i chose this one as this is a daily load. we will want to use to use for better performance. If I use source and destination I have to use data conversion tasks and some extra things which is over head.
    Monday, June 17, 2013 1:18 PM
  • How many rows are you moving through to the data flow?
    Monday, June 17, 2013 1:26 PM
  • 1MILLION
    Monday, June 17, 2013 1:33 PM
  • You should not have performance issues with 1 million rows. SSIS has being designed to handle much larger data flows.

    In addition, you do not need data conversion tasks. You just need the SELECT portion of each Execute task.

    Kind regards,

    M

    Monday, June 17, 2013 2:08 PM
  • Yes it did. It is taking more time. I tried both ways and felt like using sql task than dft as i compared the time it is taking for both of'em

    Thanks.

    Monday, June 17, 2013 3:13 PM
  • Hi Murali,

    Why you want to set a commit size in execute sql task?


    Nitesh Rai- Please mark the post as answered if it answers your question

    Monday, June 17, 2013 3:23 PM
  • You still can do that in Execute SQL Task with T-SQL like

    WHILE @@ROWCOUNT <> 0
    UPDATE ...

    Or use UPDATE TOP 7000 in a loop as in

    http://www.sqlusa.com/bestpractices/largeupdate/


    Arthur My Blog

    Monday, June 17, 2013 3:27 PM
  • Hi Murali,

    If you must commit rows in batches of 7,000; you have no other choice than using a data flow or making the SQL in your Execute SQL task more complex to process 7,000 at a time.

    Having said this, why do you need to commit in batches of precisely 7,000 is something I don't understand.

    Kind regards,

    M

    Monday, June 17, 2013 3:31 PM
  • Hi Nitesh,

                   Thanks for getting back. I need it because my colleague is a dba/developer and he always talks about transaction log size should be small. And he mentioned me to do commit for every 7000.

    I did it by using dft but it is taking more time, as we are having daily loads we want better performance thing so I came up with joins concept and used in execute sql task instead of SCD for update insert logic.

    But now in sql task I cannot figure how to do commit for every 7000 rows.

    Please get back to me, if I didn't mentioned anything technically or I didn't able to explain clearly.

    Thanks.

    Monday, June 17, 2013 5:33 PM
  • Hi Vega,

     That is our req doc says. so I have to do.

    Monday, June 17, 2013 5:33 PM
  • so here it is

    SET ROWCOUNT 7000
    
     
    
    WHILE (1 = 1)
    
      BEGIN
    
        BEGIN TRANSACTION
    
        
    
        UPDATE [Order Details]
    
        SET    Discount = 0.11
    
        WHERE  Discount != 0.11
    
        
    
        IF @@ROWCOUNT = 0
    
          BEGIN
    
            COMMIT TRANSACTION
    
            
    
            BREAK
    
          END
    
        
    
        COMMIT TRANSACTION
    
      END
    
     
    
    SET ROWCOUNT  0


    Arthur My Blog

    • Marked as answer by Murali dhar Monday, June 17, 2013 6:28 PM
    Monday, June 17, 2013 5:56 PM