Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Batch select in java for multiple params

 
Renjith Panikar
Greenhorn
Posts: 28
Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to query 10000 records from a table with multiple parameters (param1 & param2)

Sample query: select columnX from Sample_Table where param1 = param1(i) and param2(i).

Currently i am executing it one by one, which takes long time to query data.

Is there any other way i could do it in java/spring?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In pure JDBC, you might try to employ select batching described in one of our older articles: Batching Select Statements in JDBC.

To do this, you need to use IN query with several columns. Most databases should support the following syntax:, where col1_x and col2_x represent individual combination of values you need to query for. Obviously, you should use PreparedStatement, in which case you'll replace the literal values with question marks and set parameters accordingly.

Given the sheer amount of records you want to query, you'd probably want to use as many parameters in one IN clause as possible, but unfortunately the maximum possible amount of terms in an IN clause differs across databases. If you want to support several databases, you might need to address this problem somehow (or -at the very least- test your app with all of them).

In your situation, I'd consider a second option: create a temporary table containing just the col1 and col2 columns and fill it with the combination of values you want to query. Then just useAn inner join might perform better in some databases.

Again, working with temporary tables differs among databases (some DBs use global temporary tables, some use local temporary tables), making it a bit problematic if you need to support several databases. On the other hand, this technique would probably perform even better than the select batching mentioned above.

Given the amount of data you're working with it is also important to have proper indexes in place, and up-to-date statistics (if your database uses them).
 
Renjith Panikar
Greenhorn
Posts: 28
Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the help, Martin.
Since I am using db2, this logic dint worked.
But i resolved this issue by concatination.

select col_name from sample_table where CONCAT(varchar(col_1),varchar(col_2)) in ((col_1_v1+col2_v2),..... ,(col_1_vn+col_2_vn));
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Renjith Panikar wrote:Since I am using db2, this logic dint worked.

Actually it looks like DB2 supports this construct, but has a slightly different syntax: see this topic on StackOverflow.

But i resolved this issue by concatination.

Be careful with that. Let's say that you data contains the following combination of values:

And say that you're interested in the (123, 456) record, but not in the (1234, 56) one. If you simply concatenate them together, you'll get both as a result. The concatenation can be tweaked in several ways to avoid this (eg. by using a separator that cannot appear in any of the strings, or by padding both columns to their maximum lengths prior to the concatenation).

But you don't need to do it anyway, see above. Using the temp table might be even better performance-wise.
 
Renjith Panikar
Greenhorn
Posts: 28
Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are right Martin.
Forgot to inform you that I kept a pipe '|' seperator to avoid such incidents.

About the temp table,
I have went through it before. But I felt difficulty in generating the query. I am using spring's JdbcTemplate to query the table.

Now I can write the query as "select col_name from sample_table where CONCAT(varchar(col_1),varchar(col_2)) in (:valueList)" and replace the 'valueList' with List<String> which has concatinated values using
MapSqlParameterSource.

Please let me know if you know a better way to do it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic