December 11, 2024 at 5:58 pm
I have query which is filling TEMPDB
I need suggestion how to modify this query to which can minimize the TEMPDB space to fill
update #TempShow1
set Name = case when a2.ID is NULL then a1.name else a2.name end,
Address = case when a2.ID is NULL then a1.Address else a2.Address end,
State = case when a2.ID is NULL then Student.dbo.Mark(a1.state) else student.getClean(a2.state) end
from #TempShow1 t left join #INFO a1
on t.clientID = a1.clientID
and a1.Type = 1
left join #INFO a2
on t.ID = a2.ID
and a2.type = 2
December 11, 2024 at 6:51 pm
First, you need to correct the query to use the alias in the UPDATE, not the original table name. Other things to consider if still having problems:
(1) How large is the #TempShow1 table?
(2) How large is the #INFO table? If the info table is large, and it has no indexes, create a unique clustered index on ( Type, ID ) /* assuming ID is a unique value */.
update t --<<--!CRITICAL!--<<--
set Name = case when a2.ID is NULL then a1.name else a2.name end,
Address = case when a2.ID is NULL then a1.Address else a2.Address end,
State = case when a2.ID is NULL then Student.dbo.Mark(a1.state) else student.getClean(a2.state) end
from #TempShow1 t
left join #INFO a1
on t.clientID = a1.clientID
and a1.Type = 1
left join #INFO a2
on t.ID = a2.ID
and a2.type = 2
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply