Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
shaggy
Posting Yak Master
248 Posts |
Posted - 2013-03-30 : 06:42:26
|
Can anyone absorb the difference between the two same insert statements The insert staments are same running in while loop but the differenec is 1st insert is without transaction taking long time to complete 2nd insert is fast i have included the transactionFor 1st insert I've absorbed the (X) lock on RID and (IX) on PAGE & Object and the lock is getting released and acquiring lock on another RID and so on its going till end (100000).For 2nd insert completed in a second holding (100000) RID (X) lock(IX) on some x pages and (IX) on Object.I want to what is the differnec between them. internally what is happening because of lock acquired and relaesed and again acguiring is causing the 1st insert to slow down ?Is that we have to include transaction for whennever we insert bulk records ?Create table testtable (col1 int)--Insert statement 1declare @col1 int = 0while @col1 <=100000begininsert testtable select @col1select @col1 = @col1 + 1end --Insert statement 2Drop table testtable declare @col1 int = 0begin tranwhile @col1 <=100000begininsert testtable select @col1select @col1 = @col1 + 1end commit |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-30 : 18:46:29
|
It could also be because in the first one each insert is a transaction - which means each insert needs to be persisted to the log file. That results in a lot of small writes to the log. In the second case, SQL Server can afford to not persist any of the inserts until the end of the loop when you are ready to commit.In any case, if you want to insert 100,000 rows to a table, you should see if there is a way to do it as a set based operation instead of a while loop. |
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2013-04-01 : 04:43:29
|
Thanks James |
|
|
|
|
|