SQL Server: Query fast, but slow from procedure

by

I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure.

I found another answer here “Parameter Sniffing”, Thanks Omnibuzz. Boils down to using “local Variables” in your stored procedure queries, but read the original for more understanding, it’s a great write up. e.g.

Slow way:

CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
    SELECT * 
    FROM orders
    WHERE customerid = @CustID
END

Fast way:

CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
    DECLARE @LocCustID varchar(20)
    SET @LocCustID = @CustID

    SELECT * 
    FROM orders
    WHERE customerid = @LocCustID
END

Doing this reduced my execution time from 5+ minutes to about 6-7 seconds.

More Related Contents:

  1. What makes a SQL statement sargable?
  2. What do Clustered and Non-Clustered index actually mean?
  3. Select columns from result set of stored procedure
  4. Escape a string in SQL Server so that it is safe to use in LIKE expression
  5. XML Server XML performance optimization
  6. Retrieve column definition for stored procedure result set
  7. Using varchar(MAX) vs TEXT on SQL Server
  8. Dapper.NET and stored proc with multiple result sets
  9. SQL Server pivot vs. multiple join
  10. LIKE vs CONTAINS on SQL Server
  11. T-SQL Dynamic SQL and Temp Tables
  12. SQL Server Query: Fast with Literal but Slow with Variable
  13. How do I find a stored procedure containing ?
  14. Calling an API from SQL Server stored procedure
  15. Handling date in SQL Server
  16. SQL Server history table – populate through SP or Trigger?
  17. Retrieve data from stored procedure which has multiple result sets
  18. cross apply xml query performs exponentially worse as xml document grows
  19. Calling stored procedure using VBA
  20. Stored Procedure and Permissions – Is EXECUTE enough?
  21. Avoid Naming User Stored Procedures SP% or SP_%
  22. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  23. Stored procedure – return identity as output parameter or scalar
  24. Which is quicker COALESCE OR ISNULL?
  25. How to find a text inside SQL Server procedures / triggers?
  26. Python – pyodbc call stored procedure with parameter name
  27. SQL Server stored procedure return code oddity
  28. Populate Dataset With Table Names From Stored Procedure
  29. How to cleanse (prevent SQL injection) dynamic SQL in SQL Server?
  30. ODBC Call Failed with stored procedure – Pass through query

Leave a Comment