Posted by JXStern on 01/26/07 16:27

On 26 Jan 2007 05:56:21 -0800, "rbg" <rbg.net@gmail.com> wrote:

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

• Next in forum: Re: The age old argument of Temp table vs Table variable
• Prev in forum: Input Paramter as part of OPENDATASOURCE
• Thread view: Re: Paging records on SQL 2000 : Followup question

[Reply to this message]