CODEWITHSUNDEEP
c#t-sql
Sam
Sam

Reputation: 5677

Cmd.Execute times out

172 Points

206 Posts Wierd Cmd.ExecuteReader performance problem 23 hours, 8 minutes ago|LINK

I have the following query that gets built up:

SELECT TOP 14 DocumentId
 FROM idx1_AuthLetters
a INNER JOIN Documents b ON a.DocumentId = b.Id
WHERE Status in ('L','S','V')
AND  ServiceCenter = 'NC'
AND  mem_name like '%ZZZ%'
ORDER BY DOCUMENTID

when I run this in the Sql Server Management Studio Query window, it runs it a split second. Very fast.

For this query I get 0 records back.

The thing is I am building this query dynamically. so if I put a break point right before the query executes, and inspect the cmd object, This is what I see:

 SELECT TOP 14 DocumentId 
 FROM idx1_AuthLetters a 
 INNER JOIN Documents b ON     
 a.DocumentId = b.Id 
 WHERE Status in ('L','S','V')  
 AND  ServiceCenter = @1  
 AND  mem_name like @2 
 ORDER BY DOCUMENTID

And the parameter values are

@1: NC

@2: %ZZZ%

The funny thing is that if there are records returned, not 0 records, then everything works fine.

An example is the same query build up with different paramater values:

SELECT TOP 14 DocumentId 
FROM idx1_AuthLetters a 
INNER JOIN Documents b 
ON a.DocumentId = b.Id 
WHERE Status in ('L','S','V')  
AND  ServiceCenter = @1  
AND  mem_name like @2 
ORDER BY DOCUMENTID

@1: NC

@2: %JOHN%

This is a recent problem. Both test cases used to work fine for me but now 0 records returned causes the cmd.ExecuteReader() line to timeout.

Also, we have the same problem in Oracle. Although it does not timeout - it is just a little slow in the Oracle version.

This query returning 0 records in Oracle takes 11 seconds as opposed to 0 or 1 seconds for a query that does contain records.

What could cause this?

Upvotes: 1

Views: 119

Answers (2)

Yuriy Galanter
Yuriy Galanter

Reputation: 39777

Try adding OPTION (RECOMPILE) to your dynamically built query, e.g. build it like

SELECT TOP 14 DocumentId 
FROM idx1_AuthLetters a 
INNER JOIN Documents b 
ON a.DocumentId = b.Id 
WHERE Status in ('L','S','V')  
AND  ServiceCenter = @1  
AND  mem_name like @2 
ORDER BY DOCUMENTID
OPTION (RECOMPILE)

This will avoid parameter sniffing which is often cause of slow performance when SQL statement/stored procedure is executed from .NET code

Upvotes: 1

Boris B.
Boris B.

Reputation: 5024

In SQL Server Management Studio there is a great feature called Execution Plan visualization. Execute your query with execution plan and you will get the (literal) picture of how your query is processed on the server. Look for nested loops with table scans.

This is usually a problem with non-indexed or improperly indexed databases, but without additional info on the database structure I think that no one could help you properly.

EDIT: If your problem only happens when you access your DB from .Net code you should utilize SQL Server Profiler (Management Studio -> Tools -> SQL Server Profiler), start a trace on your DB and then run the query in your client. The Profiler trace will show the actual query that gets executed (you may have to edit a little because of params), and then use that query with Execution Plan.

Upvotes: 0

Related Questions