Can I speed up this script...help?

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
 tblTravelDetailAmoun
t t2 ON t1.TravelDetailId =
t2.TravelDetailId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetailId =
t3.TravelDetailId INNER JOIN
 tblTravelDetailMembe
r 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

There's no way for us to answer that without seeing your execution
plan.  What are your indexes like?

Stu

On 27 Sep 2005 04:51:43 -0700, 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
>                       tblTravelDetailAmoun
t t2 ON t1.TravelDetailId =
>t2.TravelDetailId INNER JOIN
>                      tblTravelDetail t3 ON t2.TravelDetailId =
>t3.TravelDetailId INNER JOIN
>                       tblTravelDetailMembe
r 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

Hi Trint,

My first observation is that the join to the second instance of table
tblTravelDetail (the one aliased as t3) is redundant - it will simply
provide yet another copy of each row in the first instance of the same
table (aliased as t1). Remove this join and change all references to the
alias t3 to t1.

Next, I note that the tables  tblTravelDetailMembe
r and tblTravelEvent
(aliased as t4 and t5) are not used in the query at all. You can quite
probably remove these two tables from the FROM clause and still get the
same results.

If, after these modifications, the query still runs too slow, than
please provide more information. The minimum information required is:
- Complete information about your design: all tables (as CREATE TABLE
statements, including all constraints and properties), all indexes (as
CREATE INDEX statements, unless the index is created as a side effect of
a constraint), and some information about the estimated number of rows
in each table;
- The exact query that you ran (i.e. after making the modifications I
suggested above);
- The actual execution plan (use SET SHOWPLAN TEXT ON to get this
information in a format that you can copy in the newsgroups);
- The time the query took, and the time you think it ought to take.

The following extra information is also very usefull:
- Some rows of sample data that help to illustrate what the query should
achieve (posted as INSERT statements);
- The expected output of the query for those statements (not needed if
the query in your messages produces that resutls);
- A short description of the business problem that you're trying to
solve with this query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

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  tblTravelDetailAmoun
t :
-  tblTravelDetailAmoun
t (TravelDetailId, amountTypeId)
-  tblTravelDetailAmoun
t (TravelDetailId, amountTypeId, amount)
-  tblTravelDetailAmoun
t (amountTypeId, TravelDetailId, amount)
If there is a clustered index on TravelDetailID, then you could add the
following (temporary) indexes:
-  tblTravelDetailAmoun
t (amountTypeId)
-  tblTravelDetailAmoun
t (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
>                        tblTravelDetailAmoun
t t2 ON t1.TravelDetailId =
> t2.TravelDetailId INNER JOIN
>                       tblTravelDetail t3 ON t2.TravelDetailId =
> t3.TravelDetailId INNER JOIN
>                        tblTravelDetailMembe
r 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

Thank you for your responses...Adding indexes to the tables made the
process faster.
Trint