Help to make this Fast?

Ok,
This is so slow, and I've taken the advice of others to try to get it
faster, but I think I'm in a "writers block".
SELECT t1.MemberId, t1.PeriodID,
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
WHERE (t1.MemberId = 12391) AND (t2.amount <> 0)
--WHERE (t4.TravelDetailMemberTypeId = 1) AND (t1.MemberId = 12391)
AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID
--SELECT FROM amount_type
--CASE WHEN description LIKE '%Debit%' THEN -amount ELSE amount
END AS
--Amount FROM t2
--SELECT CASE WHEN description LIKE '%Debit%' THEN -amount ELSE
amount END AS
--Amount FROM amount_type
Thanks,
TrintHi, Trint:
It looks like you join too many tables as those are not needed in your query
:
Can you try this:
SELECT t1.MemberId, t1.PeriodID,
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
tblTravelDetailMember t4 ON t1.TravelDetailId =
t4.TravelDetailId
WHERE (t1.MemberId = 12391) AND (t2.amount <> 0)
--WHERE (t4.TravelDetailMemberTypeId = 1) AND (t1.MemberId = 12391)
AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID
Perayu
"trint" wrote:

> Ok,
> This is so slow, and I've taken the advice of others to try to get it
> faster, but I think I'm in a "writers block".
> SELECT t1.MemberId, t1.PeriodID,
> 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
> WHERE (t1.MemberId = 12391) AND (t2.amount <> 0)
> --WHERE (t4.TravelDetailMemberTypeId = 1) AND (t1.MemberId = 12391)
> AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID
> --SELECT FROM amount_type
> --CASE WHEN description LIKE '%Debit%' THEN -amount ELSE amount
> END AS
> --Amount FROM t2
> --SELECT CASE WHEN description LIKE '%Debit%' THEN -amount ELSE
> amount END AS
> --Amount FROM amount_type
>
> Thanks,
> Trint
>|||have a typeidtable which
compose typeid and typevalue column
do a join rather than a case on your select
hope that helps
"trint" wrote:

> Ok,
> This is so slow, and I've taken the advice of others to try to get it
> faster, but I think I'm in a "writers block".
> SELECT t1.MemberId, t1.PeriodID,
> 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
> WHERE (t1.MemberId = 12391) AND (t2.amount <> 0)
> --WHERE (t4.TravelDetailMemberTypeId = 1) AND (t1.MemberId = 12391)
> AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID
> --SELECT FROM amount_type
> --CASE WHEN description LIKE '%Debit%' THEN -amount ELSE amount
> END AS
> --Amount FROM t2
> --SELECT CASE WHEN description LIKE '%Debit%' THEN -amount ELSE
> amount END AS
> --Amount FROM amount_type
>
> Thanks,
> Trint
>|||I'm trying these suggestions now...will let you know in just a few
minutes.
Thanks Perayu and joseg.
Trint|||On 8 Aug 2005 04:37:41 -0700, trint wrote:

>Ok,
>This is so slow, and I've taken the advice of others to try to get it
>faster, but I think I'm in a "writers block".
Hi Trint,
Instead of starting a new thread every three or four hours, could you
please limit yourself to just one thread. I've seen at least four
different threads with variations of this query, all started in the last
24 hours. I've lost track of which suggestions you've already gotten,
let alone which ones you've tried.
Anyway, before trying an indexed view, let's first try to make the query
itself a bit faster.
Did you already follow the suggestion to remove joins to unused tables
from the query? Did it help?
Did you already add indexes? Did it help?
Looking at your query, I guess that you should first add these indexes:
CREATE INDEX MakeUpAGoodName
ON tblTravelDetail (MemberId, TravelDetailId, PeriodID)
CREATE INDEX MakeUpAnotherGoodName
ON tblTravelDetailAmount (TravelDetailId, amount, amountTypeId)
Then, simplify your query to
SELECT t1.MemberId,
t1.PeriodID,
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 AS t1
INNER JOIN tblTravelDetailAmount AS t2
ON t1.TravelDetailId = t2.TravelDetailId
WHERE t1.MemberId = 12391
AND t2.amount <> 0
GROUP BY t1.MemberId, t1.PeriodID
If that doesn't perform a lot better, then follow the advice in
www.aspfaq.com/5006 with your next reply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)