SQL is filling up my hard disk up, need help

[johnny90]

Hi everyone

I am a newbie in sql statement, and I am trying to extract data from a sql database in sql 2000.

when i try to excute the sql code below it completely drains my hard drive from 80 gb to 0 and the i never could extract the data at all.

Can anyone help me out please, i am competely confused now.

*************************
SQL code
********************

SELECT DISTINCT tblItem.itId AS Expr1, tblLegalStatus.lsName, tblItemLettingPeriod.ilpIsBondLodgedRTBA, tblItemLettingPeriod.ilpCreatedDate,
tblItemLettingPeriod.ilpWeeklyRent, tblItemLettingPeriod.ilpTimesViewed, tblItem.itLeaseable, tblItem.IsAssignment, tblItemType.ittName,
tblSuburb.subName, tblSuburb.subMin, tblSuburb.subMax, tblSuburb.subAvg, tblStayLength.stayName, tblItemLettingPeriod.ilpApprovalDate,
tblItemLettingPeriod.ilpExpiryDate, tblHousingType.htName, tblItemLettingPeriod.ilpStartDate
FROM tblItem INNER JOIN
tblLegalStatus ON tblItem.lsId = tblLegalStatus.lsId INNER JOIN
tblItemLettingPeriod ON tblItem.itId = tblItemLettingPeriod.itID INNER JOIN
tblItemType ON tblItem.ittId = tblItemType.ittId INNER JOIN
tblStayLength ON tblItemLettingPeriod.stayId = tblStayLength.stayId INNER JOIN
tblHousingType ON tblItem.htId = tblHousingType.htId CROSS JOIN
tblSuburb
GROUP BY tblItem.itId, tblLegalStatus.lsName, tblItemLettingPeriod.ilpIsBondLodgedRTBA, tblItemLettingPeriod.ilpCreatedDate,
tblItemLettingPeriod.ilpWeeklyRent, tblItemLettingPeriod.ilpTimesViewed, tblItem.itLeaseable, tblItem.IsAssignment, tblItemType.ittName,
tblHousingType.htName, tblItemLettingPeriod.ilpExpiryDate, tblItemLettingPeriod.ilpApprovalDate, tblStayLength.stayName, tblSuburb.subAvg,
tblSuburb.subMax, tblSuburb.subMin, tblSuburb.subName, tblItemLettingPeriod.ilpStartDate
HAVING (tblItemLettingPeriod.ilpStartDate BETWEEN CONVERT(DATETIME, '2008-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2008-12-31 00:00:00', 102))

 

[gmmastros]

Why are you cross joining to tlbSuburb ?

I encourage you to make absolutely certain that you really need a cross join. I suspect that converting this join will also allow you to remove the DISTINCT clause, too. Should make the query a lot faster.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

Not to mention removing the cross join and distinct would remove the need for the Cartesian join which is then flattened by the distinct.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog