# question

## 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] 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] Where is the problem and how to mitigate this issue? : /storage/temp/1675-bad.png : /storage/temp/1676-good.png
good.png (79.9 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
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]. If you search, Jeff has several other articles on tally table use and performance. : http://www.sqlservercentral.com/articles/Tally+Table/72993/
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
1 Like 1 ·

### Write an Answer Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.