asked
Optimizer plays havoc with tally numbers
Hi! I'm trying to generate numbers for financial calculation. Therefore I'm using a Tally Table Function based on an article by Itzik Ben-Gan: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TallyNumbers_udf]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[TallyNumbers_udf]; GO CREATE FUNCTION [dbo].[TallyNumbers_udf] ( @Min NUMERIC(34,4) ,@Max NUMERIC(34,4) ,@StepWidth NUMERIC(34,4) ) RETURNS TABLE AS RETURN WITH T0(N) AS (SELECT 1 UNION ALL SELECT 1) ,T1(N) AS (SELECT 1 FROM T0 a CROSS JOIN T0 b) ,T2(N) AS (SELECT 1 FROM T1 a CROSS JOIN T1 b) ,T3(N) AS (SELECT 1 FROM T2 a CROSS JOIN T2 b) ,T4(N) AS (SELECT 1 FROM T3 a CROSS JOIN T3 b) ,T5(N) AS (SELECT 1 FROM T4 a CROSS JOIN T4 b) ,Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM T5) SELECT N * (@StepWidth) AS Node FROM Tally WHERE Tally.N = @Min / @StepWidth; GO When I run my code: ;WITH Data AS ( SELECT 'x' AS [KeyValue], 1 AS [NumValue] UNION SELECT 'x', 2 UNION SELECT 'x', 3 ) ,Statistic AS ( SELECT KeyValue ,MIN(NumValue) AS MinValue ,CAST(MAX(NumValue) AS NUMERIC(34,4)) AS MaxValue ,COUNT(KeyValue) AS NumberOfRecords ,STDEV(NumValue) AS StdDev FROM Data GROUP BY KeyValue ) SELECT * FROM Statistic AS S CROSS APPLY dbo.TallyNumbers_udf(S.MinValue, S.MaxValue, 0.01) AS T The query runs for ever... And the plan looks like that: (*Bad.sqlplan*) ![Bad][1] Changing the **S.MaxValue** argument to a fix value of **3** (which is the maximum value in my actual data set) like this: ... CROSS APPLY dbo.TallyNumbers_udf(S.MinValue, 3, 0.01) AS T ... solves the problem completely and the query is done in 3 ms. See Plan *Good.sqlplan* ![Good][2] Where is the problem and how to mitigate this issue? [1]: /storage/temp/1675-bad.png [2]: /storage/temp/1676-good.png
It all comes down to the statistics being generated. When the tally table is unlimited in scope, or there's not an easily defined limit by the optimizer, it's going to assume a huge number, hence the giant lines. I prefer to use a physical table of numbers in the database rather than a function. It usually runs faster. For other hints on how to make a tally table work faster, I'd suggest reading from the master, [Jeff Moden][1]. If you search, Jeff has several other articles on tally table use and performance. [1]:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thanks for the explanation, Grant! I've solved the problem by adding a SELECT TOP (@N) statement to the function. Now the query runs fine. I will definitely fall back to the physical table if other (performance) issues occur. Jeff Moden's stuff is amazing and has been a great inspiration:
http://stackoverflow.com/a/2663232/1194945
15 People are following this question.
