Re: JDBC - Setting Values on PreparedStatement

    Hi Nigel,

    I've ended up rereading your question several times as well as looking
over the responses.  Note that I, and the other responders, are making some
assumptions about your select statements and table formats that we really
don't know from your description.

> For whatever reason, presumably something to do with the varying number of
> account numbers, the query optimizer sometimes chooses to execute these
> kinds of queries in one way (which performs well) or another (which
> performs terribly)

    First, please correct me, but this has nothing special to do with
PreparedStatements; instead a problem with the IN predicate, right?

    Next, instead of "presumably", can you nail down when or at what point
you see plan and performance differences?  Do you have situations when the
same format and number of values produces different plans?  Does the DB
monitor output give any explanation?  I suppose it's going to a table scan
at some point?

    You might be able to something with multiple OR clauses or EXISTS (
rather than IN ) that would at least give more consistent results, but IMO,
the best answer is probably something you don't want to do.  If you
added client id to the table, then you could add an index by id ordered by
account number and dispense with IN ( varying size or not ) altogether.
Woulda, shoulda, coulda, I know, but it is possible to add that and perform
updates to it without modifying your apps.  If this is as big and important
an issue as you imply, you might consider it.

    BTW, the Array type, unless there has been a very recent change, is not
supported by DB2/400.  It really wouldn't help in this case anyway.

    While I can't say precisely on DB2/400, in some databases Coy's
suggestion of a subselect can cause the subquery output to be put to a
temporary table, which is then scanned == expensive.

    Aaron's suggestion of retrieving account numbers from the client
accounts table, then creating the IN predicate was the second thing I
thought of;  but since my reading of your question at this point indicates
problems depending on the number of items in the list, it doesn't seem that
it would help things.

    Gregory's response makes sense, but it seems you've already been there.

    Larry's response was the first thing I thought of for James' and your
question, but there's an issue: While the DB engine *should* short-circuit -
that is, stop on the first match - the IN list, there's no reason you should
expect the logic to "factor out" duplicates, so it's likely non-matches
would continue through the complete list.


                                                         Joe Sam

Joe Sam Shirah -        http://www.conceptgo.com
conceptGO       -        Consulting/Development/Outsourcing
Java Filter Forum:       http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International?    http://www.jguru.com/faq/I18N
Que Java400?            http://www.jguru.com/faq/Java400


----- Original Message ----- 
From: <NGay@xxxxxxxxxxxxx>
To: <java400-l@xxxxxxxxxxxx>
Sent: Thursday, January 26, 2006 12:29 PM
Subject: Re: JDBC - Setting Values on PreparedStatement


> Joe,
>
> We've had real issues in the past precisely because of this though - we
> have an application that, in a nutshell, allows clients to look only at
> information on their own account numbers.  Any client may have 1..n
account
> numbers, so the SQL statement is similar to
>
> select * from sometable where account in (439,482,509)
>
> For whatever reason, presumably something to do with the varying number of
> account numbers, the query optimizer sometimes chooses to execute these
> kinds of queries in one way (which performs well) or another (which
> performs terribly) (which I've proved by looking at the logs for how the
> SQL was executed using STRDBMON), and we've no direct control over the
> decisions that the query optimizer takes.
>
> At least if we could just prepare
>
> select * from sometable where account in (?)
>
> and then do something similar to:
> int [] accounts = new int [3];
> accounts [0] = 439; accounts [1] = 482; accounts [2] = 509;
> PreparedStatement ps = conn.prepareStatement ("select * from sometable
> where account in (?)");
> ps.setArray (1, accounts);
>
> then although we'd still have no direct control over the query optimizer,
> we'd stand a chance that all clients would behave similarly (& so have
> similar performance).  I notice there is a .setArray method so did some
> googling on this, it seems this is a java.sql.Array type, which it is up
to
> JDBC vendors to implement (or not - it is optional).  Is it implemented in
> the iSeries JDBC driver?
>
>
> Incentally, yes I also tried linking to the table that holds the accounts
> for the client, similar to:
> select * from sometable join clientaccounts on clientaccounts.account =
> sometable.account where client = ?
> and this just performs awfully for everyone so its not an option.
>
> Best regards,
>
> Nigel Gay.
>