• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Chuck Zheng
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64618
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You might try the IN construct but I have no way of knowing if that will yeild better performance.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic