|
|
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 ***
Navigation:
[Reply to this message]
|