0

I have to execute below query through JDBC call

select primaryid from data where name in ("abc", adc", "anx");

Issue is inside in clause I have to pass 11000 strings. Can I use prepared statement here? Or any other solution any one can suggest. I dont want to execute the query for each record, as it is consuming time. I need to run this query in very less time.

I am reading the strings from an XML file using DOMParser. and I am using sql server db.

Improve this question
7
  • please explain the problem a bit more detailed. is it creating long query ? Commented Feb 28, 2014 at 10:48
  • Where do you get the strings from? Another query or from somewhere outside the DB? If I remember correctly there's an limit to the number of parameters you can have in a prepared statement, around 2000 or so. Commented Feb 28, 2014 at 10:50
  • 2
    For oracle, the limit is 1000. For other databases, the limit is often the max length of a query. If this list is not obtainable from a DB query, you could insert it into a temp table and join on this temp table. Commented Feb 28, 2014 at 10:51
  • possible duplicate of PreparedStatement with list of parameters in a IN clause Commented Feb 28, 2014 at 11:09
  • @Vilen: query is very simple( written in question) but due to number of parameters inside in clause it gets very big. So I can not write select * from data where id in (?,?,?). Also, the number of values are not constant. It varies. Commented Feb 28, 2014 at 11:17

4 Answers 4

Reset to default
2

I'm just wondering why you would need to have a manual set of 11,000 items where you need to specify each item. It sounds like you need to bring the data into a staging table (surely it's not been selected from the UI..?), then join to that to get your desired resultset.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I need to process that 11K records data(varies day by day), so I need to find details from db for each of them. Earlier I was executing prepared statement for each record, but it takes a lot of time. So thought of getting all the values all together. I dont knw this will work or not
Those 11,000 records must be coming from a data source, right? It sounds like you need to push this data into the database on a daily schedule, then perform the requisite join.
Yes I am getting data from many streams/units. This data I have to process,combine and create a output file for some other stream.
I've done a few projects where data has needed to be aggregated from disparate data sources, by the sounds of it you still need to bring them into a single source into the database. It would be the most efficient way of processing the data once you have it. You can easily truncate the table on a daily basis if you don't need to keep it for historical purposes.
Yes, doing it to a temp table is the best route (especially at this data size). Fortunately, most RDBMSs have bulk data-loading operations, which is much better than row-at-a-time inserts.
|
1

Using an IN clause with 11k literal values is a really bad idea - off the top of my head, I know one major RDBMS (Oracle) that doesn't support more than 1k values in the IN list.

What you can do instead:

  • create some kind of (temporary) table T_NAMES to hold your names; if your RDBMS doesn't support "real" (session-specific) temporary tables, you'll have to add some kind of session ID
  • fill this table with the names you're looking for
  • modify your query to use the temporary table instead of the IN list: select primaryid from data where name in (select name from T_NAMES where session_id = ?session_id) or (probably even better) select primaryid from data join t_names on data.name = t_names.name and t_names.session_id = ?session_id (here, ?session_id denotes the bind variable used to pass your session id)
Improve this answer

Comments

0

A prepared statement will need to know the number of arguments in advance - something along the lines of :

PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (?, ?, ?)");
stmt.setInt(1);
stmt.setInt(2);
stmt.setInt(3);

11,000 is a large number of parameters. It may be easiest to use a 'batch' approach as described here (in summary - looping over your parameters, using a prepared statement each time)

Note - if your 11,000 strings are the result of an earlier database select, then the best approach is to write a stored procedure to do the whole calculation in the database (avoiding passing the 11,000 strings back and forth with your code)

Improve this answer

3 Comments

two options - either write the 11,000 values in to a table in the DB instead of to the XML file (as JaneD suggests) - or use a prepared statement in a loop, passing in say 500 parameters at a time, as in my answer. Hers is a better solution, but you may not have control over the process of creating the XML file.
So, I have to write '?' 500 times. Is that the only possible way.:-(
use dynamic sql instead of prepared statement, but still in batches? So, you still have a loop to put 500 parameters into a single query, but instead of a prepared statement you just build up a string "WHERE IN (....)". It will be slower and less type safe...but cleaner code.
0

You can merge all your parameter strings into one bitg string separating by ';' char

bigStrParameter=";abc;adc;anx;"

And use LOCATE to find substring.

select primaryid from data where LOCATE(concat(';',name,';'),?)>=0;
Improve this answer

3 Comments

How this query is working can you please explain.. I liked your answer. Hope this works for me.
It tries to find substring in the big string. If the name is part of the big merged string the chanr index returned by LOCATE >0
is name =bigstrparameter?. Locate string will return index of name in '?' ? I am totally confused with your sql.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.