1

I am creating a stored procedure in sql server 2019 that needs to use multiple select statements to get a parent row and then its related data. I have the primary key clustered value for the parent so the first query will at most return 1 row.

Should I select everything into a temp table for the first query and then join my subsequent queries to the temp or should I just keep joining to the original table?

I am not sure if the overhead of creating the temp table will overshadow the overhead of joining to the actual table repeatedly.

I have looked at the performance plans and they come out to be the same and the statistics for reads/scans and time are about the same as well.

I think what I am trying to figure out is if I use the temp table, will it relieve pressure on the original table. The original table is heavily read and written to.

I should note that these statements will be inside of a Stored Procedure so I may potentially get a boost from Temporary Object Caching.

Assume table A has > 1 million rows and has 0-10 rows per entry in TableB and 0-10 per entry in TableC

Simplistic Table Diagram

Queries without temp table

declare @taID bigint=123

select
    ta.*
from
    TableA ta
where
    ta.ID=@taID


select
    tb.*
from
    TableA ta
    inner join TableB tb on ta.ID=tb.TableAID
where
    ta.ID=@taID

select
    tc.*
from
    TableA ta
    inner join TableC tc on ta.ID=tc.TableAID
where
    ta.ID=@taID

Queries with temp table

declare @taID bigint=123

select * 
into #tmpA
from
    TableA ta
where
    ta.ID=@taID

select * from #tmpA

select
    tb.*
from
    #tmpA ta
    inner join TableB tb on ta.ID=tb.TableAID

select
    tc.*
from
    #tmpA ta
    inner join TableC tc on ta.ID=tc.TableAID
3
  • 2
    If the search predicate is known (TableAId) why join at all? SELECT <whatever> FROM TableB WHERE TableAID = 123? – bbaird Feb 26 at 21:12
  • That's a great point and perhaps I should change the question to make the original query non trivial. As a generic pattern, which would you use? – Joshua Grippo Feb 27 at 22:12
  • Write the queries as they need to be written - SQL Server is generally pretty good about caching pages, no need to try and outsmart it. – bbaird Feb 28 at 5:40
0
  1. as you know the PK from child tables, the joins are pointles.

  2. even with joins, the difference between #tmpA and TableA is zero, may even be maybe in favor of TableA, depending. Your first select does the physical read if needed into memory, then its only logical reads and those would have to be done with temp table as well, except for temp table you need to crete it and insert the data

0

I don't think you need to use temp table. It's only a waste of write IO.

Waht I would do is to have cover indexes in tableB and tableC on filed TableAID.

You can eventually evaluate a the SNAPSHOT ISOLATION LEVEL if updates aiming the A table are generating to high locks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.