Is this the most efficient query?

irlrobins

Hey all,

Is this query the most efficient? Or is there a better way of coding it?

(Query should return one result which is the newest record that matches a given app_num. One or more records will match the given app_num)

SELECT LOCATION FROM (SELECT * FROM tblLocation WHERE app_num='*****') WHERE LOC_DATE=(SELECT MAX(LOC_DATE) FROM (SELECT * FROM tblLocation WHERE app_num='*****'))

Table tblLocation has columns app_num, location, loc_date and loc_desc.

Oracle DB btw.

seamus

Hmm.

The below would work in MySQL and MSSQL

SELECT LOCATION from tblLocation WHERE LOC_DATE = (SELECT MAX(LOC_DATE) FROM tblLocation WHERE app_num='*****') AND app_num='*****'

Only one subquery. I may be wrong though and this may not return what you're looking for.

irlrobins

Thanks for the reply.

That works. Both queries return in 32ms. So prob much of a muchness.

seamus

Just be aware that it may depend on the size of the table and the indexes in use. If your current dataset is quite small, you're not going to notice a difference either way

pH

If you're on 9i (or higher) have a look at the oracle RANK() function -

SELECT LOCATION FROM (
 SELECT *, RANK() OVER (ORDER BY LOC_DATE DESC) DATE_RANK 
 FROM tblLocation 
 WHERE app_num='*****')
WHERE DATE_RANK = 1

(Don't have an Oracle DB to hand so that's not tested but something like that should work - it's the standard and most readable way to do it - google oracle and rank for more examples)

nobodythere

irlrobins wrote:

Thanks for the reply.

That works. Both queries return in 32ms. So prob much of a muchness.

Sorry to hijack your thread here but how do you find that out?

irlrobins

grasshopa wrote:

Sorry to hijack your thread here but how do you find that out?

Ran the two queries in TOAD and it reports the time it took to complete on the status bar.

seamus wrote:

Just be aware that it may depend on the size of the table and the indexes in use

Fair point. Ran it against a test DB which is probably close to the live DB.

I'll test pH's suggestion tomorrow to see if that offers any significant gains

bonkey

irlrobins wrote:

Thanks for the reply.

That works. Both queries return in 32ms. So prob much of a muchness.

Time-to-return is usually a very bad indicator of efficiency. For a start, if you run the query twice in a row, the second run has the benefit that the results are still in cache from the first run. (Its a tactic often used by unscrupulous marketing people to show how superior their solution is...run the 'old' way first, then the 'new and improved' way).

Also, time-to-return can be dependant on your test case.

Check the Query Plan for both queries. It will give you a much better comparison of cost.

koloughlin

It doesn't sound like this is a requirement of yours yet, but if you ever wanted to get the most recent location for all app_num values you could do something like this:

select 
   a.app_num, a.location, a.loc_date 
from 
   tblLocation a 
where a.loc_date = 
(
select 
   max(b.loc_date) 
from 
   tblLocation b 
where 
   a.app_num = b.app_num 
)

This will give you one row back for each app_num with its most recent location and loc_date provided that each app_num has only has one record for each loc_date.