November 26, 2020 at 3:28 am
There are about thirty fields in a table SaleOrderLines, I created some indexes , but there are 2 indexes as below, if performing select *from SaleOrderLines where OrderID='value1' and itemid=value2'
and select *from SaleOrderLines where OrderID='value1' and itemid=value2' and status='open' , only for this two statement, can we drop IDX1 index on this table? Thanks!
create index IDX1 on SaleOrderLines (OrderID,ItemID)
create index IDX12 on SaleOrderLines (OrderID,ItemID,Satus)
November 26, 2020 at 3:42 am
Yes, you can drop IDX1; IDX12 can cover all queries that would use IDX1.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 26, 2020 at 12:38 pm
Yes, you can drop IDX1; IDX12 can cover all queries that would use IDX1.
thank you !
if I create the following on the table , could you please tell the difference between this 2 indexes as I don't understand how system retrieves data while there is a included index? thanks!
create index IDX1 on SaleOrderLines (OrderID,ItemID,Satus)
create index IDX2on SaleOrderLines (OrderID,ItemID ) include (status)
November 26, 2020 at 3:45 pm
I think I can help with that one.
IDX1 would have the 3 columns with the data ordered by OrderID, then ItemID, and finally Status. If there were more columns in the table, you would need to use a key lookup to look at the row on the actual table.
IDX2 on the other hand will still have 3 columns, but is ordered by OrderID, then ItemID and Status is not part of the ordering. Status is just included in the index so if your select included the Status, you would not need to use a key lookup to find the status in the actual table. Any columns that you select outside of OrderID, ItemID, and Status will still need a key lookup back to the main table.
Since IDX2 does not have any order on the Status, if an OrderID + ItemID can produce multiple Status's (ie OrderID 1 with ItemID1 can have 100 status's), your second query would need to look at all 100 rows in IDX2 to get all of the ones that are "open". But if it used IDX1, and only 10 of the 100 status's were of type "open", it would only need to look at 10 rows as it already has them in order.
IF an orderID and ItemID can only have 1 possible status, then they would do essentially the same thing on the query side, but the optimizer may do things a bit differently as it doesn't know that there is only 1 status per orderID+ItemID.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 27, 2020 at 1:14 am
For best overall performance, it's extremely likely that you need to change the clustering index on the table to be ( OrderID, ItemID ). Especially if the table is now clustered on identity (wrong!). If those two columns are not unique, then add a $IDENTITY as the last column of the clus key. You want to make sure the clus key is inherently unique, so that SQL doesn't have to "uniquify" the key itself.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 27, 2020 at 4:51 am
I think I can help with that one.
IDX1 would have the 3 columns with the data ordered by OrderID, then ItemID, and finally Status. If there were more columns in the table, you would need to use a key lookup to look at the row on the actual table.
IDX2 on the other hand will still have 3 columns, but is ordered by OrderID, then ItemID and Status is not part of the ordering. Status is just included in the index so if your select included the Status, you would not need to use a key lookup to find the status in the actual table. Any columns that you select outside of OrderID, ItemID, and Status will still need a key lookup back to the main table.
Since IDX2 does not have any order on the Status, if an OrderID + ItemID can produce multiple Status's (ie OrderID 1 with ItemID1 can have 100 status's), your second query would need to look at all 100 rows in IDX2 to get all of the ones that are "open". But if it used IDX1, and only 10 of the 100 status's were of type "open", it would only need to look at 10 rows as it already has them in order.
IF an orderID and ItemID can only have 1 possible status, then they would do essentially the same thing on the query side, but the optimizer may do things a bit differently as it doesn't know that there is only 1 status per orderID+ItemID.
Thank you for your kind help! for the following indexes, can we say that IDX1 takes more index space than IDX2 ? thanks!
create index IDX1 on SaleOrderLines (OrderID,ItemID,Satus)
create index IDX2on SaleOrderLines (OrderID,ItemID ) include (status)
November 27, 2020 at 6:31 am
For best overall performance, it's extremely likely that you need to change the clustering index on the table to be ( OrderID, ItemID ). Especially if the table is now clustered on identity (wrong!). If those two columns are not unique, then add a $IDENTITY as the last column of the clus key. You want to make sure the clus key is inherently unique, so that SQL doesn't have to "uniquify" they key itself.
noted , thank you!
November 27, 2020 at 2:10 pm
From my understanding both IDX1 and IDX2 would use the exact same space. They are holding the exact same data, just in potentially a different order.
If there is any compression happening on those indexes, you may get better compression on one over the other, but offhand, I am not positive which would be smaller.
Based on your original 2 queries, I would be going with creating index 1 OR (better yet) creating the clustered index as ScottPletcher indicated and then potentially creating a non-clustered index on Status. But creating indexes is an art; there is a balance with making indexes. The more indexes you have on a table, the slower an INSERT will be and it can help or hurt SELECT, UPDATE, and DELETE performance. And, as you noted, each index will take up some disk space as well.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 27, 2020 at 3:23 pm
Typically, you'd want columns used in the WHERE condition to be in the index keys, for the reasons noted by Mr. Gale.
select *from SaleOrderLines where OrderID='value1' and itemid=value2' and status='open'
However, status tends to change a lot, and you want a clus key that never changes (except possibly once at end-of-life, such as for an active/inactive flag).
So, in this case, you'd leave status off the clus key and use an identity.
Note that INCLUDE columns are not relevant here, since the clus index by definition does not have include columns.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply