Paging records on SQL 2000 : Followup question

Paging records on SQL 2000 : Followup question

Post by rbg » Sat, 27 Jan 2007 22:56:21


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 Permit *** ame, Permit *** umber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate
on PermitMain.Permit *** umber = 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 Permit *** umber = @Permit *** umber
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 Permit *** ame, Permit *** umber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate
on PermitMain.Permit *** umber = 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 Permit *** umber = @Permit *** umber

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
 
 
 

Paging records on SQL 2000 : Followup question

Post by Aaron Bert » Sat, 27 Jan 2007 23:26:45

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

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

A

 
 
 

Paging records on SQL 2000 : Followup question

Post by JXSter » Sun, 28 Jan 2007 01:27:30


...
...

...
...



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.