Should I use a temp table or join

Question

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

Queries without temp table

declare @taID bigint=123
select
    ta.*
from
    TableA ta
where
    ta.ID=@taID
    and ta.Field1>1
    and ta.Field2<1000
select
    tb.*
from
    TableA ta
    inner join TableB tb on ta.ID=tb.TableAID
where
    ta.ID=@taID
    and ta.Field1>1
    and ta.Field2<1000
select
    tc.*
from
    TableA ta
    inner join TableC tc on ta.ID=tc.TableAID
where
    ta.ID=@taID
    and ta.Field1>1
    and ta.Field2<1000

Queries with temp table

declare @taID bigint=123
select * 
into #tmpA
from
    TableA ta
where
    ta.ID=@taID
    and ta.Field1>1
    and ta.Field2<1000
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

score 1 · Answer 1 · answered Feb 26 '21 at 21:26

as you know the PK from child tables, the joins are pointles.
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

score 0 · Answer 2 · answered Feb 27 '21 at 09:23

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.

score 0 · Answer 3 · answered Jun 08 '22 at 04:37

Create index on TableB and TableC for column TableAID. It can be used to accelerate the search.

create index on TableB(TableAID);
create index on TableC(TableAID);
select * from TableB where TableAID = 123;
select * from TableC where TableAID = 123;

Should I use a temp table or join

3 Answers3