Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Help Fix Slow Query.

    I have a query that is taking too long to run. It take 14 seconds to return 6800 rows. However, if I move the query out of a stored proc, it takes 1 second. I want to understand this issue and ideally fix the stored proc case.

    I've simplified my actual queries for readability.

    Code:
    -- @filter is value to filter against or NULL to return all records.
    CREATE PROCEDURE queryPlayerStations(@filter INTEGER)
    AS
    	SELECT * FROM MyTable
    	-- Other joins and query logic omitted for brevity
    	WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
    GO
    
    DECLARE @filter INTEGER
    SET @filter = NULL
    
    -- Takes 14 seconds to return 6800 rows. That's unacceptable performance
    EXEC dbo.queryPlayerStations @filter
    When I run the query directly in Query Analyzer, it runs very fast.

    Code:
    DECLARE @filter INTEGER
    SET @filter = NULL
    
    -- Takes ~1 second to return 6800 rows. That's great performance
    SELECT * FROM MyTable
    -- Other joins and query logic omitted for brevity
    WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
    When I put the parameters in the stored proc it runs fast.

    Code:
    CREATE PROCEDURE queryPlayerStations
    AS
    	DECLARE @filter INTEGER
    	SET @filter = NULL
    
    	SELECT * FROM MyTable
    	-- Other joins and query logic omitted for brevity
    	WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
    GO
    
    -- Takes ~1 second to return 6800 rows. That's great performance
    EXEC dbo.queryPlayerStations
    Anyone have any ideas what I can do to improve the stored proc case?

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Just a quick *guess* before I leave office for tonight...

    The optimization in SQL Server works differently depending
    on where the parameter is defined (as a procedure call argument or inside using DECLARE). In one of the cases,
    it doesn't have enough info to optimize in the best way.

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    As Coolberg implied, what happens if you do this:

    Code:
    ALTER PROCEDURE queryPlayerStations(@filterIN INTEGER)
    AS
    DECLARE @filter INTEGER
    SET @filter = @filterIN
    
    SELECT * FROM MyTable
    -- Other joins and query logic omitted for brevity
    WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
    GO
    
    
    DECLARE @filterIN INTEGER
    SET @filterIN = NULL
    EXEC dbo.queryPlayerStations @filterIN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •