Tempdb gets out of control when inserting records

[AG]

I have a very big table with 20 million records DistinctProjectionKey
which i join several times to different tables in this query.
select distinct distinctprojectionkeyid,dpk.MarketID,
dpk.Classificationid,
dpk.DistributorID,
dpk.ManufacturerID,
dpk.LocationID,
dpk.TimeID,P4.FACTOR as factor1,P3.FACTOR as factor2 ,P2.FACTOR as
factor3,P1.FACTOR as factor4
into Projectionfactors1
FROM DistinctProjectionKey dpk INNER JOIN D_Time t
ON t.TimeID = dpk.TimeID

INNER JOIN (select * from (select distinct
ClassificationID_Major,'fam' as lab from
StagingOLTP..ClassificationFlat) cf1)cf
ON cf.ClassificationID_Major = dpk.ClassificationID
LEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p4
ON t.TheDate = p4.TheDate
AND p4.Name = 'FAM'
AND cast(cf.Lab as varchar(20)) = cast(p4.Lab as varchar(20))
AND dpk.MarketID = p4.MarketID
AND p4.ManufacturerID IS NULL
AND p4.ClassificationID IS NULL

LEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p3
ON t.TheDate = p3.TheDate
AND p3.Name = 'fam'
AND cast(cf.Lab as varchar(20)) = cast(p3.Lab as varchar(20))
AND dpk.MarketID = p3.MarketID
AND p3.ClassificationID = dpk.ClassificationID
AND p3.ManufacturerID IS NULL

LEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p2
ON t.TheDate = p2.TheDate
AND p2.Name = 'fam'
AND cast(cf.Lab as varchar(20)) = cast(p2.Lab as varchar(20))
AND dpk.MarketID = p2.MarketID
AND p2.ManufacturerID = dpk.ManufacturerID
AND p2.ClassificationID IS NULL

LEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p1
ON t.TheDate = p1.TheDate
AND p1.Name = 'fam'
AND cast(cf.Lab as varchar(20)) = cast(p1.Lab as varchar(20))
AND dpk.MarketID = p1.MarketID
AND p1.ManufacturerID = dpk.ManufacturerID
AND p1.ClassificationID = dpk.ClassificationID
the other table have fewer number of records .

I find that when I try to do the insert tempdb goes out of control ,it
grows above 100 GB?
Would anyone know the reason why and the solution to apply to avoid
this problem?
The other tables have fewer recods
Classification flat has 5000 records and projection default flat has
32652 records.
Ajay
Ajay

[Mike Epprecht \(SQL MVP\)]

Hi

To execute the query, the SQL Engine needs to take data out of the original
tables and store it in a work table. If the work table exceeds the amount of
RAM you have, it has to spool it to disk. TempDB is the spool location. (If
you switch on Display Execution Plan in QA, you will notice many spool
operations).

The use of code like "INNER JOIN (select * from (select distinct" results in
having to do this as it first has to find distinct rows, store it, then do
the join.

Re-writing the query will help and monitor the operations in the execution
plans until you can get rid of the spool operations.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"AG" <aj*****@hotmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

I have a very big table with 20 million records DistinctProjectionKey
which i join several times to different tables in this query.
select distinct distinctprojectionkeyid,dpk.MarketID,
dpk.Classificationid,
dpk.DistributorID,
dpk.ManufacturerID,
dpk.LocationID,
dpk.TimeID,P4.FACTOR as factor1,P3.FACTOR as factor2 ,P2.FACTOR as
factor3,P1.FACTOR as factor4
into Projectionfactors1
FROM DistinctProjectionKey dpk INNER JOIN D_Time t
ON t.TimeID = dpk.TimeID

INNER JOIN (select * from (select distinct
ClassificationID_Major,'fam' as lab from
StagingOLTP..ClassificationFlat) cf1)cf
ON cf.ClassificationID_Major = dpk.ClassificationID
LEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p4
ON t.TheDate = p4.TheDate
AND p4.Name = 'FAM'
AND cast(cf.Lab as varchar(20)) = cast(p4.Lab as varchar(20))
AND dpk.MarketID = p4.MarketID
AND p4.ManufacturerID IS NULL
AND p4.ClassificationID IS NULL

LEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p3
ON t.TheDate = p3.TheDate
AND p3.Name = 'fam'
AND cast(cf.Lab as varchar(20)) = cast(p3.Lab as varchar(20))
AND dpk.MarketID = p3.MarketID
AND p3.ClassificationID = dpk.ClassificationID
AND p3.ManufacturerID IS NULL

LEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p2
ON t.TheDate = p2.TheDate
AND p2.Name = 'fam'
AND cast(cf.Lab as varchar(20)) = cast(p2.Lab as varchar(20))
AND dpk.MarketID = p2.MarketID
AND p2.ManufacturerID = dpk.ManufacturerID
AND p2.ClassificationID IS NULL

LEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p1
ON t.TheDate = p1.TheDate
AND p1.Name = 'fam'
AND cast(cf.Lab as varchar(20)) = cast(p1.Lab as varchar(20))
AND dpk.MarketID = p1.MarketID
AND p1.ManufacturerID = dpk.ManufacturerID
AND p1.ClassificationID = dpk.ClassificationID
the other table have fewer number of records .

I find that when I try to do the insert tempdb goes out of control ,it
grows above 100 GB?
Would anyone know the reason why and the solution to apply to avoid
this problem?
The other tables have fewer recods
Classification flat has 5000 records and projection default flat has
32652 records.
Ajay
Ajay

[Erland Sommarskog]

AG (aj*****@hotmail.com) writes:

I have a very big table with 20 million records DistinctProjectionKey
which i join several times to different tables in this query.
...
I find that when I try to do the insert tempdb goes out of control ,it
grows above 100 GB?
Would anyone know the reason why and the solution to apply to avoid
this problem?
The other tables have fewer recods
Classification flat has 5000 records and projection default flat has
32652 records.

I have a suspicion that this query does not do what you intend. How many
rows in the ProjectionDefaultFlat can each LEFT JOIN hit? If it's more
than one row, you also have the answer for the tempdb explosion. Since
you join with the table four times, you get a four-way cross join (i.e
a cartestian product) between the various projections of the staging table.
That can be many rows! And it may be more rows than you want. You will
get a combination of factor1-4 that make little sense.

Probably you should write the query something like this:

SELECT ...
FROM DistinctProjectionKey
JOIN D_Time t
JOIN (SELECT ...) cf
LEFT JOIN Staging...
AND p4.ManufacturerID IS NULL
AND p4.ClassificationID IS NULL
UNION
SELECT ...
FROM DistinctProjectionKey
JOIN D_Time t
JOIN (SELECT ...) cf
LEFT JOIN Staging...
AND p3.ClassificationID = dpk.ClassificationID
AND p3.ManufacturerID IS NULL

Or you could just have:

AND dpk.MarketID = p4.MarketID
AND (p4.ManufacturerID IS NULL OR p4.ManufacturerID = dpk.ManufacturerID)
AND (p4.ClassificationID IS NULL ...)

There is also this part:

INNER JOIN (select *
from (select distinct ClassificationID_Major,'fam' as lab
from StagingOLTP..ClassificationFlat) cf1) cf

This looks like a bit of an overkill. It may be better to replace this
with an EXISTS:

WHERE EXISTS (SELECT *
FROM StagingOLTP..ClassificationFlat cf1
WHERE cf1.ClassificationID = dpk.ClassificationID)

In the LEFT JOIN there is a reference to cf:

AND cast(cf.Lab as varchar(20)) = cast(p4.Lab as varchar(20))

But since cf.lab is a constant, this can be rewritten.

Disclaimer: I no nothing of your business process, nothing about your
tables and nothing about your indexes. See the above as general guesses
that may or may not help to improve the processing. If there is something
you think does not make sense, feel free to discard. For more precise
help, it can be a good idea to post CREATE TABLE and CREATE INDEX statements
of your tables, and don't forget PRIMARY and UNIQUE constraints.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp