Strange Performance question

[Auday Alwash]

Hi,

I have a really interesting one for you guys...

SQL Server 2000 sp3 on Windows Server 2003

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

declare @find2 varchar(50),
@SQL nvarchar(4000)

SET @find2 = 'TTLD0006423203'

SET @SQL = '
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 ''' + @find2 + '%''
ORDER BY c.Created DESC'

execute sp_executesql @stmt = @SQL

OR

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

Can you please help me as this is causing Huge issues in our Live system
and I really don't want to rewrite 400+ stored procedures!!!!

Thank you thank you thank you in advance....

:-)

Auday
*** Sent via Developersdex http://www.developersdex.com ***

[Auday Alwash]

Further to this:

If I change the first Statement to use an Index Hint it works (index
seek in under a second)!!!! Is something wrong with my SQL Execution
Plan optimizer or something???

declare @find varchar(50)

SET @find = 'TTLD0006423203'

SELECT TOP 250
ConsignmentID,
c.Created
FROM tranzbranch_archive.dbo.tblConsignment c WITH (NOLOCK,INDEX
(ix_tblconsignment))
WHERE c.ConNoteNum LIKE @find + '%'
ORDER BY c.Created DESC

Please Help!!!!

Auday

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Auday Alwash (aa*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx