| Performance related [message #372984] |
Tue, 20 March 2001 19:12  |
jack
Messages: 123
Registered: September 2000
|
Senior Member |
|
|
Hi all,
I've a sql statement executed in two different ways.
Can somebody tell me which sql statement is best to use keeping performance as main criteria.
----First Method ---
select
bls_id,
blssta_id,
bls_due_date,
bls_min_pmt,
bls_current_bal,
BLS_RECV_DATE,
BLS_STATEMENT_DATE
from
bill_summary a,
(select mem_id,pae_id,mpa_id,max(bls_recv_date) max_date from cbbill_summary
where mem_id = 1 and pae_id = 1 and mpa_id = 1 and blssta_id < 4 group by mem_id,pae_id,mpa_id)
bill_summary_view
where
a.mem_id = 1 and
a.pae_id = 1 and
a.mpa_id = 1 and
BLS_RECV_DATE = max_date and
BLSSTA_ID < 4
--------Second Method ----------
select bls_id
from bill_summary a
where a.mem_id = 1 and
a.pae_id = 1 and
a.mpa_id = 1 and
a.blssta_id <4 and
bls_recv_date = (select max(bls_recv_date)
from bill_summary b
where a.mem_id = b.mem_id
and a.pae_id = b.pae_id
and a.mpa_id = b.mpa_id
and a.blssta_id = b.blssta_id
)
-------------
Note: Both queries retrieve the same result.
pl. respond quickly.
Thanks
|
|
|
|
| Re: Performance related [message #372986 is a reply to message #372984] |
Tue, 20 March 2001 20:16  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
|
Whichever way looks best could still end up being slower under certain execution plans. Why don't you just ensure that the amount of data in the talbes is roughly representitive of what you would expect, analyze the tables, then in SQLPLUS "set timing on" and compare the results. Also try "set autotrace on" to give the plan, network stats etc.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
