Same query on same tables two execution plans with big time diff

Other SQL Server Topics
1

Hi,
I have a query used for paging that is running very fast in 0 seconds (records returned are 2000 // param char_field2='26752'),
but slow more than 12 seconds when changed param (records returned 95 //param char_field2='27622') just replaced the param char_field2='26752' to char_field2='27622', all else are identical. I checked the executions plans they are different.
Would any one pls. explain this although first query returns more records?

first query
select * from ( select docno from (select top 15 docno from FINANCE_MAIN m where CHAR_FIELD2='26752' AND CHAR_FIELD3='89715' AND CHAR_FIELD4='89716' AND CHAR_FIELD6='16000' AND CHAR_FIELD7='16003' Order by docno desc) as t1 where not exists ( select * from (select top 0 docno from FINANCE_MAIN m where CHAR_FIELD2='26752' AND CHAR_FIELD3='89715' AND CHAR_FIELD4='89716' AND CHAR_FIELD6='16000' AND CHAR_FIELD7='16003' Order by docno desc) as t2 where t1.docno=t2.docno )) as t3 inner join( select top 10000000 INDOCNO,CHAR_FIELD2,CHAR_FIELD3_AR,CHAR_FIELD1_AR,CHAR_FIELD4_AR,convert(nvarchar,FORWARD_DATE,103) as FORWARD_DATE,SUBJECT,COALESCE(DC2.parent_DOCNO,DC1.parent_DOCNO) AS ISLINKED,att.docno as attach,m.docno as docno,m.confid_id as confid,f.docname ,CHAR_FIELD3 from FINANCE_MAIN m left outer join (select top 1000000 max(cast (parent_docno as int) ) as parent_docno from oaefile_doclinks group by parent_docno order by parent_docno desc) as DC1 on m.docno=dc1.parent_docno LEFT OUTER JOIN OAEFILE_DOCLINKS DC2 ON m.DOCNO=DC2.CHILD_DOCNO LEFT OUTER JOIN (select top 1000000 cast (t.docno as int) as docno,min(f.docid) as attach_docno from dmsdocinfo f inner join FINANCE_ATTACH t on f.docid=t.attach_docno group by docno order by docno desc) as att on m.docno=att.docno left outer join dmsdocinfo f on att.attach_docno=f.docid where CHAR_FIELD2='26752' AND CHAR_FIELD3='89715' AND CHAR_FIELD4='89716' AND CHAR_FIELD6='16000' AND CHAR_FIELD7='16003' Order by docno desc ) as t4 on t3.docno=t4.docno order by t3.docno desc

second query
select * from ( select docno from (select top 15 docno from FINANCE_MAIN m where CHAR_FIELD2='27622' AND CHAR_FIELD3='89715' AND CHAR_FIELD4='89716' AND CHAR_FIELD6='16000' AND CHAR_FIELD7='16003' Order by docno desc) as t1 where not exists ( select * from (select top 0 docno from FINANCE_MAIN m where CHAR_FIELD2='27622' AND CHAR_FIELD3='89715' AND CHAR_FIELD4='89716' AND CHAR_FIELD6='16000' AND CHAR_FIELD7='16003' Order by docno desc) as t2 where t1.docno=t2.docno )) as t3 inner join( select top 10000000 INDOCNO,CHAR_FIELD2,CHAR_FIELD3_AR,CHAR_FIELD1_AR,CHAR_FIELD4_AR,convert(nvarchar,FORWARD_DATE,103) as FORWARD_DATE,SUBJECT,COALESCE(DC2.parent_DOCNO,DC1.parent_DOCNO) AS ISLINKED,att.docno as attach,m.docno as docno,m.confid_id as confid,f.docname ,CHAR_FIELD3 from FINANCE_MAIN m left outer join (select top 1000000 max(cast (parent_docno as int) ) as parent_docno from oaefile_doclinks group by parent_docno order by parent_docno desc) as DC1 on m.docno=dc1.parent_docno LEFT OUTER JOIN OAEFILE_DOCLINKS DC2 ON m.DOCNO=DC2.CHILD_DOCNO LEFT OUTER JOIN (select top 1000000 cast (t.docno as int) as docno,min(f.docid) as attach_docno from dmsdocinfo f inner join FINANCE_ATTACH t on f.docid=t.attach_docno group by docno order by docno desc) as att on m.docno=att.docno left outer join dmsdocinfo f on att.attach_docno=f.docid where CHAR_FIELD2='27622' AND CHAR_FIELD3='89715' AND CHAR_FIELD4='89716' AND CHAR_FIELD6='16000' AND CHAR_FIELD7='16003' Order by docno desc ) as t4 on t3.docno=t4.docno order by t3.docno desc

Thank you

2

I'm amazed it runs in a reasonable time at all with all the nested sub-selects ...

For example, what's the purpose of this?

LEFT OUTER JOIN 
(
	select top 1000000 cast (t.docno as int) as docno, min(f.docid) as attach_docno 
	from dmsdocinfo f 
		inner join FINANCE_ATTACH t 
			on f.docid=t.attach_docno 
	group by docno 
	order by docno desc
) as att 
	on m.docno=att.docno

Casting the docno to INT in order to then make the join, having to pre-prepare this for the GROUP BY and forcing a sort to get the TOP 1000000 - surely a JOIN would be able to sort all that out, more efficiently, without all that paraphernalia?

It all looks like horribly tangled to me. The whole query is on one line too, don't know how many people here will bother to read it unformatted, nor whether I have spotted anything useful, or have just focus on irrelevant points, from my quick look at it

3

Thank you for your comment I agree with you for the bad chosen query,
unfortunately I have inherited the system and profiling it showed me this query used for paging and they should have used top and offset for paging instead.
I have control only to the Db and trying to tune the paging by creating indexes, I don't know if I can force to use a plan for a query from the DB only.

first he try to get the rows for the page he is on by
select top 30 docno from maintable where ... not exist in (select top 15 docno from maintable where ..) // so he will get the 15 doc nos. for the second page.
then he join this table with itself and other tables to get the extra columns.
but Iam surprised why its choosing diff plan for different values of the parameter.

formatted query

select * 
from    ( select docno 
			from (select top 15 docno from FINANCE_MAIN m  
			where ....
			Order by docno desc) as t1 
	where not exists (  select * 
						from  (select top 0 docno 
								from FINANCE_MAIN m  
								where ... 
								Order by docno desc
							) as t2 
						where t1.docno=t2.docno 
						)
			) as t3 
 inner join	 ( select top 10000000 INDOCNO,CHAR_FIELD2,CHAR_FIELD3_AR,CHAR_FIELD1_AR,CHAR_FIELD4_AR,convert(nvarchar,FORWARD_DATE,103) as FORWARD_DATE,SUBJECT,COALESCE(DC2.parent_DOCNO,DC1.parent_DOCNO) AS ISLINKED,att.docno as attach,m.docno as docno,m.confid_id as confid,f.docname ,CHAR_FIELD3 
 from  FINANCE_MAIN  m 
 left outer join  (select  top 1000000 max(cast (parent_docno as int) ) as parent_docno from oaefile_doclinks group by parent_docno  order by parent_docno desc) as DC1 on m.docno=dc1.parent_docno 
 LEFT OUTER JOIN OAEFILE_DOCLINKS DC2 ON m.DOCNO=DC2.CHILD_DOCNO  
 LEFT OUTER JOIN  (select top 1000000 cast (t.docno as int) as docno,min(f.docid) as attach_docno from dmsdocinfo f 
 inner join FINANCE_ATTACH t on f.docid=t.attach_docno group by docno  order by docno desc) as att on m.docno=att.docno 
 left outer join dmsdocinfo f on att.attach_docno=f.docid  where CHAR_FIELD2='26752' AND CHAR_FIELD3='89715'  AND CHAR_FIELD4='89716' AND CHAR_FIELD6='16000' AND CHAR_FIELD7='16003'   Order by docno desc )   as t4 on t3.docno=t4.docno 
 order by t3.docno desc