Query keeps long in executing.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query keeps long in executing.

Author

Topic

degraft
Starting Member

10 Posts

Posted - 2009-02-17 : 11:33:15

Hi Guys. I have an SQL statement that takes a long time to execute. I dont seem to know the cause. I kindly need some help. The Code:

Select Region,District,
'PAVED' = CASE
WHEN (Select SUM(TCHAIN-FCHAIN) From tbCondInv C Where ENG_CLASS = 'P' And C.RD_ID = RD.RD_ID) IS NULL THEN 0
ELSE (Select SUM(TCHAIN-FCHAIN) From tbCondInv C Where ENG_CLASS = 'P' And C.RD_ID = RD.RD_ID) END,
'ROCKY' = CASE
WHEN (Select SUM(TCHAIN-FCHAIN) From tbCondInv C Where ENG_CLASS = 'R' And C.RD_ID = RD.RD_ID) IS NULL THEN 0
ELSE (Select SUM(TCHAIN-FCHAIN) From tbCondInv C Where ENG_CLASS = 'R' And C.RD_ID = RD.RD_ID) END,
'SANDY' = CASE
WHEN (Select SUM(TCHAIN-FCHAIN) From tbCondInv C Where ENG_CLASS = 'S' And C.RD_ID = RD.RD_ID) IS NULL THEN 0
ELSE (Select SUM(TCHAIN-FCHAIN) From tbCondInv C Where ENG_CLASS = 'S' And C.RD_ID = RD.RD_ID) END,
'UNCLASSIFIED' = CASE
WHEN (Select SUM(TCHAIN-FCHAIN) From tbCondInv C Where ENG_CLASS = 'X' And C.RD_ID = RD.RD_ID) IS NULL THEN 0
ELSE (Select SUM(TCHAIN-FCHAIN) From tbCondInv C Where ENG_CLASS = 'X' And C.RD_ID = RD.RD_ID) END

From tbRegions R,tbDistrict D,tbRD_DEF RD
Where D.RegionID = R.RegionID
And R.RegionID = 1
And RD.DistrictID = D.DistrictID

DD

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 11:44:54

seems like this is what you want


Select Region,District,
'PAVED' = SUM(CASE WHEN ENG_CLASS = 'P' THEN COALESCE(TCHAIN-FCHAIN,0) ELSE 0 END),
'ROCKY' = SUM(CASE WHEN ENG_CLASS = 'R' THEN COALESCE(TCHAIN-FCHAIN,0) ELSE 0 END),
'SANDY' = SUM(CASE WHEN ENG_CLASS = 'S' THEN COALESCE(TCHAIN-FCHAIN,0) ELSE 0 END),
'UNCLASSIFIED' = SUM(CASE WHEN ENG_CLASS = 'X' THEN COALESCE(TCHAIN-FCHAIN,0) ELSE 0 END)
From tbRegions R
JOIN tbDistrict D
ON D.RegionID = R.RegionID
JOIN tbRD_DEF RD
ON RD.DistrictID = D.DistrictID
JOIN tbCondInv C
ON C.RD_ID = RD.RD_ID
Where R.RegionID = 1
GROUP BY Region,District

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources