Paging records on SQL 2000 : Followup question

[rbg]

You are right, I did not include the exact query since it has a whole
of joins and many where clauses in it.
I did not want to make the post very hard to read, hence I simplified
it.

In the Stored proc I am using a String variable @SQLString
varchar(2000) to hold the entire select statement, and then executing
that SQL using EXEC (@SQLString).

Thus for debugging, I used Query Analyzer, and within the Analyzer I am
using the Select statement.
So in my test I do not use any stored proc.

so one select statement says:

Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart",
tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate

>From PermitMain inner join tbl_Permittee

on PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like '01%' ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))
and PermitteeNumber = @PermitteeNumber
and PermitMain.Boroughcode = @Boro
order by WorkStart DESC
) as T2 order by WorkStart ASC

) as T3

) as T4 order by WorkStart DESC

THIS ONE RUNS FAST and RETURNS RESULTS.

The Other Select statement:

Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart",
tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate

>From PermitMain inner join tbl_Permittee

on PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like @WorkTYPE ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))

and PermitteeNumber = @PermitteeNumber

and PermitMain.Boroughcode = @Boro

order by WorkStart DESC

) as T2 order by WorkStart ASC

) as T3

) as T4 order by WorkStart DESC

TAKES FOREVER to COMPLETE.

However IF I INCREASE the PAGESIZE from 600 to 800, BOTH QUERIES RETURN
RESULTS EQUALLY FAST.

Thanks for your help

[Aaron Bertrand [SQL Server MVP]]

You are right, I did not include the exact query since it has a whole

of joins and many where clauses in it.

Can you *PLEASE* not start a new thread for every reply?

A

[JXStern]

On 26 Jan 2007 05:56:21 -0800, "rbg" <rb*****@gmail.comwrote:

>Select * from ( Select Top 600 * from

....

>(PermitType_ID like '01%' ) and worktypeid is null

....

>THIS ONE RUNS FAST and RETURNS RESULTS.

>The Other Select statement:

Select * from ( Select Top 600 * from

....

>(PermitType_ID like @WorkTYPE ) and worktypeid is null

....

>TAKES FOREVER to COMPLETE.

>However IF I INCREASE the PAGESIZE from 600 to 800, BOTH QUERIES RETURN
RESULTS EQUALLY FAST.

Seems incredibly unlikely that the 600/800 would make a difference.

My guess would be that the "like @variable" can't know in advance that
the value will be single and have only a trialing wildcard, so it does
a scan instead of using the index. This uses a lot more of both CPU
and diskio. This would make it more sensitive to contention from
other system activities. Are you sure there was nothing else running
on the system when you were doing the comparisons?

J.