Strange Performance question


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

Relevant Pages

  • Re: MIN() + MAX() Deadlock ?
    ... The only difference between the statements is with (nolock). ... if the second statement is trying to use parellelism in its query plan. ... > Select minminTime, maxmaxTime From TblAvlTrans ... > Where UnitID = '1720200022285010001407' ...
    (microsoft.public.sqlserver.programming)
  • A query with too many joins...
    ... I have a query that makes tempdb as large as 20GB and exceed the disk ... join EXTENDED_ACC_SET eac (nolock) on ... These and other tables used in such queries are ...
    (microsoft.public.sqlserver)
  • Re: SQL tuning
    ... Does SQL Server optimizer use indexes to perfom the query? ... It might be that otpimizer uses a 'bad' execution plan ... > FROM dbo.QuarterlyRunPortfolio qrp (NOLOCK) INNER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Performance Problem
    ... FROM dbo.Task T (NOLOCK) ... INNER JOIN dbo.StateMaster ON StateMaster.Id = WO.StatusId ... WorkOrder table is master table which consists of 155986 rows. ... For executing the above query it is taking 1.7 sec. ...
    (comp.databases.ms-sqlserver)
  • Re: Access and equiv of Transact-SQLs Nolock hint?
    ... > uncommitted transaction might be rolled back. ... > Why don't you just test your queries in the SQL Query Analyzer & see ... I have had to use NOLOCK on a few occasions where I ... was using a data table as the source of a ComboBox RowSource. ...
    (microsoft.public.access.queries)