SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question w.r.t. amount columm


Question w.r.t. amount columm

Author
Message
sqlenthu 89358
sqlenthu 89358
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 360
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)

Group: Administrators
Points: 619752 Visits: 21258
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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
sqlenthu 89358
sqlenthu 89358
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 360
Steve Jones - SSC Editor - Monday, December 4, 2017 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

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.

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)

Group: General Forum Members
Points: 401212 Visits: 43394
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)

Group: Administrators
Points: 619752 Visits: 21258
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search