|
midrange.com
Aaron,
That makes an amount of sense - I had tried using a subselect on my query
as well and found it performed consistently terribly as well, when compared
to retrieving the list of values in one query and simply listing them out
in the IN clause. I'd never ventured to try to find out or guess the
reason behind this - it always seemed a bit daft since you'd expect the
subselect to be executed only once, and effectively produce the list of
values for the IN clause just like if you'd listed them out.
Regards,
Nigel Gay.
"Aaron Bartell"
<albartell@gmail.
com> To
Sent by: "'Java Programming on and around
java400-l-bounces the iSeries / AS400'"
@midrange.com <java400-l@xxxxxxxxxxxx>
cc
26/01/2006 14:56 Subject
RE: JDBC - Setting Values on
PreparedStatement
Please respond to
Java Programming
on and around the
iSeries / AS400
<java400-l@midran
ge.com>
I did the method you suggested once with MySQL instead of doing two
separate
queries and then composing the IN clause with the results of the first
query
by concatenating values together.
When my initial query had a sub select it created a huge amount of
processing overhead vs. when I first executed a query to get the comma
delimited values and then placed them in the IN clause of the second query.
I am guessing it was executing for each result that came back from the sub
select.
Don't know if the DB2 optimizer operates the same way, but thought I would
throw it out there.
Aaron Bartell
http://mowyourlawn.com/blog
-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Coy Krill
Sent: Thursday, January 26, 2006 11:52 AM
To: Java Programming on and around the iSeries / AS400
Subject: RE: JDBC - Setting Values on PreparedStatement
How about using a subselect on the prepared statement like this:
select * from sometable where account in (select account from
sometable where customer = ?)
And just replace the customer id, which I would assume you already know in
the app or could pull "real quick".
Coy Krill
Systems Analyst
Whidbey Island Bank
-----Original Message-----
From: java400-l-bounces+coyk=wibank.com@xxxxxxxxxxxx
[mailto:java400-l-bounces+coyk=wibank.com@xxxxxxxxxxxx] On Behalf Of
NGay@xxxxxxxxxxxxx
Sent: Thursday, January 26, 2006 09:30
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.
****************************************************************************
****
--
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 e-mail is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this e-mail
by anyone else is unauthorized. If you are not the intended recipient, any
disclosure, copying, distribution or any action taken or omitted to be
taken
in reliance on it, is prohibited and may be unlawful.
***********************************************************************
--
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.
--
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.
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.
