Schema bound views and indexes

Question

Schema bound views and indexes

mssqlsrv

SSCrazy

Points: 2375
More actions
May 13, 2014 at 1:31 am

#307305

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

mssqlsrv SSCrazy Points: 2375 More actions · Answer 2

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?

twin.devil SSC-Insane Points: 22208 More actions · Answer 3

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.

mssqlsrv SSCrazy Points: 2375 More actions · Answer 4

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?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

mssqlsrv SSCrazy Points: 2375 More actions · Answer 6

mssqlsrv

SSCrazy

Points: 2375

May 15, 2014 at 5:03 am

#1713785

Thanks for the help. I will test it.