File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Any ideas on optimizing this query?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Any ideas on optimizing this query??" Watch "Any ideas on optimizing this query??" New topic
Author

Any ideas on optimizing this query??

v ray
Ranch Hand

Joined: Mar 15, 2007
Posts: 223
Select distinct DPM.dep_id,OPM.org_id,A.year
from DPM,OPM,M,A
where M.pro_id=? and DPM.Affil_id(+)=A.affil_id
and OPM.affil_id=A.affil_id
and M.seqno=A.seqno and M.pubid=A.pubid
order by A.year desc;


So this is a query which is being used in my project and apparently it takes a long time for it to execute. Is there so way to optimize the where clause so that the query executes faster??

Also, what is the significance of the (+) symbol used??

Thanks guys.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2499
    
    8

DPM.Affil_id(+)=A.affil_id
what is the significance of the (+) symbol
This is an outer join.
Your query will return a row, even when a.affil is null (which means that there is no DPM record for that A record)
Look up outer join in the Oracle SQL reference for more info.

apparently it takes a long time for it to execute
You are returning all rows. There is no filter in this query that restricts the data that is returned. You only have join conditions in your where clause.

You use distinct, which means you are expecting duplicate rows. It might be a good idea to analyze why you get duplicated.

Look if proper indexes are in place for the fields you are using in the where clause.

Buy Oracle's SQL Tuning book. Lots of good advice.

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
v ray
Ranch Hand

Joined: Mar 15, 2007
Posts: 223
Thanks Jan!
Is the use of "distinct" going to cost me performance? Also, I have the M.pro_id=? condition right? My manager tells me the
"DPM.Affil_id(+)=A.affil_id
and OPM.affil_id=A.affil_id
and M.seqno=A.seqno and M.pubid=A.pubid" might take a long time to execute due the permutations and combinations possible out of it, is there a way to change this?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2499
    
    8

Is the use of "distinct" going to cost me performance?
Yes. Oracle will have to sort the results by all fields, to allow it to remove duplicates.
Only then can it start to serve the records to you.
I have the M.pro_id=? condition right?
Right. You are restricting the scope of the query.
...might take a long time to execute due the permutations and combinations possible out of it, is there a way to change this?
Doesn't look too bad. You have table M (-with a restriction on it-), and thats joined to A.
A has a join to OPM
A has an outer join to DPM.

If all comumns are properly indexed, and not too much records match any of the joins, it should not take too long.
Do an explain plan to see how oracle approaches your query.

Reordering the links might help in some cases (pseudocode):(secret: sometimes Oracle performs better if you put joins in both directions, e.g.:
and A.affil_id = OPM.affil_id
and OPM.affil_id = A.affil_id)
Regards, Jan
v ray
Ranch Hand

Joined: Mar 15, 2007
Posts: 223
So, ways to optimize the query could be:
1. Check the database, see if there is a chance that duplicate values can exist in the resultset, if duplicates dont exist, eliminate "distinct" clause from the query.
2. Try and use joins in both the directions and see if it improves performance.
3. Try to reorder the query and see how that affects the performance.

Have I got this right?
Do an explain plan to see how oracle approaches your query

This means, I run this query on SQL?


Is this right?
Thanks so much for your detailed help jan! Really appreciate it. And yeah, I think I will get Oracle's SQL Tuning book as there are a lot of sql queries used in my project and the application processes thousands of records everyday, so there is serious room for performance improvement through improving the sql queries used.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Any ideas on optimizing this query??