CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
Andrew
Andrew

Reputation: 14526

Using the results of a WITH query in multiple subqueries

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.

Upvotes: 0

Views: 131

Answers (3)

paparazzo
paparazzo

Reputation: 45106

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.

Upvotes: 0

Mathese F
Mathese F

Reputation: 559

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 (......)

Upvotes: 0

SQB
SQB

Reputation: 4078

No, there is no way to do this.

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

Upvotes: 3

Related Questions