March 5, 2013 at 12:55 am
In Insert and Update, Multiple Index and Inculude columns is effect performance.
March 5, 2013 at 1:08 am
Each index will cause an insert statement to be slower, because the operation will have to update the table and then the index. Without the index it needs to update only the table. For update operations it depends. If the update statement is using an index to locate the record it can improve the performance (Think of a table that has few millions records and has to update 1 record. Locating the record with an index will improve the performance). If the update operation did not use an index, but needed to modify the index, then the index will cause the statement to run slower.
This doesn't mean of course that you should not have indexes. You just have to realize that each index has a small price to pay in some statement and you have to consider the effect of the index for all operations on the table (including of course select operations).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2013 at 1:11 am
What about inculude columns in indexes?
March 5, 2013 at 1:19 am
Comparing multi-indexes and indexes with included columns: an UPDATE operation will touch every nonclustered index that contains at either the key or the leaf level the column being updated.
March 5, 2013 at 1:24 am
Technically any column which is part of any index will get impacted by any DML operation plus statistics also get hit
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 5, 2013 at 1:25 am
Think that i have a item total table and it inculudes all item totals and take 300.000 insert and update per a day and it has a
two index threee columns is index key column and other 15 column is inculude column i think it is not true artitecture for in this
presaure and i think to remove inculude columns
now item total table has 2 333 332
and 867.086 MB
March 5, 2013 at 1:39 am
ESAT ERKEC (3/5/2013)
Think that i have a item total table and it inculudes all item totals and take 300.000 insert and update per a day and it has atwo index threee columns is index key column and other 15 column is inculude column i think it is not true artitecture for in this
presaure and i think to remove inculude columns
now item total table has 2 333 332
and 867.086 MB
Lets discuss your actual problem , share with us .these hypothetical examples always dotn provide actual solutions 😀
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply