|
midrange.com
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.
********************************************************************************
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
