August 24, 2009 at 2:44 pm
Our developer wrote a query that chews up tempdb space (700GB) before our maximum limit kils the process. The tables involved are large and there doesn't appear to be a "cross join" in his code but I can't delve into "tempdb" as it is a black box to me. Do you have any ideas how to re-cast the query into something less tempdb intensive?
insert into MyTable
select DISTINCT field1,.. field20
from Table1 -- Table1 has about 71 million records
left join Table2 on-- Table2 has about 700,000 records
inner join Table3 on-- Table3 has about 86k records
inner join Table4 on-- Table4 has about 157k records
inner join Table5-- Table5 has about 2 million records
TIA,
barkingdog
August 24, 2009 at 2:55 pm
Is the distinct really necessary? If there's no where clause, nothing limiting rows, you're probably going to get hash joins (that use TempDB) and the sort for the distinct will require quite a large worktable, also in TempDB.
What's the purpose of this query, how many rows in total should it return (try count distinct) and how many would it return without the distinct (count(*))?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply