SQL Server Performance

Urgent Exec. time of SP btw Query Analyser and ASP

Discussion in 'T-SQL Performance Tuning for Developers' started by gupta_nag, Dec 7, 2003.

  1. gupta_nag New Member

    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.
  2. Twan New Member

    I suggest using Profiler to look for all of the SQL that is actually fired off by the ASP page...

    Cheers
    Twan
  3. satya Moderator

    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
  4. Dave Wells New Member

    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.

Share This Page