David & others:
With Oracle, there is SQL statement reuse at multiple levels.
At the application level, you can indeed reuse prepared statements. You are
correct that this is very difficult to pull off when using DB connection pools.
It is apparently possible if an advanced JDBC driver is doing the pooling. On
our project we had to abandon attempts to cache prepared statements as we were
using WebLogic's DB connection pooling.
At the database level, Oracle maintains a cache of previously executed SQL.
This allows the RDBMS to avoid reconstructing SQL execution plans for frequently
executed statements. You get this benefit whether or not you reuse prepared
statements at the application level. Another poster to this list stated that
Oracle 8.1.6 can now reuse SQL statements even when the hard-coded WHERE clause
values change from execution to execution. I hadn't heard this, but it would be
great if it's true.
However, there's a third benefit to using bind variables that's loosely related
to performance. Oracle's SQL profiling tool aggregates the performance results
by SQL statement. If each SQL execution constitutes a new SQL statement, these
results are nearly unusable when monitoring large test runs. The information
overload makes diagnostics very difficult, and therefor impedes ones ability to
tune database accesses.
-Tom
-----Original Message-----
From: David Michaels <[EMAIL PROTECTED]>
Sent: Thursday, May 04, 2000 3:09 PM
To: Tom Larson
Subject: Re: Does finder support IN clause
Hi Tom,
you mentioned:
>assuming you want SQL statement reuse (and for Oracle at least, you do -- trust
>me)
We've been wondering about this exact issue. If we use PreparedStatements via WL
connection pools, and create new copies of the same prepared statement SQL each
time, do we get "SQL statement reuse"? How much of a performance concern is
this,
and what do you do to make sure you get the performance gain you expect? (It
seemed
to us that DB connection pooling was an enemy to SQL statement reuse, since you
can't keep a Statement open without holding onto a Connection, which you never
do
when you use a pool)
thanks much
david
Tom Larson wrote:
> Not exactly.
>
> However, we've used the TopLink CMP container for WebLogic 4.5.1. (Their WLS
> 5.1 product is in beta right now) TopLink does support constructing IN()
> clauses from input Vectors -- with one important limitation: It doesn't work
> with bind variables. In other words, the desired JDBC SQL should look
something
> like:
>
> /* ?'s representing bind variables and allowing
> SQL statement reuse at the DB level */
> SELECT ...
> FROM ...
> WHERE columnA in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
>
> assuming you want SQL statement reuse (and for Oracle at least, you do --
trust
> me)
> What TopLink can produce is:
>
> /* hard-coded values for the IN() restriction */
> SELECT ...
> FROM ...
> WHERE columnA in (1, 3, 8, 12, 87, 105)
>
> We managed to work around this limitation by using lots of "OR"s. The
resulting
> SQL was quite ugly, but much more efficient.
>
> Tom Larson
> Capital One Services, Inc.
> [EMAIL PROTECTED]
>
> -----Original Message-----
> From: Shiv Kumar <[EMAIL PROTECTED]>
> Sent: Thursday, May 04, 2000 8:14 PM
> To: [EMAIL PROTECTED]
> Subject: Does finder support IN clause
>
> Hi all
>
> Does any other Application Server provide such a feature? Read on..
>
> I am using Weblogic 5.1 and struggling to write a finder method for a CMP bean
> which can use the SQL 'IN' clause. For example, I want something like this :-
>
> public Enumeration findByAandBs(A a, B[] b) {
> // this finder should translate to
> // select * from t where t.a = a and t.b IN (b[0], b[1], b[2],.... b[n]);
> }
>
> The weblogic WLQL operator list does not include the 'IN' operator. Has anyone
> else in this list faced the same problem?
>
> Thanks.
> --
> shiv
> [EMAIL PROTECTED]
>
> --
> shiv
> [EMAIL PROTECTED]
>
> __________________________________________________
> Do You Yahoo!?
> Talk to your friends online with Yahoo! Messenger.
> http://im.yahoo.com
>
> ===========================================================================
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
> of the message "signoff EJB-INTEREST". For general help, send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
>
> ===========================================================================
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
> of the message "signoff EJB-INTEREST". For general help, send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
--
David Michaels <[EMAIL PROTECTED]>
Director of Technology
ShockMarket Corporation (650) 330-4665
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
