Posted by Hugo Kornelis on 09/27/05 22:58

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
> 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

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 tblTravelDetailMember 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)

[Back to original message]