February 15, 2011 at 9:38 am
Just making sure I know what I'm talking about here...
We have a table with 9 million rows. Each month 1 million extra get added (rough figures). The data goes via SSIS into a staging table. A stored procedure then moves this on (with modifications) into the LIVE table. When it updates this table it fires off an update trigger that closes down the previous month's 1,000,000 using dates from the new set.
I have noted that the stored procedure loads the data very quickly into the table, but on occassion it fails for some reason (timeouts, server issues) and when this happens, even thought the data is in place it rolls it all back again (the job is still running).
Would I be right in saying that the data will arrive in the table, but the batch isn't fully committed until the Trigger has completed? That woudl explain things.
Also on the subject, is the index getting updated at the same time as the initial insert, or does it wait for the entire batch including Triggers?
February 15, 2011 at 10:29 am
Yes, transactions include anything a trigger calls for.
If you want to manage that more precisely, move the code out of the trigger and into a proc. You can then manage transactions with that.
If possible, with data volumes that high, I usually try to split the job up into smaller batches. Some data doesn't work that way, but most can be made to. So, transfer and update 1,000 rows at at time, 1,000 times, instead of 1,000,000 rows all at once. Will usually make it more manageable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2011 at 2:19 pm
Yeah that's good advice. As part of our improvement works we do have plans to split the load into smaller batches, and to include the updates as part of the stored proc too!
But its good to understand what is going on so cheers!
February 16, 2011 at 11:47 am
Can someone answer me this with regards SQL Server "mechanics"?
The 1,000,000 rows hit the table in 1 batch. It loads them all and you can see them in there. The trigger then runs as part of the batch, and it finishes and you can see the updates in the table.
With regards to indexes - at which point would they update on a big batch like that? Would the new rows be added in as each one arrives in the db, or when the batch of 1,000,000 has finished? If the latter - before and after or just after the trigger?
Also - if the batch (Inserts AND trigger) has completed and I can see all the records but the job is still in progress - does that suggest it is then working on the indexes, or is there something else then going on that I've been oblivious to all these years?
If someone can help me understand that - THANK YOU SO MUCH! :hehe:
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply