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.

-- @.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.

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.

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?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.|||As Coolberg implied, what happens if you do this:

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