Function replacement for iterator table?

All:

One of the things that I didn't like about the iterator table that I have used for several years is that it adds a table to a schema that initially might look out of place. Over the past year I have been working at replacing the iterator table that I use as a utility with either an view that I could index or a function. I recently came accross this website when I was looking for something else:

http://stevekass.com/blog/page/2/

This function uses progressive set cross products within an inline function based on a CTE to generate a set cross product and then uses the ROW_NUMBER function to enumerate a set of integers. I tested this out and it seems really fast and this to me looks like a good candidate to replace my iterator table. Anyone else used this function as an iterator or something similar to it?

Dave

I forgot. I cannot use the function as use the function as part of an indexed view so I might want to stick with the table?|||

Yes, you cannot index a view that references such inline functions and you cannot index views that contains CTE in it's definition also.

Apart from this, there are other problems with the inline function approach. For example, the optimizer does not have real statistics on the number of rows so it makes a guess and this can cause serious performance problems depending on how you use the function. So you may want to compare this approach with the static table approach to see the pros and cons based on your workload. Other problems include higher compilation costs for the function (CPU usage) depending on the value of the parameters. The effect depends on whether your database server is constrained by CPU or disk or memory.

Below are some queries (only against the numbers table & tvf) that you could compare. Other cases are harder to generate because it depends on the use case.

set statistics io on

set statistics time on

go

select *

from dbo.tvf_Numbers(1, 1)

select *

from dbo.Numbers

where n = 1

declare @.n int

set @.n = 1

select *

from dbo.tvf_Numbers(1, 8000)

where n = @.n

select *

from dbo.Numbers

where n = @.n

select *

from dbo.tvf_Numbers(1, 255)

select *

from dbo.Numbers

where n between 1 and 255

go

set statistics io off

set statistics time off

go

I would simply stick with the table approach since it is easy to engineer and understand impact of it. The inline TVF approach cost is hidden due to the assumptions made and it is hard to comment when it will be beneficial or not (there are simply too many variables to consider).

|||

Thanks Umachandar,

That is exactly the kind of feedback that I was looking for. I appreciate the help.

Dave