Urgent Exec. time of SP btw Query Analyser and ASP | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Urgent Exec. time of SP btw Query Analyser and ASP

hi Experts,
i have a problem in executing a sp through Query Analyser and ASP command object.
The execution time of sp in Query Analyser is taking 2 sec and the same exec statement is getting time out through ADO command object in ASP.
exec USP_ENQUIRY_SEARCH @ENROLMENT_NO=’BLR’,@GROUP_NAME=’IGATE’
and all remaining queries r working fine and execution time between Query Analyser and ASP also same.so there will not be a problem of Networking stuff. Iam recompiled the sp so many times and i rebuiled the indexes that iam using in the query also and i updated the statistics also..but the same problem is repeating..
what will be the problem?can any one guide me.
I suggest using Profiler to look for all of the SQL that is actually fired off by the ASP page… Cheers
Twan
Are you setting the CommanTimeout property for the commandobject? If so what is the value you have it set? Refer to this linkhttp://vyaskn.tripod.com/watch_your_timeouts.htm for timeout guide and resolve them. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I had a similar problem and it was down to SQL using different cached execution plans. Adding a recompile to the SP fixed it for me.
]]>