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?
more ▼

asked Feb 23 '12 at 01:00 PM in Default

GPO gravatar image

1.9k 32 35 39

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered Feb 23 '12 at 01:27 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Feb 23 '12 at 02:10 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Feb 23 '12 at 01:04 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

@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.
Feb 23 '12 at 01:31 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left


"...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?
more ▼

answered Feb 23 '12 at 09:09 PM

GPO gravatar image

1.9k 32 35 39

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.
Feb 23 '12 at 09:13 PM Grant Fritchey ♦♦
"...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.
Feb 23 '12 at 09:32 PM GPO
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.
Feb 23 '12 at 09:37 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 23 '12 at 01:00 PM

Seen: 788 times

Last Updated: Feb 23 '12 at 09:38 PM