May 26, 2011 at 10:56 am
Hi,
Does anyone know if there is a maximum number of rows that can be inserted in a single statement?
I have a procedure that runs in an overnight job. There are 4 SELECT statements that are unioned together and inserted into a table. It's about as straightfoward as that. There's no transaction around the INSERT, there are no parameters.
All of a sudden the step got 'stuck.' It normally finishes running in about 50 minutes, and I realized when I got in this morning that it had been running for 8 hours. This is time-sensitive, so in the interest of moving things along I stopped the job, truncated the table, and started the insert again. After nearly two hours, I stopped it again. It was again stuck. But it got stuck at the same record count (2,763,363).
Does anyone have any troubleshooting ideas/suggestions? It's imperative I resolve this as quickly as possible. Initially I thought it was just bad data and for some reason rather than the job bombing it would just get stuck. But based on our data, it appears as though the last record in the table is the last available record to be inserted.
This procedure executes successfully on both of our development databases, one of which is SQL 2005 and the other SQL 2008. But they haven't been refreshed since the weekend and the total record count is lower, which is what led to the question about a maximum number of records being inserted. Though again, I'd like to think that if a maximum was hit, the job would've failed rather than just hanging.
Again, any input would be GREATLY appreciated!
Thanks!
May 26, 2011 at 10:59 am
If it gets stuck as you say make sure you're not waiting on db growth (could be tempdb too).
2M is not a big insert for sql server!
May 26, 2011 at 11:01 am
How do I check to see if it's waiting on growth?
May 26, 2011 at 11:01 am
You will probably need to do this insert in batches. That is a huge amount of info to write to the transaction log all at once. Remember that just because it is not in an implicit transaction it still writes to the transaction for ACID. It must complete the entire insert and not partial which means it has to be written to the log. There are tons of posts on here about how to do that. I would help you but I have to run for a couple hours. Let me know if you get it figured out. Just search for "batch insert" and you should find the help you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 26, 2011 at 11:02 am
And another question...if the following job steps run successfully (which are doing more inserts), wouldn't the growth or tempdb be problems there, too?
May 26, 2011 at 11:11 am
Once the first insert is completed then that space can be reclaimed.
For the log it is also reclaimed if you run in simple. For bulk and full it takes a tlog backup to have that space marked as reusable.
May 26, 2011 at 11:24 am
I'm going to take a different direction. 50m for 2.7M records is terrible performance. You could likely do it in under 5m with SSIS. You could use the entire 4 query union in the source and pipe it into the destination with fast load it will be VERY fast. I've loaded 10's of millions in under 50m using this method.
CEWII
May 26, 2011 at 11:25 am
If it's a UNION of several inserts, then wouldn't it all have to be inserted at once?
May 26, 2011 at 11:27 am
SQL doesn't look at it that way.. It is 4 selects and 1 insert.
CEWII
May 26, 2011 at 11:28 am
Vicki Peters (5/26/2011)
If it's a UNION of several inserts, then wouldn't it all have to be inserted at once?
Union or union all? That could make a hell of a difference as well.
May 26, 2011 at 11:54 am
I'm going to second Elliot. We had a process I recently converted to use ssis to perform the insert vs the stored procedure and saw an immense improvement in performance. 112 million rows that was taking hours and did not complete took 90 minutes using SSIS with Fast Load.
May 26, 2011 at 12:00 pm
Ninja's_RGR'us (5/26/2011)
Vicki Peters (5/26/2011)
If it's a UNION of several inserts, then wouldn't it all have to be inserted at once?Union or union all? That could make a hell of a difference as well.
Union.
May 26, 2011 at 12:01 pm
Elliott Whitlow (5/26/2011)
I'm going to take a different direction. 50m for 2.7M records is terrible performance. You could likely do it in under 5m with SSIS. You could use the entire 4 query union in the source and pipe it into the destination with fast load it will be VERY fast. I've loaded 10's of millions in under 50m using this method.CEWII
I was thinking of BCP'ing it out. Any thoughts on that verses the package?
May 26, 2011 at 12:05 pm
Ninja's_RGR'us (5/26/2011)
If it gets stuck as you say make sure you're not waiting on db growth (could be tempdb too).2M is not a big insert for sql server!
I did check database size. It actually did need to be increased. I increased both the database and tempdb (primary and secondary) sizes. I was hopeful it would make a difference, but it doesn't seem to be helping. The job is running its 13th step, which should've completed in about 15 minutes, and it's been running over an hour now. It's another step that inserts records, but I'm not sure why all of a sudden this is such a problem. Especially after increasing the database sizes.
May 26, 2011 at 12:05 pm
If you BCP it out you still have to spend the time to BCP it IN.. It would still be faster than what you are doing though. I would still recommend SSIS, perhaps as a scheduled job.
I would say the "cost" in terms of time to generate the rows would be roughly the same between the BCP and the SSIS. The difference being no intermediate file and the insert is occurring more real-time.
CEWII
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply