Posted by Erland Sommarskog on 11/28/05 13:44

Auday Alwash (aalwash@tollnz.co.nz) writes:
> I have a really interesting one for you guys...

Nah, sorry to disappoint to, but this optimizer basics.

> If I run this query:
>
> declare @find varchar(50)
>
> SET @find = 'TTLD0006423203'
>
> SELECT TOP 250
> ConsignmentID,
> c.Created
> FROM tblConsignment c WITH (NOLOCK)
> WHERE c.ConNoteNum LIKE @find + '%'
> ORDER BY c.Created DESC
>
> It takes 5 - 7 seconds with an Index Scan on the Consignment Table
>
> HOWEVER, if I run either of the next two queries below they are instant
> (under 1 second) with no scan only an Index Seek ..
>...
> SELECT TOP 250
> ConsignmentID,
> c.Created
> FROM tranzbranch_archive.dbo.tblConsignment c WITH (NOLOCK)
> LEFT JOIN tblCustomer cu WITH (NOLOCK) ON c.FreightPayerCode =
> cu.CustCode
> WHERE c.ConNoteNum LIKE 'ttld0006423203%'
> ORDER BY c.Created DESC

The optimizer in SQL 2000 optimizes an entire batch at a time. This means
that its blind to variable values, but applies a standard guess. On the
other hand, in the fast query, the optimizer knows exactly what you are
looking for.

Particular in this case, the optimizer knows in the latter case that it
can perform an Index Seek, but in the first case, it has no idea whether
@find starts with % or not.

As you have discovered you can use an index hint to convince the optimizer
to use the index, but keep in mind that if @find starts with a %, this
will be very expensive - a lot more expensive than 5-7 seconds.

You have three options:
1 - The index hint.
2 - Dynamic SQL.
3 - Move the SELECT to an inner stored procedure, and pass @find as
parameter. For parameters, SQL Server do look at the value, and
may choose the plan with Index Seek.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]