File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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 REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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

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

Chuck Zheng

Joined: Apr 03, 2004
Posts: 5

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?

Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 63540

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

[Asking smart questions] [About Bear] [Books by Bear]
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 32815

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.

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: how to optimize JDBC on SELECT ...FROM table1 WHERE ..OR .. OR ..OR .. OR .. ?
jQuery in Action, 3rd edition