Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Execution plan shennanigans...... Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 6:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 10:12 PM
Points: 6, Visits: 29
Lynn Pettis (6/12/2013)
What is also missing is the DDL for the tables you are using along with the current indexes defined on those tables.

Part of the problem you are having may also be attributed to poor indexing of the tables.


Part of the requirement here is that records be presented in a default sort order of date desc, then docid asc. Additionally, the user can specify an arbitrary third sort column, which can be asc or desc. Indexing the primary table with the default sort in mind results in reasonable performance (not great, but acceptable). However, allowing any one of another 65 columns to be specified in the sort makes the server work overtime trying to achieve this. A less than ideal solution to this has been to add suitable indexes to specifically handle the sort orders that might be specified (where the index would be restrictive enough to be used), but obviously this has resulted in more space being consumed in indexes than there is actual data in the table. However, it makes the queries perform at an acceptable level again, so has to remain.

I'm sure a suitably normalised table structure with appropriate indexes would outperform this monstrosity hands down, but it's not an option in the short term
Post #1462868
Posted Wednesday, June 12, 2013 6:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 20,801, Visits: 32,724
mrbonk (6/12/2013)
Lynn Pettis (6/12/2013)
What is also missing is the DDL for the tables you are using along with the current indexes defined on those tables.

Part of the problem you are having may also be attributed to poor indexing of the tables.


Part of the requirement here is that records be presented in a default sort order of date desc, then docid asc. Additionally, the user can specify an arbitrary third sort column, which can be asc or desc. Indexing the primary table with the default sort in mind results in reasonable performance (not great, but acceptable). However, allowing any one of another 65 columns to be specified in the sort makes the server work overtime trying to achieve this. A less than ideal solution to this has been to add suitable indexes to specifically handle the sort orders that might be specified (where the index would be restrictive enough to be used), but obviously this has resulted in more space being consumed in indexes than there is actual data in the table. However, it makes the queries perform at an acceptable level again, so has to remain.

I'm sure a suitably normalised table structure with appropriate indexes would outperform this monstrosity hands down, but it's not an option in the short term



This arbitrary 3rd column is only for the final sort or does it also impact the select of the rows?

Is this query contained in a stored procedure?



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)
Post #1462869
Posted Wednesday, June 12, 2013 6:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 20,801, Visits: 32,724
And how is this third column passed to the query?



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)
Post #1462870
Posted Wednesday, June 12, 2013 8:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 10:12 PM
Points: 6, Visits: 29
Lynn Pettis (6/12/2013)

This arbitrary 3rd column is only for the final sort or does it also impact the select of the rows?

Is this query contained in a stored procedure?


Unfortunately no.....it forms part of the index.

This is all in a stored proc which has to be dynamically generated (for a variety of reasons). The user selected sort column name is passed in as a parameter, along with a number of other parameters.
Post #1462888
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse