JDBC Huge data for select

Question

In my Java application, I have to SELECT data from Oracle database with WHERE clause. I am going to use prepared statement. Which is better, should I use

SELECT column_name FROM table_name WHERE column_name = ?
SELECT column_name FROM table_name WHERE column_name = ?
SELECT column_name FROM table_name WHERE column_name = ?
.
.
.
SELECT column_name FROM table_name WHERE column_name = ?

or

SELECT column_name FROM table_name WHERE column_name IN (?, ?, ?, ... ?)

Count of data I need to use in WHERE clause can be vary from 01 to 500.

Mick Mnemonic · Answer 1 · 2016-02-16T18:00:28.513

2

Each query execution comes with some processing/parsing/network overhead so it can easily be an order of magnitude faster to retrieve multiple rows in a single query, by providing a list of values in the IN clause. For example, Oracle has to do a "soft parse" (syntactic & semantic validation, cache lookup), even if the query is reusable (uses bind variables), as is the case in your first example.

You should note that there is a fixed limit of 1000 bind variables you can include in the IN clause in Oracle.

edited Feb 16 '16 at 18:00

answered Feb 16 '16 at 17:38

Mick Mnemonic

7,808
2
26
30

Yes am I aware of 1000 limit in IN clause. You know a better way than iterating to append ',' and '?' within IN clause as my data for IN clause is in ArrayList? – Waleed Khan Feb 16 '16 at 17:39
[This thread](http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives) has some alternatives for dynamically generating the query with bind placeholders. – Mick Mnemonic Feb 16 '16 at 17:46

score 0 · Answer 2 · answered Feb 17 '16 at 11:20

If the number of possible values varies from 1 to 500, then it's probably not worth it to prepare these 500 queries. I would just generate plain SQL each time (for example SELECT column_name FROM table_name WHERE column_name IN (23,29,31,37)) and execute it as a plain statement. I know it's against the rule of always preparing queries and using binds (make sure your code isn't subject to SQL injection attacks) but rules are made to be broken.

score 0 · Answer 3 · answered Feb 17 '16 at 14:36

Actually, the in() operator can be a lot faster, but comes with a (neglible) parse cost, plus the limitation of using up to 500 elements, plus the limitation of total sql query text size. So it is good enough, but not rocket proof :)

The rocket-proof solution is to pass the arbitrary number of parameters in a separate call, and then have a view (or any other way) to represent them in SQL and use in your where criteria.

A brute-force variant is here http://tkyte.blogspot.hu/2006/06/varying-in-lists.html

However if you can use PL/SQL, this mess can become pretty neat.

function getCustomers(in_customerIdList clob) return sys_refcursor is 
begin
    aux_in_list.parse(in_customerIdList);
    open res for
        select * 
        from   customer c,
               in_list v
        where  c.customer_id=v.token;
    return res;
end;

Then you can pass arbitrary number of comma separated customer ids in the parameter, and:

will get no parse delay, as the SQL for select is stable
the SQL is using a simple join, instead of an IN operator, which is quite fast
after all, it is a good rule of thumb of not hitting the database with any plain select or DML, since it is Oracle, which offers lightyears of more than MySQL or similar simple database engines. PL/SQL allows you to hide the storage model from your application domain model in an effective way.

The trick here is:

we need a call which accepts the long string, and store somewhere where the db session can access to it (e.g. simple package variable, or dbms_session.set_context)
then we need a view which can parse this to rows
and then you have a view which contains the ids you're querying, so all you need is a simple join to the table queried.

The view looks like:

create or replace view in_list
as
select
    trim( substr (txt,
          instr (txt, ',', 1, level  ) + 1,
          instr (txt, ',', 1, level+1)
             - instr (txt, ',', 1, level) -1 ) ) as token
    from (select ','||aux_in_list.getpayload||',' txt from dual)
connect by level <= length(aux_in_list.getpayload)-length(replace(aux_in_list.getpayload,',',''))+1

where aux_in_list.getpayload refers to the original input string.

JDBC Huge data for select

3 Answers3