Posted by trint on 09/27/05 14:51

Ok,
This script is something I wrote for bringing up a report in reporting
services and it is really slow...Is their any problems with it or is
their better syntax to speed it up and still provide the same report
results?:

SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM tblTravelDetail t1 INNER JOIN
tblTravelDetailAmount t2 ON t1.TravelDetailId =
t2.TravelDetailId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetailId =
t3.TravelDetailId INNER JOIN
tblTravelDetailMember t4 ON t3.TravelDetailId =
t4.TravelDetailId INNER JOIN
tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
period t8 ON t1.PeriodID = t8.period_id
WHERE (t1.MemberId = @MemberId) AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type

Any help is appreciated.
Thanks,
Trint

• Next in forum: Re: Can I speed up this script...help?
• Prev in forum: Stopping Query Execution from PHP
• Thread view: Can I speed up this script...help?

[Reply to this message]