HELP MAKING SQL CODE MORE EFFICIENT

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

General SQL Server Forums

New to SQL Server Programming

HELP MAKING SQL CODE MORE EFFICIENT

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2012-07-27 : 14:34:10

I'm fairly new to sql, but thanks to this forum I've been able to write a few things... nothing so complex as the code below though.

It works but because of the fact that I'm new, I'm thinking that there is a better way to run this. Right now it runs too slow.

I have four CTEs and then my main code. First CTE pulls a list of people that have worked on a specific file since the last year. 2, 3 and 4th CTE check for possible exceptions on these people rates.

Then my main code is bassically the same as my first CTE, but now I'm bringing the possible exceptions. So:

Project Person Rate D5 D6 D8
1 Smith 100 60 70 300

I have the impression that I might not need that many CTEs, or basically that I could do all the work on the first one.

By looking at the code, can youys think on a simpler way. Any help would be appreciated.
Thanks.

WITH CLT1 AS
(SELECT
matter.mclient
, timecard.ttk
, timekeep.tktitle
, timekeep.tkstrate AS 'BaseRate'

FROM
son_db.dbo.matter matter
, son_db.dbo.timecard timecard
, son_db.dbo.timekeep timekeep

WHERE timecard.tmatter = matter.mmatter
AND timecard.ttk = timekeep.tkinit
AND (timecard.tworkdt > DATEADD(year, -1, GetDate()))

GROUP BY matter.mclient, timecard.ttk, timekeep.tktitle, timekeep.tkstrate, timecard.tstatus

HAVING (timecard.tstatus='B')
)

, D5 AS
( SELECT
matter.mclient
, cexrate.crclient
, cexrate.crtk
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crccode
, cexrate.crmaximum
, cexrate.crrtnum
, cexrate.crenddate
, GETDATE() AS 'CurrentDate'
, CLT1.ttk
, CLT1.BaseRate
, CASE
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate >= cexrate.crrate) then cexrate.crrate
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate <= cexrate.crrate) then CLT1.BaseRate
WHEN (cexrate.crmaximum = 'N' AND cexrate.crrate IS NOT NULL) then cexrate.crrate
end as 'D5Rate'

FROM
son_db.dbo.cexrate cexrate, CLT1, son_db.dbo.matter matter

WHERE (GETDATE() <= cexrate.crenddate)
AND (cexrate.crclient = CLT1.mclient AND cexrate.crtk = CLT1.ttk)
AND (matter.mclient = cexrate.crclient)

GROUP BY
matter.mclient
, cexrate.crclient
, cexrate.crtk
, cexrate.crtitle
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crccode
, cexrate.crmaximum
, cexrate.crrtnum
, cexrate.crenddate
, CLT1.ttk
, CLT1.BaseRate
)

,D6 AS
(
SELECT
matter.mclient
, cexrate.crclient
, cexrate.crtk
, cexrate.crtitle
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crccode
, cexrate.crmaximum
, cexrate.crrtnum
, cexrate.crenddate
, GETDATE() AS 'CurrentDate'
, CLT1.ttk
, CLT1.BaseRate
, CASE
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate >= cexrate.crrate) then cexrate.crrate
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate <= cexrate.crrate) then CLT1.BaseRate
WHEN (cexrate.crmaximum = 'N' AND cexrate.crrate IS NOT NULL) then cexrate.crrate
end as 'D6Rate'

FROM
son_db.dbo.cexrate cexrate, CLT1, son_db.dbo.matter matter

WHERE (GETDATE() <= cexrate.crenddate)
AND (cexrate.crclient = CLT1.mclient AND cexrate.crtitle = CLT1.tktitle)
AND (matter.mclient = cexrate.crclient)

GROUP BY
matter.mclient
, cexrate.crclient
, cexrate.crtk
, cexrate.crtitle
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crccode
, cexrate.crmaximum
, cexrate.crrtnum
, cexrate.crenddate
, CLT1.ttk
, CLT1.BaseRate

)

, D7 AS
(
SELECT
matter.mclient
, cexrate.crclient
, cexrate.crtk
, cexrate.crtitle
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crccode
, cexrate.crmaximum
, cexrate.crrtnum
, cexrate.crenddate
, GETDATE() AS 'CurrentDate'
, CLT1.ttk
, CLT1.BaseRate
, CASE
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate >= cexrate.crrate) then cexrate.crrate
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate <= cexrate.crrate) then CLT1.BaseRate
WHEN (cexrate.crmaximum = 'N' AND cexrate.crrate IS NOT NULL) then cexrate.crrate
end as 'D7Rate'

FROM
son_db.dbo.cexrate cexrate, CLT1, son_db.dbo.matter matter

WHERE (GETDATE() <= cexrate.crenddate)
AND (cexrate.crclient = CLT1.mclient AND cexrate.crtitle = 'ALL')
AND (matter.mclient = cexrate.crclient)

GROUP BY
matter.mclient
, cexrate.crclient
, cexrate.crtk
, cexrate.crtitle
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crccode
, cexrate.crmaximum
, cexrate.crrtnum
, cexrate.crenddate
, CLT1.ttk
, CLT1.BaseRate
)

SELECT

D5.mclient
, D5.ttk
, D5.D5Rate
, NULL AS 'D6Rate'
, NULL AS 'D7Rate'

From D5

UNION ALL

SELECT

D6.mclient
, D6.ttk
, NULL AS 'D5Rate'
, D6.D6Rate
, NULL AS 'D7Rate'

FROM D6

UNION ALL

SELECT

D7.mclient
, D7.ttk
, NULL AS 'D5Rate'
, NULL AS 'D2Rate'
, D7.D7Rate

FROM D7

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 15:30:52

I think your code can reduced to this?


WITH CLT1 AS
(SELECT 
matter.mclient
, timecard.ttk
, timekeep.tktitle
, timekeep.tkstrate AS 'BaseRate'

FROM 
son_db.dbo.matter matter
, son_db.dbo.timecard timecard
, son_db.dbo.timekeep timekeep

WHERE timecard.tmatter = matter.mmatter
AND timecard.ttk = timekeep.tkinit
AND (timecard.tworkdt > DATEADD(year, -1, GetDate())) 

GROUP BY matter.mclient, timecard.ttk, timekeep.tktitle, timekeep.tkstrate, timecard.tstatus

HAVING (timecard.tstatus='B')
)

SELECT
matter.mclient
, cexrate.crclient
, cexrate.crtk
, cexrate.crtitle
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crccode
, cexrate.crmaximum
, cexrate.crrtnum
, cexrate.crenddate
, GETDATE() AS 'CurrentDate'
, CLT1.ttk
, CLT1.BaseRate
, SUM( CASE WHEN cexrate.crtk = CLT1.ttk THEN CASE
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate >= cexrate.crrate) then cexrate.crrate
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate <= cexrate.crrate) then CLT1.BaseRate
WHEN (cexrate.crmaximum = 'N' AND cexrate.crrate IS NOT NULL) then cexrate.crrate
end ELSE 0 END) AS D5Rate
, SUM( CASE WHEN cexrate.crtitle = CLT1.tktitle THEN CASE
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate >= cexrate.crrate) then cexrate.crrate
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate <= cexrate.crrate) then CLT1.BaseRate
WHEN (cexrate.crmaximum = 'N' AND cexrate.crrate IS NOT NULL) then cexrate.crrate
end ELSE 0 END)  AS D6Rate
, SUM( CASE WHEN cexrate.crtitle = 'ALL' THEN CASE
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate >= cexrate.crrate) then cexrate.crrate
WHEN (cexrate.crmaximum = 'Y' AND cexrate.crrate IS NOT NULL AND CLT1.BaseRate <= cexrate.crrate) then CLT1.BaseRate
WHEN (cexrate.crmaximum = 'N' AND cexrate.crrate IS NOT NULL) then cexrate.crrate
end ELSE 0 END) as 'D7Rate'

FROM
son_db.dbo.cexrate cexrate, CLT1, son_db.dbo.matter matter

WHERE (GETDATE() <= cexrate.crenddate)
AND (cexrate.crclient = CLT1.mclient )
AND (matter.mclient = cexrate.crclient)

GROUP BY
matter.mclient
, cexrate.crclient
, cexrate.crtk
, cexrate.crtitle
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crccode
, cexrate.crmaximum
, cexrate.crrtnum
, cexrate.crenddate
, CLT1.ttk
, CLT1.BaseRate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-28 : 00:24:39

Some points:
1) Why do you have the group by with no aggregates? Does not make a great deal of sense so DISTINCT would be a better choice. Not sure if it would make any performance difference though.
2) Having with no aggregate means your HAVING can become WHERE. You can push that into your query then you don't have to pull all the groups together first. The optimiser might do this for you. Not sure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources