SQL Server Performance Forum – Threads Archive
SQL Stored Proc Performance with isnull
Hi We use the following method to facilitate optional parameters in our stored procs. We have some bigger procs with 20 or so params. It turns out that SQL is forced to do a whole table scan every time. I have tried providing hints to force the proc to use the right index, but even tho it uses it, the proc still takes 30 seconds, where as the same query without nullable parameters takes less than 1 second. Any ideas how I can force this approach to be more optimal? Thanks Sacha CREATE PROCEDURE spGetHistCurve@varReference CHAR(20) = NULL,
@varHistCurveType CHAR(20) = NULL,
@varTradeDate DATETIME = NULL
AS
SELECT * FROM HistCurves
WHERE
(@varReference IS NULL OR Reference = @varReference) AND
(@varHistCurveType IS NULL OR HistCurveType = @varHistCurveType) AND
(@varTradeDate IS NULL OR TradeDate = @varTradeDate)
GO
This is one of the situations where dynamic SQL can improve performance … CREATE PROCEDURE spGetHistCurve
@varReference CHAR(20) = NULL,
@varHistCurveType CHAR(20) = NULL,
@varTradeDate DATETIME = NULL
AS DECLARE @Main NVARCHAR(4000), @AlreadyIn BIT SET @AlreadyIn = 0 SET @Main = N’SELECT * FROM dbo.HistCurves hc’
IF @varReference IS NOT NULL OR @varHistCurveType IS NOT NULL OR @varTradeDate IS NOT NULL
BEGIN
SET @Main = @Main + N’ WHERE ‘
IF @varReference IS NOT NULL
BEGIN
SET @Main = @Main + N’hc.Reference = @p1′
SET @AlreadyIn = 1
END
IF @varHistCurveType IS NOT NULL
BEGIN
SET @Main = @Main + CASE WHEN @AlreadyIn = 1 THEN N’ AND ‘ ELSE N” END + N’hc.HistCurveType = @p2’
SET @AlreadyIn = 1
END
IF @varTradeDate IS NOT NULL
BEGIN
SET @Main = @Main + CASE WHEN @AlreadyIn = 1 THEN N’ AND ‘ ELSE N” END + N’hc.TradeDate = @p3’
END
END EXEC dbo.sp_ExecuteSQL @Main, N’@p1 CHAR(20),@p2 CHAR(20),@p3 DATETIME’, @varReference, @varHistCurveType, @varTradeDate GO
Thanks for your reply Adriaan I feared as much. Its a shame, because then I lose all syntax checking. I was hoping there might be a way to get the optimizer to ‘optimize out’ the @var.. IS NULL check. I don’t know much about it, but read that the optimizer does take parameter values into account when doing its thing. It seems poor that it can’t use the index I have set up optimally – even with a hint to use it. Any further advice much appreciated. Thanks Sacha
Check out the articles athttp://www.sommarskog.se
Especially the two about "Dynamic SQL" and "Dynamic Searches" may turn out useful for you. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Syntax checking is a very important feature, but it doesn’t solve all your problems. There are plenty of errors that can happen at run-time in scripts that are syntactically perfect.
SELECT * FROM HistCurves
WHERE
Reference =
CASE
WHEN @varReference IS NULL THEN Reference
ELSE @varReference
END
AND HistCurveType =
CASE
WHEN @varHistCurveType IS NULL THEN HistCurveType
ELSE @varHistCurveType
END
AND TradeDate =
CASE
WHEN @varTradeDate IS NULL THEN TradeDate
ELSE @varTradeDate
END
]]>