asked
ITVFs, CTEs, and temp tables
I have a stored procedure in which a chain of temp tables is used. I want to convert the sproc to a ITVF, but of course temp tables can't be used in ITVFs. So I converted my chain of temp tables to a chain of CTEs. Doing this means it takes 5 to 10 times longer to run. The query plan seems to suggest that the same CTEs are created multiple times through the process, whereas the temp tables are only created once and reused. Does this sound right? Are there other alternatives to temp tables and CTEs that can be used in ITVFs?
It is perfectly correct behavior. CTEs are logical breakdowns. Take them as subqueries instead (Although are more flexible). So if you keep using a CTE again and again, it will be like calling a subquery again and again. With temp tables, the data could move to cache and continuous call will cost less resources. Moreover, you can have indexes on temp tables to gain more performance. For rest of your queries, as Kev Riley said, give us more details.
Probably better if you could post some code. I don't quite understand why you would want to turn a SP into a UDF. Yes one of the pitfalls of UDFs is multiple invocation of the code, hence why you are seeing it recreate the CTEs everytime.
@Kev Riley I can think of the typical requirement when you have to call the procedure again and again against some table values. And the INSERT EXEC scenario we are also familiar with. But I beleive in that case, code re-usability should not be the first choice. It can be handled differently.
Just remember, a CTE is not a table, it's a query. Temp tables are actual methods of persistence, even if only temporary, so, depending on how you're referring to these different objects, they will absolutely behave in different ways. Don't conflate the two and think of them the same way because they are very different.
@usman > "...when you have to call the procedure > again and again against some table > values..." Yes, that's precisely the case. Either/both options are intended to replace a scalar function that has to do some horribly convoluted calculations involving multiple selects of other tables. The problem with the scalar function was, of course, that it was performing these calcs row by row (or is some cases (row by row)^3). The bigger problem was that it was the only thing that would come up with the right answers at the time so it got implemented, like a cancer, throughout the organization's codebase. We've improved the execution time of a number of queries using this scalar function from hours to seconds using the sproc, and minutes using the ITVF. It's going to be easier to implement the ITVF than the sproc... assuming performance is comparable. I'd rather not post the code unfortunately for two reasons 1) my organization is very thingy about trade secrets and so on, and 2) It's hundreds of lines long. It's not that I'm self conscious.... no seriously... ;-) @Grant: Are there methods of persistence that can be used in ITVFs? Are table variables an option?
No, the inline function is just a query call, no persistance. If you're doing lots and lots of calculations within TSQL, stop. It's better to store those calculations within tables or indexed views. This sort of coding inevitably leads to issues.
Because TSQL is so very powerful it's easy to forget that as a programming language, it kind of sucks. It really is about Querying, and the rest is not as well structured.
5 People are following this question.
