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.