aspose file tools*
The moose likes JDBC and the fly likes how to optimize JDBC on SELECT ...FROM table1 WHERE ..OR .. OR ..OR .. OR .. ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to optimize JDBC on SELECT ...FROM table1 WHERE ..OR .. OR ..OR .. OR .. ?" Watch "how to optimize JDBC on SELECT ...FROM table1 WHERE ..OR .. OR ..OR .. OR .. ?" New topic
Author

how to optimize JDBC on SELECT ...FROM table1 WHERE ..OR .. OR ..OR .. OR .. ?

Chuck Zheng
Greenhorn

Joined: Apr 03, 2004
Posts: 5
Hi,

I've been given a java.util.Collection of data (actually composite alternative keys) to query Oracle. so I need to build dynamic SQL query:

SELECT ... FROM table1
WHERE (ak_col1 = ... AND ak_col2 = ...)
OR (ak_col1 = ... AND ak_col2 = ...)
OR (ak_col1 = ... AND ak_col2 = ...)
...
... // till the last element in the Collection

The size of Collection is between 100 to 500. When I construct this query as String and passed to JDBC Statment. It can take a few seconds to run.

I wonder if any one know a better way to imporve the performance?

TIA
chuck
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61433
    
  67

You might try the IN construct but I have no way of knowing if that will yeild better performance.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

Chuck,
A few things that come to mind:
1) First thing is to check you have an index on column one and column two.
2) Are the values in the columns distinct? If there are duplicates you can merge some which would slightly optimize the query.
3) Try an experiment where you break the query up into batches on 10-50 in clause sets each. This will ensure that the extremely large prepared statement isn't too big to deal with in memory.

Bear,
I don't think an in clause would work here. It would end up returning more rows because it can only check for one condition at a time.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to optimize JDBC on SELECT ...FROM table1 WHERE ..OR .. OR ..OR .. OR .. ?