Can I speed up this script...help?

[trint]

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
tblTravelDetail Amount t2 ON t1.TravelDetail Id =
t2.TravelDetail Id INNER JOIN
tblTravelDetail t3 ON t2.TravelDetail Id =
t3.TravelDetail Id INNER JOIN
tblTravelDetail Member t4 ON t3.TravelDetail Id =
t4.TravelDetail Id INNER JOIN
tblTravelEvent t5 ON t1.TravelEventI d =
t5.TravelEventI d 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

[Stu]

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

Stu

[Hugo Kornelis]

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
tblTravelDetail Amount t2 ON t1.TravelDetail Id =
t2.TravelDetai lId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetail Id =
t3.TravelDetai lId INNER JOIN
tblTravelDetail Member t4 ON t3.TravelDetail Id =
t4.TravelDetai lId INNER JOIN
tblTravelEvent t5 ON t1.TravelEventI d =
t5.TravelEvent Id 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_ty pe

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 tblTravelDetail Member 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)

[Gert-Jan Strik]

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 tblTravelDetail Amount :
- tblTravelDetail Amount (TravelDetailId , amountTypeId)
- tblTravelDetail Amount (TravelDetailId , amountTypeId, amount)
- tblTravelDetail Amount (amountTypeId, TravelDetailId, amount)
If there is a clustered index on TravelDetailID, then you could add the
following (temporary) indexes:
- tblTravelDetail Amount (amountTypeId)
- tblTravelDetail Amount (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_t ype) 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
tblTravelDetail Amount t2 ON t1.TravelDetail Id =
t2.TravelDetail Id INNER JOIN
tblTravelDetail t3 ON t2.TravelDetail Id =
t3.TravelDetail Id INNER JOIN
tblTravelDetail Member t4 ON t3.TravelDetail Id =
t4.TravelDetail Id INNER JOIN
tblTravelEvent t5 ON t1.TravelEventI d =
t5.TravelEventI d 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

[trint]

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