RE: JDBC - Setting Values on PreparedStatement

You should be able to use the INNER JOIN to get the best performance.
If it is performing poorly I would look to see what indexes it is trying
to use to get the data.

If you cannot use the normal joins think about using temp tables instead
of the IN.  Like you said there is no way for you to control the
execution plan.  With tables you can add indexes and provide hints to
which hints it should use.  

Don't rely on the database to know how it should look for data.  
I have had this happen many times

Select t1.pk from t1 inner join t2 on t1.fk = t2.pk
When the engine would scan t1 it would use the pk index instead of the
fk index.  Tell the compile to use the fk index makes a big difference.

HTH

Gregory Jones


-----Original Message-----
From: java400-l-bounces+gtjones=fpic.net@xxxxxxxxxxxx
[mailto:java400-l-bounces+gtjones=fpic.net@xxxxxxxxxxxx] On Behalf Of
NGay@xxxxxxxxxxxxx
Sent: Thursday, January 26, 2006 12:30 PM
To: java400-l@xxxxxxxxxxxx
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.




 

             "Joe Sam Shirah"

             <jshirah@attgloba

             l.net>
To 
             Sent by:                  "Java Programming on and around
the 
             java400-l-bounces         iSeries / AS400"

             @midrange.com             <java400-l@xxxxxxxxxxxx>

 
cc 
 

             26/01/2006 13:14
Subject 
                                       Re: JDBC - Setting Values on

                                       PreparedStatement

             Please respond to

             Java Programming

             on and around the

              iSeries / AS400

             <java400-l@midran

                  ge.com>

 

 






    Hi James,

    While there often are additional reasons to use prepared statements,
the
primary idea is that a search plan can be created beforehand and reused
in
later queries.  The benefit comes from not having to recalc the plan
each
time, which is the case with standard Statements.  While the comparison
*values* of variables can vary using replacement markers, it's pretty
much
an implicit requirement that the variables themselves don't change.  If
they
do, clearly another plan has to be devised, which pretty much defeats
the
advantage.

    If you ignore possible other considerations, then perforrmance-wise
a
standard Statement will beat out a PreparedStatement in a one-time use.
That's becasue a PreparedStatement requires a separate trip to the
database
so the database engine can get the information and prepare the plan.

    There's no substantive difference in Sarah's solution and building
the
appropriate statement with replacement markers ( question marks - ? ) as
you
need it.  Whether it makes sense performance-wise to do either one
depends
on whether the statement will be executed multiple times.

    A relatively new impact is that some drivers/databases are capable
of
caching prepared statements, so in a multi-user environment, like
servlets,
you may still get a performance gain overall even though an individual
response/thread uses it only once.  HTH,


                                                         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: "James O'Sullivan" <josulli4@xxxxxxx>
To: "Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx>
Sent: Thursday, January 26, 2006 10:57 AM
Subject: Re: JDBC - Setting Values on PreparedStatement


>
> Thanks Sarah,
>       I was hopeful that it could be done using the ?, but good to
have
> this option.
>
> regards
>
>
> James O'Sullivan
> Senior Technical Consultant
> email: josulli4@xxxxxxx
> Office: +44 1252 536681
> Fax: +44 1252 534022
> www: www.csc.com
> Based at: Tower 2, Floor 2, Royal Pavilion, Wellesley Road, Aldershot,
> Hampshire, GU11 1PZ
>
> CSC Corporation Limited: No. 1812179.
> All registered office addresses: Royal Pavilion, Wellesley Road,
Aldershot,
> Hampshire, GU11 1PZ. Registered in England.
>
>
>
>
>              Sarah Poger
>              Gladstone
>              <listmember@glads
To
>              tone.info>                Java Programming on and around
the
>              Sent by:                  iSeries / AS400
>              java400-l-bounces         <java400-l@xxxxxxxxxxxx>
>              @midrange.com
cc
>
>
Subject
>              26/01/2006 15:12          Re: JDBC - Setting Values on
>                                        PreparedStatement
>
>              Please respond to
>              Java Programming
>              on and around the
>               iSeries / AS400
>              <java400-l@midran
>                   ge.com>
>
>
> I had to do something similar where I didn't know in advance how many
> variables would be in the IN clause.
>
> How I solved my issue was to not use ? in my prepared statement.
>
> For example:
>
> String langs = "'" + lang1 + "' , "  + "'" + lang2 + "'";
> PreparedStatement pstmt = con.prepareStatement("SELECT * FROM ERORPF
WHERE
> ERORLANG IN (" + langs + ")"  );
>
> HTH,
> Sarah
>
>
> On 1/26/06, James O'Sullivan <josulli4@xxxxxxx> wrote:
> >
> >
> >
> >
> > Hi All,
> >      I have had a tour around the Net and can't seem to find an
> > answer/example of this.
> >
> > Have a simple SQL statement
> > SELECT * FROM ERORPF WHERE ERORLANG IN ('E', 'N', 'D')
> > The contents of the IN predicate can vary from 1 to 10 items, hence
my
> > question.
> >
> > I set this in a PreparedStatement
> >
> > PreparedStatement pstmt = con.prepareStatement("SELECT * FROM ERORPF
> WHERE
> > ERORLANG IN (?)");
> >
> > But when it came to replacing the ? with the required values, I hit
my
> > problem.
> > I tried (perhaps optimistically) just to see if it would accept
> parameters
> > in the following format, but no joy.
> >
> > String langs = "'" + lang1 + "' , "  + "'" + lang2 + "'";
> > setString(1, langs);
> >
> > How can you pass a variable amount of values to the IN predicate?
> >
> >
> > regards
> >
> >
> > James O'Sullivan

--
This is the Java Programming on and around the iSeries / AS400
(JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.





************************************************************************
********
The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee; access to this
email by anyone else is unauthorised.

If you are not the intended recipient: (1) you are kindly requested
to return a copy of this message to the sender indicating that you
have received it in error, and to destroy the received copy; and (2)
any disclosure or distribution of this message, as well as any action
taken or omitted to be taken in reliance on its content, is prohibited
and may be unlawful.
************************************************************************
********