Posted by Auday Alwash on 11/28/05 03:42

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

[Back to original message]