Bulk Copy from small table to larger one in SQL Server 2005

bcp sqlbulkcopy sql-server sql-server-2005

Question

I'm a newbie in SQL Server and have the following dilemma:

I have two tables with the same structure. Call it runningTbl and finalTbl.

runningTbl contains about 600 000 to 1 million rows every 15 minutes.

After doing some data cleanup in runningTbl I want to move all the records to finalTbl. finalTbl currently has about 38 million rows.

The above process needs to be repeated every 15-20 minutes.

The problem is that the moving of data from runningTbl to finalTbl is taking way longer than 20 minutes at times..

Initially when the tables were small it took anything from 10 seconds to 2 minutes to copy.

Now it just takes too long.

Any one that can assist with this? SQL query to follow..

Thanks

Popular Answer

For starters : one of the things I've learned over the years is that MSSQL does a great job at optimizing all kinds of operations but to do so heavily relies on the statistics for all tables involved. Hence, I would suggest to run "UPDATE STATISTICS processed_logs" & "UPDATE STATISTICS unprocessed_logs" before running the actual inserts; even on a large table these things don't take all that long. Apart from that, based on the query above, a lot depends on the indexes of the target table. I'm assuming the target table has its clustered index (or PRIMARY KEY) on (at least) UnixTime, if not you'll create major data-fragmentation when you squeeze more and more data in-between the already existing records. To work around this you could try defragmenting the target table once in a while (can be done online, but takes a long time), but making the clustered index (or PK) so that data is always appended to the end of the table would be the better approach; well, at least in my opinion.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why