May 13, 2014 at 1:31 am
Good Morning Team,
I have process which inserts 2-3 million of records in table T1.
T1 has around 60 millions of records and schema bound views with clustered indexes on views.
Now the developer has written code to insert daily data in table T1 but at that time they dropped the schema bound views and indexes and again recreate them.
My question is that does the dropping and recreating of indexes required
as there is no table alteration is done only new data insert.
Please suggest.
May 13, 2014 at 5:59 am
It depends. A common practice for large data loads is to drop the indexes because they're not used during inserts and then recreate them after the inserts are complete. In some circumstances, that makes for a much faster load process. That may be the case here. If you're also doing deletes and updates, then the indexes can be needed to assist the data load. Not knowing the details of your process, the best thing I can suggest is to test it both ways.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2014 at 11:23 pm
I got the point that while insert we should drop and recreate the indexes.
But we are not rebuilding indexes on "table".
Here the schema bound views and its indexes are rebuild and it takes 2 hrs.
So my question here is if I don't rebuild views and indexes on views, will that affect the functionality of views apart from performance?
May 14, 2014 at 11:36 pm
mssqlsrv (5/14/2014)
I got the point that while insert we should drop and recreate the indexes.But we are not rebuilding indexes on "table".
Here the schema bound views and its indexes are rebuild and it takes 2 hrs.
So my question here is if I don't rebuild views and indexes on views, will that affect the functionality of views apart from performance?
if you are dropping the view and index, then why you are rebuilding the index again? because rebuilding the index process drop and recreate the complete index. you are doing one thing twice.
May 15, 2014 at 2:45 am
I am doing the rebuild process once only.
If I don't drop the views and indexes which are schema bound to table,
Will that affect my insert statements on table?
May 15, 2014 at 4:34 am
mssqlsrv (5/15/2014)
I am doing the rebuild process once only.If I don't drop the views and indexes which are schema bound to table,
Will that affect my insert statements on table?
Yes, it can. How much? You'll need to test it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2014 at 5:03 am
Thanks for the help. I will test it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply