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 |
|
NifflerX
Starting Member
29 Posts |
 Posted - 2012-08-27 : 10:38:29
|
| Hello,I have a data loading process that was created by my predecessor on SQL Server 2000 that has to load a bunch of data daily (not huge amounts but on the order of 10,000). To do this his process always drops the index on the table that is getting the insert, and then re-creates the index after the records have been inserted.I was wondering if this is still the best method given the SQL enhancements from 2000 to 2008 R2 (my current version). Does anyone know if this process is better, worse, or the same than just letting the records be inserted with the index in existence? I know originally it was designed this way because with the index in the insert took too long, but I'm thinking there may be SQL improvements that would handle this. Thank you very much.-NifflerX |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-08-27 : 11:04:16
|
| Unfortunatly, nothing changed. But you must perform drop and recreate index (disable/rebuild indexes), after comparing time you are saving and time when you will REBUILD these indexes. Secondly, disabling all index on target table WILL slow down queries, that need to be satisfied during this bulk insertion time.--------------------------http://connectsql.blogspot.com/ |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-27 : 11:04:18
|
| Test it and see?You might need something to rebuild the index periodically anyway and watch out for log space.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2012-08-27 : 11:33:58
|
| Thanks so much for the quick replies. I've got weekly index rebuilds and statistic updates but for this data it has to be daily which is why the current scenario is in place. If I get a chance I'll do some tests and see, but given that nothing fundamental has changed in SQL I won't throw it at the top of my list. Thanks again.-NifflerX |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-08-27 : 14:51:16
|
| Are the tables being completely reloaded, meaning that all the data is deleted/truncated and then completely reloaded with new data?If that is the case, you should try dropping the non-clustered indexes only, truncate the table, load the data in same sequence as the clustered index, and then create the non-clustered indexes.As always, you should test to see which is fastest.CODO ERGO SUM |
|
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2012-08-27 : 20:12:28
|
quote:
Originally posted by Michael Valentine Jones
Are the tables being completely reloaded, meaning that all the data is deleted/truncated and then completely reloaded with new data?
No, in this case only new records are being added to the table daily. No deletions, truncations or updates.-NifflerX |
|
|
|
|
|
|
|
|
Dropping and creating an index daily