none
Weird problem with SP RRS feed

  • Question

  • I have a huge SP which is running forever. But when I the run the same query in the SP as a SQL Statement it's returning data in 8secs. What would be the issue? Have anyone came acorss this kind of thing. Please advise.  
    • Edited by guest369 Wednesday, August 29, 2012 10:06 PM
    Wednesday, August 29, 2012 10:06 PM

Answers

  • However if i create a stored procedure with the same statement and pass the id as a parameter it's running forever.

    Check to ensure the parameter data type matches the underlying column data type.  If there is a mismatch, SQL Server cannot use indexes efficiently if the column data type has a lower precedence.  If you still need help, plese pust create table statements, including indexes, and your proc code.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Friday, August 31, 2012 3:07 AM
  • Also i have another SP which will update the table is also taking too(10mis) long but when I execute the query in the SP as a sql statement it's executing right away. Is it something related to memory issue? Please advise.

    Unlikely it is a memory issue as that would cause slowness of both the ad-hoc SQL statement and proc.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Friday, August 31, 2012 3:08 AM

All replies

  • Chances are you have different execution plans.  You can verify this by displaying the estimated execution plans of both using SSMS.

    Are you using literal values or local variables in the SQL statement?  With literal values, SQL Server knows the exact values at compile time and can optimize the statement accordingly.  With local variables, the actual value is unknown so SQL Server optimizes the query using average value density based on statistics.

    With stored procedure parameters, SQL Server uses the actual parameter values when the proc is first called to generate the optimal plan for the supplied values and caches the plan for reuse.  An issue (parameter sniffing) can occur if the initial values are atypical of subsequent executions.  The same cached plan will be reused in subsequent proc executions, which may be suboptimal for other parameter values.  This can be addressed by adding OPTION (RECOMPILE) to the proc statement.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, August 29, 2012 10:33 PM
  • Example:

       Sql Statement:

    select * from tablename where id=1

    When I execute the above statement it is returning data very fast.

    However if i create a stored procedure with the same statement and pass the id as a parameter it's running forever.


    • Edited by guest369 Thursday, August 30, 2012 3:24 PM
    Thursday, August 30, 2012 3:23 PM
  • Also i have another SP which will update the table is also taking too(10mis) long but when I execute the query in the SP as a sql statement it's executing right away. Is it something related to memory issue? Please advise.


    • Edited by guest369 Thursday, August 30, 2012 3:34 PM
    Thursday, August 30, 2012 3:33 PM
  • However if i create a stored procedure with the same statement and pass the id as a parameter it's running forever.

    Check to ensure the parameter data type matches the underlying column data type.  If there is a mismatch, SQL Server cannot use indexes efficiently if the column data type has a lower precedence.  If you still need help, plese pust create table statements, including indexes, and your proc code.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Friday, August 31, 2012 3:07 AM
  • Also i have another SP which will update the table is also taking too(10mis) long but when I execute the query in the SP as a sql statement it's executing right away. Is it something related to memory issue? Please advise.

    Unlikely it is a memory issue as that would cause slowness of both the ad-hoc SQL statement and proc.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Friday, August 31, 2012 3:08 AM