December 24, 2015 at 10:51 pm
Hi Team,
We have a table which has abt 30Lakh records, we insert abt 1 lakh record every day and update the records via some remote DB tables.
The update takes about 3 hours. But when we rebuild the indexes before updation, its very fast , about 20 mins.
My Question:
Is it a good design to drop and create indexes at run time , the table being updated is not used in any other module.
Drop before updating and create it back after the updates.
OR
Rebuild the index at runtime , ie before updation.
December 25, 2015 at 1:33 pm
GonnaCatchIT (12/24/2015)
Is it a good design to drop and create indexes at run time
Not usually. It's done for bulk inserts, less so updates, but test both ways and see.
Also, see whether you really do need to rebuild the index, or if it's just a matter of updating the statistics.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 26, 2015 at 10:41 pm
Instead of rebuilding, see whether UPDATE STATISTICS, by itself, improves performance.
December 28, 2015 at 1:47 am
How does fragmentation causes slow updates of a large table? Could you please put light on this? As far as I know, scanning/seeking of index becomes slow due to fragmentation and this is resolved by rebuilding/reorganizing indexes (daily/weekly) as per need.
Regards
VG
December 28, 2015 at 3:00 am
vivekgrover44 (12/28/2015)
How does fragmentation causes slow updates of a large table? Could you please put light on this?
It doesn't usually, which is why I said to check whether a statistics update had the same effect (rebuilding an index updates stats)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply