RE: JDBC - Setting Values on PreparedStatement

A few more points - 

1) Make sure you are trying to solve the right problem.  
The way I see it the problem is database design/optimization.   It
should be a warning when you have to jump through hoops to get a simple
1:n relationship from a database.  By fixing the issues with the
database you will make life much easier now and in the future.  Ignoring
the erratic performance and working around it will not make it go away
and you will have to deal with it eventually, and it is always easier to
do it sooner than later.  Performance issues will only magnify as the
size of the data becomes larger.


To find an optimal solution you really need tables, indexes, the command
and the execution plan.  Without that information it is just a shooting
in the dark.  

2) Instead of coding a solution that limits you to 1-10 account numbers
solve the first problem and implement a solution that will not have to
change if the max number of accounts change.  After all the relationship
is 1..n not 1..10.





-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Sam Shirah
Sent: Thursday, January 26, 2006 9:16 PM
To: Java Programming on and around the iSeries / AS400
Subject: 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.
>