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 Monday, June 10, 2013 11:46 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
Hey folks,
I'm looking for some help in understanding why my query is behaving differently under certain circumstances. The query is as follows:

with blah_index As
(Select ROW_NUMBER() Over (Order By blahdate desc, blahid asc) As RowNum
, *
from blah
where blahcode not in ('D')
and (blahstatus is null or blahstatus in ('A','B','C'))
and blahtype not in ('type1,'type2')
and blahcheck = 1
and ((blahtray = 1) or (blahtray = 2))
and blahname in (select filterValue
from blah_filters
where colName = 'blahname'
and comparitor = 'and')
)
select *
from blah_index
where RowNum between 1 and 1000
order by RowNum asc;

Now, if the filter value is sufficiently restrictive (ie. the column values are highly varied), the query works great and is nice and fast, particularly with the indexes that have been set up on the table. However, if the filter value is on a column that only contains a couple of values, the execution plan changes and performs considerably worse. I'm noticing there's a difference in where it's placing the 'distinct sort' for the in clause in the execution plan. In cases where it executes well, the sort is almost at the end of the chain. In cases where it's no good, it's further up the chain after the nested joins.

The odd thing is though, if I manually insert the filter values as a string into that query, it's super fast again. So, what's causing the execution plan to be so bad?

I've attached a couple of images of the differences between the plans. The 'goodrun' takes a matter of ms to run. The 'badrun' takes some 30seconds to execute.


  Post Attachments 
goodrun.png (13 views, 24.83 KB)
badrun.png (11 views, 24.79 KB)
Post #1461869
Posted Tuesday, June 11, 2013 12:11 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
Instead of posting pictures of the execution plans, how about attaching the actual execution plans (as .sqlplan files) instead. The pictures don't tell as much as the execution plans will.



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 #1461872
Posted Tuesday, June 11, 2013 1:07 AM
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/11/2013)
Instead of posting pictures of the execution plans, how about attaching the actual execution plans (as .sqlplan files) instead. The pictures don't tell as much as the execution plans will.


Hi Lynn....sorry, I'm not sure if I can/should do that without having the table/proc names redacted (dunno what my company policy would be regarding that). I'll chase it up tomorrow and post the actual plans if it's not an issue.
Post #1461889
Posted Tuesday, June 11, 2013 3:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:06 PM
Points: 972, Visits: 3,028
One thing that stands out is the number of Key Lookups in each plan. Viewing the actual plans would be useful though.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1461947
Posted Tuesday, June 11, 2013 6:17 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
Ok, I'll modify my original question.

Why does this first query perform so much worse than the second one?

First query:
with doc_index As
(Select ROW_NUMBER() Over (Order By docdate desc, docid asc) As RowNum
, a.*
from doclist a
inner join DocList_Filters b
on b.colName = 'STATUS'
and a.Status in ( select distinct filterValue
from DocList_Filters
where colName = 'STATUS'
and comparitor = 'and')
where doccode not in ('D')
and (statuscode is null or statuscode in ('A','I','D'))
and doctype not in ('type1','type2')
and docchecked = 1
and ((doctray = 1) or (doctray = 2))
)
select *
from doc_index
where RowNum between 1 and 1000
order by RowNum asc;

Second query:
with doc_index As
(Select ROW_NUMBER() Over (Order By docdate desc, docid asc) As RowNum
, a.*
from doclist a
where doccode not in ('D')
and (statuscode is null or statuscode in ('A','I','D'))
and doctype not in ('type1','type2')
and docchecked = 1
and ((doctray = 1) or (doctray = 2))
and a.status in ('active','inactive')
)
select *
from doc_index
where RowNum between 1 and 1000
order by RowNum asc;

The filters table only contains the 2 records that are manually used in the second query.
The doclist table contains 66 columns and about a million rows.

Is that enough info?
Post #1462429
Posted Wednesday, June 12, 2013 3:22 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:06 PM
Points: 972, Visits: 3,028
mrbonk (6/11/2013)
Is that enough info?

No. We need the actual execution plans and DDL for your tables and indexes.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1462514
Posted Wednesday, June 12, 2013 7:28 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
mrbonk (6/11/2013)
Ok, I'll modify my original question.

Why does this first query perform so much worse than the second one?

First query:
with doc_index As
(Select ROW_NUMBER() Over (Order By docdate desc, docid asc) As RowNum
, a.*
from doclist a
inner join DocList_Filters b
on b.colName = 'STATUS'
and a.Status in ( select distinct filterValue
from DocList_Filters
where colName = 'STATUS'
and comparitor = 'and')
where doccode not in ('D')
and (statuscode is null or statuscode in ('A','I','D'))
and doctype not in ('type1','type2')
and docchecked = 1
and ((doctray = 1) or (doctray = 2))
)
select *
from doc_index
where RowNum between 1 and 1000
order by RowNum asc;

Second query:
with doc_index As
(Select ROW_NUMBER() Over (Order By docdate desc, docid asc) As RowNum
, a.*
from doclist a
where doccode not in ('D')
and (statuscode is null or statuscode in ('A','I','D'))
and doctype not in ('type1','type2')
and docchecked = 1
and ((doctray = 1) or (doctray = 2))
and a.status in ('active','inactive')
)
select *
from doc_index
where RowNum between 1 and 1000
order by RowNum asc;

The filters table only contains the 2 records that are manually used in the second query.
The doclist table contains 66 columns and about a million rows.

Is that enough info?


First, here is a rewrite of both of your queries in reverse order (second query first):


Select top 1000
a.*
from
doclist a
where
a.doccode not in ('D')
and (a.statuscode is null or a.statuscode in ('A','I','D'))
and a.doctype not in ('type1','type2')
and a.docchecked = 1
and ((a.doctray = 1) or (a.doctray = 2))
and a.status in ('active','inactive')
order by
a.docdate desc,
a.docid asc;

Select top 1000
a.*
from
doclist a
inner join DocList_Filters b
on a.Status = b.filterValue
where
a.doccode not in ('D')
and (a.statuscode is null or statuscode in ('A','I','D'))
and a.doctype not in ('type1','type2')
and a.docchecked = 1
and ((a.doctray = 1) or (a.doctray = 2))
and b.colName = 'Status'
and b.comparitor = 'and'
order by
a.docdate desc,
a.docid asc;


Now, why is your first query less performant than the first? Here is what I saw in it:

1. Accessing 2 tables, one of then twice; one in a join and again as a subquery in that join.
2. The second query was only reading data from a single table.

Now, questions:

1. Do both queries return the same results sets?
2. Let us know if the my rewrites do the same, please.



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 #1462624
Posted Wednesday, June 12, 2013 7:57 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
I just rewrote the first query a second time:


Select top 1000
a.*
from
doclist a
where
a.doccode not in ('D')
and (a.statuscode is null or statuscode in ('A','I','D'))
and a.doctype not in ('type1','type2')
and a.docchecked = 1
and ((a.doctray = 1) or (a.doctray = 2))
and a.Status in (select
b.filterValue
from
DocList_Filters b
where
b.colName = 'Status'
and b.comparitor = 'and')
order by
a.docdate desc,
a.docid asc;





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 #1462648
Posted Wednesday, June 12, 2013 5: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
Hi Lynn,
Firstly, thank you so much for spending the time helping out!

The rewritten queries perform approx the same as my original ones. As soon as the subquery is present to get the filter values from the second table, it takes considerably longer to execute compared to the other method. Something else that I have to consider is that this forms part of a JIT loading implementation, so I can't just use top1000 to get the results. I need to be able to specify arbitrary start/end rows, hence my usage of the rownumber() call to produce a unique index from a sort column that's passed into the proc.

I've been working on this for about a week now and it's starting to look like there's no real viable solution that will work within both the software architecture and database structure that I'm unfortunately stuck with for the time being. Something that *does* work is to use 2 CTEs, with the first one being used to perform a gross paring of the starting set, so that second CTE and remaining select query don't have such a volume of data to deal with. It's not the preferred solution, but it's the one I'm going to have to run with until I have the ability to restructure the woeful database structure I'm presented with on this on!

For the sake of completeness, I'll post the queries I end up with when this is finalised.
Post #1462860
Posted Wednesday, June 12, 2013 5:24 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
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.



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 #1462863
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse