query filling tempdb

Hello,
I've got a user who has been trying to run a query for the last few
days. Every time he tries to run it, the tempdb grows to be about 5G and
ends up filling up the hard drive. Anyone have any insight into why
this particular query is filling up the tempdb like this?
SELECT top 50
SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
dbo.CIMSDetail.AccountCode As Acct_Code,
dbo.CIMSDetail.StartDate AS StartDay,
dbo.CIMSDetail.EndDate AS EndDay,
dbo.CIMSDetail.RateCode as RateCode,
dbo.CIMSDetail.ResourceUnits AS Volume,
dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
FROM dbo.CIMSDetail INNER JOIN
dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID = dbo.CIMSDetailIdent.DetailUID AND
dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber = dbo.CIMSIdent.IdentNumber
WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) = 1
AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
GROUP BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
ORDER BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You might consider normalization. :-) Why is all that discrete data in one
column? The database has to do a lot of work separating it out with
SUBSTRING like that.
You might also consider putting tempdb on a drive with more than 5GB free.
:-)
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:eJh59sOjDHA.3192@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I've got a user who has been trying to run a query for the last few
> days. Every time he tries to run it, the tempdb grows to be about 5G and
> ends up filling up the hard drive. Anyone have any insight into why
> this particular query is filling up the tempdb like this?
> SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
> FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID => dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber => dbo.CIMSIdent.IdentNumber
> WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) => 1
> AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
> GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
> ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Typically coputed values in your select statement (like
all of the substring statements) require use of tempdb
space, and the group by and order by portions of your
query will also require a large amount of tempdb space.
In addition to Aaron's comments, you may want to consider
using a summary table that stores some of these
calculated/summary values so that you don't have to
calculate them within the query itself. While this will
use space within your actual database, this will be
easier to monitor and control then the space allocated on
the fly within tempdb.
Just an idea. I hope that this helps somehow.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello,
>I've got a user who has been trying to run a query for
the last few
>days. Every time he tries to run it, the tempdb grows to
be about 5G and
>ends up filling up the hard drive. Anyone have any
insight into why
>this particular query is filling up the tempdb like this?
>SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS
Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS
BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As
BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS
LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS
Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS
WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS
Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS
Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS
Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS
Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26,
3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS
LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS
LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS
ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
>FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID =>dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine =dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber =>dbo.CIMSIdent.IdentNumber
>WHERE DATEPART(DD,GETDATE()) - DATEPART(DD,
dbo.CIMSDetail.EndDate) =>1
> AND dbo.CIMSDetail.RateCode IN
('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription
= 'WORK_ID'
>GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32,
4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>