August 7, 2007 at 9:04 am
I have an old report that runs on 2 tables and a view that started to run slower when the amount of records increased. The tables have a little over then 200,000 mln records. All good indexes and stats assumed. All joins need to be left oter as there might be transaction with no name, or no history, and still need to return them.
I decided that i may rewrite the query to optimize it. The 2 cases below run much faster then the original, but I'm not sure what will be the most efficient way. Any other way to accomplish what I'm trying to?
Any advice on that is greatly appreciated.
The tables:
transactions - tran_id, from_acct, to_acct, date (indexes on each of them, Date+tran_id)
transaction_history - old_id, status, some more fields;(indexes on both)
NAME View - supposely good indexed underlined tables.
Current query:
select trasaction.*,
(select last_name from NAME_view where acct = transactions.from_Acct) as last_Name_from,
(select last_name from NAME_view where acct = transactions.to_Acct) as last_Name_to,
(select first_name from NAME_view where acct = transactions.from_Acct) as first_Name_from,
(select first_name from NAME_view where acct = transactions.to_Acct) as first_Name_to
--some more selects from NAME_view here
from transactions left outer join transaction_history on tran_id =old_id where transaction_history.status = 'Current'
I also tried this:
select sc.*, bnc.first_name_from, bnc.last_name_from, bnc2.first_name_to, bnc.last_name_to, h.*
FROM (SELECT * FROM transaction ) sc
LEFT JOIN (SELECT * FROM name_view ) bnc ON bnc.acct = sc.from_acct
LEFT JOIN (SELECT * FROM transaction_history WHERE source_type = 'CONFLICT') h ON sc.conflict_id = h.source_id
LEFT JOIN (SELECT * FROM name_view ) bnc2 ON bnc2.acct = sc.to_acct
Thanks a lot for the help.
tom
August 7, 2007 at 9:24 am
select
txns.*, last_name as last_Name_from,
last_name as last_Name_to,
first_name as first_Name_from,
first_name as first_Name_to
from transactions txns
left outer join transaction_history hist on txns.tran_id = hist.old_id
left outer join NAME_view From_Acct on From_Acct.acct = txns.from_Acct
left outer join NAME_view From_Acct on To_Acct.acct = txns.to_Acct
where hist.status = 'Current'
consider using the fully qualified column name instead of txns.*
August 7, 2007 at 9:25 am
August 7, 2007 at 9:41 am
I like beldu's solution, but left joins cause scans, so you lose the benefits of indexes. If you can avoid them, do that.
Be sure statistics update automatically, maybe even manually run them. Be sure indexes are rebuilt if you can and check the performance before/after.
Not sure if there's a much better way to write this. I might use a temp table if these are large tables and do full joins, maybe union in the missing rows then.
August 7, 2007 at 12:10 pm
All indexes are rebuilded and the stats are updated.
The execution plans show that the optimizer uses FTS on the Name table coming from the NAME_view which makes no sense to me.
Here is the example. if I run the query below, it runs just fine and all indexes are in use (even with or):
select * from Name_view where From_Acct = 1000 or to_Acct = 1000
But when I join this view to the tables int he example above, then I get FTS and no one of the indexes are in use.
But the joins are using the same exact join criteriea...
What am I missing?
Thanks a lot for the help.
August 7, 2007 at 1:11 pm
One correction to bledu's suggestion:
select txns.*, last_name as last_Name_from,
last_name as last_Name_to,
first_name as first_Name_from,
first_name as first_Name_to
from transactions txns
left outer join transaction_history hist on txns.tran_id = hist.old_id
and hist.status='Current'
left outer join NAME_view From_Acct on From_Acct.acct = txns.from_Acct
left outer join NAME_view From_Acct on To_Acct.acct = txns.to_Acct
-- where hist.status = 'Current'
Optimizer likely chooses FTS on Name because your original query returns a bunch of transactions, not just one. Check the query plan to see how many times the FTS is executed. I guess the index is not covering and it might be cheaper to do the scan once and then just merge the outcome with the rest of the data.
August 7, 2007 at 4:30 pm
I don't see anything from "hist" table in SELECT list, so it must be either removed from query or INNER JOINed if it's used as a filter for transaction records.
bledu's option was doing filtering, but using of LEFT JOIN in such case does not make any sense.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply