October 2, 2009 at 7:35 am
We are using an ORM that is executing a call from .NET to SQL Server's sp_executesql stored procedure.
When the stored proc is called from .NET, we receive a timeout exception.
Looking at Profiler, I can see that the query is indeed taking a long time to execute.
The query is essentially:
exec sp_executesql N'SELECT DISTINCT FROM
[OurDatabase].[dbo].[Contract] [LPLA_1] ) [LPA_L1] LEFT JOIN [OurDatabase].[dbo].[Customer] [LPA_L2] ON
[LPA_L2].[Customer_ID]=[LPA_L1].[CustomerId] AND [LPA_L2].[Data]=[LPA_L1].[Data]) WHERE ( ( ( ( ( [LPA_L1].[DealerId] = @DealerId1))
AND ( [LPA_L2].[Last_Name] = @LastName2))))',N'@DealerId1 varchar(18),@LastName2 varchar(25)',@DealerId1='1234',@LastName2='SMITH'
The confusing part for me is this: If I copy and paste the query that's timing out into SQL Management studio and execute it interactively, it executes just fine.
Does anyone know why the same query would take significantly longer when executed via .NET code? (I'm able to reproduce this -- the query executed from code consistently times out, and the query executed interactively consistently works fine.)
Any help is appreciated. Thanks!
October 2, 2009 at 8:49 am
Hmmm... Have you looked at the execution plans for the quick & the slow execution? Check the connection properties from the ORM tools to see if they're setting something differently than you are through Management Studio. And when you say you're running the script the same way, you're running the sp_executesql in SSMS too?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 2, 2009 at 8:51 am
Dont discount that possibility that your .Net app could be pulling the rows slower than SSMS
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply