Paging records on SQL 2000 : Followup question

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

Quote:

Originally Posted by

>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

Quote:

Originally Posted by

>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 helpYou are right, I did not include the exact query since it has a whole

Quote:

Originally Posted by

of joins and many where clauses in it.

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

A|||On 26 Jan 2007 05:56:21 -0800, "rbg" <rbg.net@.gmail.comwrote:

Quote:

Originally Posted by

>Select * from ( Select Top 600 * from

...

Quote:

Originally Posted by

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

...

Quote:

Originally Posted by

>THIS ONE RUNS FAST and RETURNS RESULTS.

Quote:

Originally Posted by

>The Other Select statement:
>
>Select * from ( Select Top 600 * from

...

Quote:

Originally Posted by

>(PermitType_ID like @.WorkTYPE ) and worktypeid is null

...

Quote:

Originally Posted by

>TAKES FOREVER to COMPLETE.

Quote:

Originally Posted by

>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.