question

GPO avatar image
GPO 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?
ctetemporary-tableinline-table-valued-function
10 |1200 characters needed characters left characters exceeded

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

Usman Butt avatar image
Usman Butt answered
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.
10 |1200 characters needed characters left characters exceeded

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

Kev Riley avatar image
Kev Riley answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

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

@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.
3 Likes 3 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

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

GPO avatar image
GPO answered
@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?
3 comments
10 |1200 characters needed characters left characters exceeded

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

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.
2 Likes 2 ·
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.
2 Likes 2 ·
"...It's better to store those calculations within tables..." Fair call. Even though we've made significant inroads into the problem, sounds like we might have to rethink the strategy.
0 Likes 0 ·

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.