December 3, 2017 at 11:29 am
Hello all,
I am working on a SQL query will lots of joins and it is currently taking around a min to complete which as per the amount of records in all tables and keeping the complexity in mind is fine. Though the current time taken is already good but anyways I was checking the query plan to see if there is any possibility for enhancing it. I saw that there is a particular point which shows around 70% in RID lookup for the main table. It's a heap table but the lookup seems to be because of an amount column which acts as output in aggregate function but is not used in any of the NC index. Rest all of the used or output columns are in NC indexes. My question is if it's beneficial OR best practice to add amount column in indexes (nc of course) AND what are the implications of adding a column directly to index vs adding a column as covered. Please share ur views.
Note: I have no authority currently to share the screenshot so please forgive me for that.
December 4, 2017 at 9:22 am
It would be good to see more information, even mocked up.
Is amount used in other queries, or in WHERE/HAVING/ON clauses? If so, then adding it to the index itself can help. If it's used in other queries for calculations or display, then the INCLUDE might be helpful. In terms of removing the RID, this would do it at the expense of more data in the index, either as a column or the include. If there is ordering needed, then the values might be better in the index (sorted), but if not, then include is fine.
You'll have to test this in a few ways. You might read this as well: https://sqlperformance.com/2014/07/sql-indexes/new-index-columns-key-vs-include
December 5, 2017 at 11:41 am
Steve Jones - SSC Editor - Monday, December 4, 2017 9:22 AMIt would be good to see more information, even mocked up.Is amount used in other queries, or in WHERE/HAVING/ON clauses? If so, then adding it to the index itself can help. If it's used in other queries for calculations or display, then the INCLUDE might be helpful. In terms of removing the RID, this would do it at the expense of more data in the index, either as a column or the include. If there is ordering needed, then the values might be better in the index (sorted), but if not, then include is fine.
You'll have to test this in a few ways. You might read this as well: https://sqlperformance.com/2014/07/sql-indexes/new-index-columns-key-vs-include
Hi Steve, with all due respect, I will not be able to provide any query plan for some reasons.
The query aggregates amount for four columns for a month. The table contains data for 10 years. This table joins with 8 other tables. All the columns used in join as well as select query are in index. However the amount is not. Should I include the amount column in the index itself or with INCLUDE ? this table gets populated continuously during the day by some users. Only insert happens on this table and no delete or update.
December 5, 2017 at 1:39 pm
Well, not being able to see the query and the execution plan, not much we can do. There are things to look for, such as sub-queries with DISTINCT. This could be valid, but it could also hide poor joins between tables.
Another thing would be any mismatches in data types in in joins or where clauses.
December 6, 2017 at 12:40 pm
I am hesitant to include these unless they are used in other queries as well, but as Lynn mentioned, without more information, it's hard to make a good recommendation either way here.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply