1

I thought CTEs were perfect for my stored procedure, until I found out they can only be referenced in ONE query (ie the query immediately following the CTE). They now seem fairly pointless.

I'm looking for a way to perform a query, store that result set in memory (not on disk like a temp table or table variable), and then reference it within a stored procedure in multiple SELECT/INSERT/UPDATE statements. Just like a temporary view, or a CTE with a longer scope. But that doesn't seem to exist at all in SQL Server! Does anyone have a solution? Doesn't this seem rather short-sighted on behalf of the CTE functionality?

tuseau
  • 1,334
  • 4
  • 17
  • 37
  • 4
    Why are you objecting to using a table variable or temp table? It sounds like exactly what you're asking for. – Joe Stefanelli Feb 22 '11 at 14:10
  • I agree with Joe. If you need to use a CTE in multiple queries I guess you can redefine it and re-execute it. But why would you want to do that? My understanding is that the scenario where you want to use CTEs is to build queries that are more readable/maintainable than nested queries. Your case looks like the textbook use-case for table variables. – Paolo Falabella Feb 22 '11 at 14:24
  • Well for performance reasons I want to minimize I/O reads, especially with regards to the tempdb. Therefore I'm thinking storing a result set in memory would be much faster. Maybe I'm misunderstanding the way a result set is stored? I don't think redefining the CTE is a solution since it would have to re-read everything from the original table again. – tuseau Feb 22 '11 at 14:44
  • For example, I can see from the execution plans of 2 identical queries that the INSERT cost of creating a temp table or table variable adds enormously to the cost of the operation. The same operation can be done using select query/CTE in 10% of the time it takes to insert then select a result set into/from a temp table. – tuseau Feb 22 '11 at 14:59
  • **"Premature optimization is the root of all evil."** Are you having a performance issue? If not, use the tools that exist and see how they function. You can't extrapolate results in SQL Server based on small-scale testing. – JNK Feb 22 '11 at 15:17
  • Yes, I'm re-writing a very large sproc that is using temp tables a lot (selects, inserts, updates). I have identified the I/O to the tempdb as the cause of performance problems. It just seems like there should be a way of using a cte in more than one query. Otherwise, they're basically adding nothing. – tuseau Feb 22 '11 at 15:21
  • 1
    @tuseau actually performance-wise (especially in scenarios where you insert/update many rows) temp tables outperform CTEs. See here for some good considerations on performance: http://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables – Paolo Falabella Feb 22 '11 at 15:23
  • The thing is, a CTE was far out-performing my temp table in this sproc. The reason is because it is dealing with huge result sets (millions) but when it came to doing more than one query, I realized I would just have to create the CTE all over again. It seems like a shame that there's no way to store a result set in memory for usage in more than one query. – tuseau Feb 22 '11 at 15:34
  • @tuseau: Without knowing more about your stored procedure and scenario it's hard to generalize... Anyway, especially since you're talking about millions of records, I should point out that if more than one user or process is running the same stored procedure, SQL might stop on one run waiting for enough RAM to be free to run the sp again. So the performance gain you see when running your sp might disappear with multiple users – Paolo Falabella Feb 22 '11 at 15:49
  • That's true paolo. Thanks for the comments everyone, it looks like loading the result sets into a table variable is the best option. According to my testing, a table variable is outperforming temp tables substantially, and should also outperform re-creating the cte multiple times. – tuseau Feb 22 '11 at 16:02

1 Answers1

0

I agree with the comments. I also don't know precisely how the following would be done, but I think it would work. With that said: have your query generate XML output, store that output in an XML variable, and then use xquery to reference and extract data from it for the rest of the procedure. (But again, I'm not at all sure you can write a "SELECT @XML = ..." query. Maybe OPENXML, or something like that?)

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92