Using the results of a WITH query in multiple subqueries

Question

I would like to make a single round trip to an MSSQL database to retrieve multiple tables. Each table relies in one way or another on the same somewhat expensive lookup, which will grab a bunch of IDs and handle pagination and very basic filtering. So I'm trying to move the expensive part out so it happens only once, and reuse its result set multiple times afterwards.

Below is a really stripped-down version of what I'm trying to do. The "primary query" is the expensive lookup that's common to the next other queries below it. The "first result set" should return what I want, as you would expect, but the "second result set" fails because the primary query is out of scope.

-- Primary query
WITH t(ItemId, Row) AS (
    SELECT ItemId, ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row
    FROM Items
)

-- First result set
SELECT * 
FROM Items
INNER JOIN t ON Items.ItemId = t.ItemId
WHERE t.Row < 10

-- Second result set
SELECT *
FROM Photos
INNER JOIN ItemPhotos ON Photos.PhotoId = ItemPhotos.PhotoId
INNER JOIN t ON ItemPhotos.ItemId = t.ItemId
WHERE t.Row < 10

Is there a way to do this so that the second result set works?

I would like to avoid creating temp tables because, in my experience, there is almost always a cheaper alternative that I've just not learned yet. In this case, I'm not sure there's an alternative, but I'm hoping someone knows a way around it. (I'll test both of course.)

I know that in the example above you can probably return a single result set by doing an INNER JOIN on the whole thing, but in my case it's not a workable solution as the result set would be massive.

Why would the result set be massive? To me it looks as if you could simply top 10 the ItemId's ordered by DateCreated and join on that. With an Index on DateCreated and the join id's that can't be problematic or am I missing something? — Anton, Dec 06 '13 at 13:51
Even if the syntax did work it would not eliminate round trips to the database. A CTE is just syntax. — paparazzo, Dec 06 '13 at 14:04
@Anton From what I understood, this is just a simplified example. — SQB, Dec 06 '13 at 14:15
Fair comment, @Anton. The example above is pretty simplified. I can't post the full example, but the query will have actual pagination (`t.Row > 1740 AND t.Row <= 1760`), and there were be at least four subqueries rather than two, each result set is quite wide already, and some of those subqueries will result in many rows per "Item". I lean toward normalization more that most people, but this will be too much. — Andrew, Dec 06 '13 at 14:18

score 3 · Accepted Answer · answered Dec 06 '13 at 13:44

3

No, there is no way to do this.

What you can do, is use temp tables, like you said, or use a materialized view.

answered Dec 06 '13 at 13:44

SQB

3,926
2
28
49

Mathese F · Answer 2 · 2013-12-06T14:47:01.213

0

if you don't want to use temporary table you can use a variable table like this : (changed due to an error Thanks to Aaron)

declare @mytable (......)

edited Dec 06 '13 at 14:47

answered Dec 06 '13 at 13:48

Mathese F

559
4
9

No, this is completely wrong. [Perhaps you should read this](http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386). – Aaron Bertrand Dec 06 '13 at 14:09
Thanks, yes sorry i interpreted to quickly a new functionnality of 2014 (OLTP). Here is a link comparing variable table to temporary table http://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server – Mathese F Dec 06 '13 at 14:25
1

Now that you know that a table variable is no different from a temporary table in terms of storage location, how can you keep thinking that the result of a WITH clause is the same for the optimiser as the contents of a table variable? – Andriy M Dec 06 '13 at 14:40
You confused a Hekaton (in-memory OLTP) table with a table variable? – Aaron Bertrand Dec 06 '13 at 14:44
2

This is still no good. Why would a table variable be better? Because its name doesn't start with a `#`? The OP has issues with a temp table not because of that but because they are looking for a cheaper alternative. Some aspects of table variables (like absence of column statistics, very limited (prior to 2014) indexing capabilities) may make them even a more expensive one. Sorry, I still disagree with your suggestion. – Andriy M Dec 06 '13 at 15:09

paparazzo · Answer 3 · 2013-12-06T16:43:25.333

As stated in my comment even if that syntax worked it would not achieve the objective of a single trip to the database as a CTE is just syntax.

Given the t.Row > 1740 AND t.Row <= 1760 I would go #temp over temporary table
I like the simplicity of a temporary table but it does not query optimize well

This assumes ItemID is a PK

If you are going to create a #temp then put some structure in it to make the joins as efficient as possible.

The order by in the insert will minimize (or eliminate) fragmentation on the PK

The #temp.rn in the join rather than the where gives the query optimizer a chance to filter before the join

IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL   DROP TABLE #temp;
CREATE TABLE #temp (ItemId INT PRIMARY KEY CLUSTERED, rn INT);  

insert into #temp 
SELECT sID, ROW_NUMBER() OVER(ORDER BY addDate DESC) AS Row
  FROM docSVsys 
 where sID < 10000
 ORDER by sID;

select count(*) from #temp;

CREATE UNIQUE NONCLUSTERED INDEX [IX] ON #temp ([rn] ASC);

select docSVtext.value
  from docSVtext 
  join #temp 
    on docSVtext.sID = #temp.ItemID 
   and #temp.rn >= 100
   and #temp.rn  < 200;

select docSVdate.value
  from docSVdate 
  join #temp 
    on docSVdate.sID = #temp.ItemID 
   and #temp.rn >= 100
   and #temp.rn  < 200; 

IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL   DROP TABLE #temp;

Another option is a #temp2 that you insert the rows for a single condition join.

Using the results of a WITH query in multiple subqueries

3 Answers3