Tag: Parameters

Uncategorized

Wow. I learned something very important about SQL today.

Posted on by derreckdean

Consider this SQL statement, containing a inline table-valued function (TVF)

SELECT *
FROM dbo.Games_StandardLotto_Summaries(
dbo.Date(DATEADD(D, -1, GETDATE())), 
dbo.EndOfDay(DATEADD(D, 1, GETDATE())), 
NULL, NULL, NULL);

It’s taking 17 seconds to run, and I cannot figure out WHY. I copied the guts of the TVF from a sql script I was tweaking for the good part of an hour. It’s a single line with CTEs that runs a fairly complex query in about a second or two – vast improvement from getting the same data from temp tables in 15-20 times the time. But now, it’s back to square one, it seems.

Unless…

DECLARE @sd DATETIME, @ed DATETIME;
SELECT @sd = dbo.Date(DATEADD(D, -1, GETDATE())), 
    @ed = dbo.EndOfDay(DATEADD(D, 1, GETDATE()));
SELECT * FROM dbo.Games_StandardLotto_Summaries(@sd, @ed, NULL, NULL, NULL);

…runs in under 1 second for me.

So what SQL is telling me here is that for TVFs, and I’m sure more than that, it uses the parameters by reference – meaning it executes the representation of the expression, rather than the value of the expression at the time the TVF is called.

The lesson learned here is that you should always declare and set your variables and use those to execute the TVF, rather than calling it with complex statements. As you can see above, I’m calling GETDATE(), then adding or subtracting a day from that value, and then running a scalar function to make it midnight or 11:59:59:999, twice per row minimum. Storing it into variables knocked the time down to next to nothing. Changing the SQL code above that calls the TVF resulted in a drop from 16.766 seconds to 0.275 seconds. That’s almost a 61x improvement!

Advertisement
0