INDEX and INSERT in SQL Server 2005
-
Hi i have table which is having 0.5 billion entries... In that table, i have added 5 columns for indexing.all are Non-Unique and Non-Clustered. One application is frequently insert new rows to this table... My question is Will the INDEX cause, the INSERT operation to take more time to completed?
My small attempt...
-
Hi i have table which is having 0.5 billion entries... In that table, i have added 5 columns for indexing.all are Non-Unique and Non-Clustered. One application is frequently insert new rows to this table... My question is Will the INDEX cause, the INSERT operation to take more time to completed?
My small attempt...
sujithkumarsl wrote:
Will the INDEX cause, the INSERT operation to take more time to completed?
Yes, but whether it is significant is down to testing and usage - if it makes a significant improvement in data retrieval is it worth the slow down with the insert? Its up to you to decide if the trade off is worth it.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
sujithkumarsl wrote:
Will the INDEX cause, the INSERT operation to take more time to completed?
Yes, but whether it is significant is down to testing and usage - if it makes a significant improvement in data retrieval is it worth the slow down with the insert? Its up to you to decide if the trade off is worth it.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Thanks.... actually several SELECT as well as INSERT operation are running at same time.. What will be the wise way to manage the indexes?
My small attempt...
-
Thanks.... actually several SELECT as well as INSERT operation are running at same time.. What will be the wise way to manage the indexes?
My small attempt...
sujithkumarsl wrote:
several SELECT as well as INSERT operation are running at same time
So there may be blocking on the table anyway. The only way to really find out is to test under real life load.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
Thanks.... actually several SELECT as well as INSERT operation are running at same time.. What will be the wise way to manage the indexes?
My small attempt...
In SQL Studio Manager, under the Query menu, choose the option, "Display Estimated Execution Plan" while testing your Select / Insert logic to verify that the indexes you have created are actually being used. If an index is not being used, you may want to recosider whether or not you really need it. david