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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy