asked
TVF is slower but less IO than stored proc
I have 3 tables; customers has 202,571 rows, Flags has 29,913,158 and CustomerHistory has 7,261,134. In order to aggregate some data I have an option to write a stored proc that builds two CTE's and combines the results into one result set or use a table valued function and OUTER APPLY. This function takes the ID from CustomerHistory and references a single row (by virtue of needing two columns with specified values) in Flags. In execution tests the sp method takes approx 1s whereas the TVF method is approx 5s. Can someone please explain these values from Statistics IO? TVF (8 row(s) affected) Table '#1843406A'. Scan count 2247, logical reads 2247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'CustomerHistory'. Scan count 202571, logical reads 2105592, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Customers'. Scan count 1, logical reads 27917, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SP (8 row(s) affected) Table 'CustomerHistory'. Scan count 405142, logical reads 2502156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Customers'. Scan count 10, logical reads 61422, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Companies'. Scan count 0, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'FLAGS'. Scan count 1444, logical reads 7786, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. The way I am reading them is that the TVF is doing less work. Does that mean the CPU is doing the lifting for the function and I should stick with the s.proc? cheers
If the TVF is an inline function, I'd consider using it. If it's a multi-statement TVF, I'd toss it out of hand, regardless of apparent performance enhancements. It's really that simple. As to which is actually doing fewer reads, it appears it's the TVF, but you need to be careful because this can be masked to a degree. Have you compared the values returned by RPC or SQL Batch completion events? They might paint a slightly different picture.
+1, but I'd be a little bit less harsh on multi-statement TVFs. I certainly wouldn't use one in an OUTER APPLY, or any other sort of large load environment, but for simple management style tasks I think they can be useful for encapsulating functionality. Same as I don't really have a problem using cursors for management tasks (e.g. iterating a list of tables to issue DBCC DBREINDEX commands)...
The TVF returns a single value, it essentially looks up the customer ref and then within that range a flag number and returns the flag value - 1 or 0. This is similar to a BIT column but we dont have sparse column-ability. The calling script needs the result of the TVF is used to ensure only clients with a history that has flag value of 1 are counted.
