Posted by Gert-Jan Strik on 09/28/05 00:05

Trint,

It looks pretty good so far. Your indexes are very important, since the
WHERE clause is probably not very restrictive, and you have many joins
(some of which may be unnecessary). So in addition to Hugo's advice the
following tips:

1) Make sure you have proper indexes in place. You could consider using
the Index Tuning Wizard, or you could add many indexes yourself, check
the execution plan, and remove all unused indexes. When doing that, you
could also consider covering indexes.

For example, you could create the following (temporary) indexes for
table tblTravelDetailAmount :
- tblTravelDetailAmount (TravelDetailId, amountTypeId)
- tblTravelDetailAmount (TravelDetailId, amountTypeId, amount)
- tblTravelDetailAmount (amountTypeId, TravelDetailId, amount)
If there is a clustered index on TravelDetailID, then you could add the
following (temporary) indexes:
- tblTravelDetailAmount (amountTypeId)
- tblTravelDetailAmount (amountTypeId, amount)

2) Remove the t2.amount <> 0 predicate from the query, and check if this
makes the query run faster or slower

3) Table amount_type seems to be a lookup table. If it is, then make
sure that in addition to the Primary Key (on amount_type_id), the table
also has a Unique constraint (or index) on the description column (on
amount_type). If the query does not benefit from this, then you could
consider rewriting the query to this:

SELECT t1.MemberId, t1.PeriodID, t8.start_date,
t6.amount_type_id, MIN(t6.amount_type) AS amount_type, ...
// added MIN() on this line
FROM tblTravelDetail t1 ...
WHERE (t1.MemberId = @MemberId)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id
// removed amount_type from this line

Hope this helps,
Gert-Jan


trint wrote:
>
> 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

[Back to original message]