Re: Caching JDBC Statements

From: Joe Weinstein (joeNOSPAM_at_bea.com)
Date: 12/15/03


Date: Sun, 14 Dec 2003 20:05:26 -0800
To: NOBODY <anti@spam.org>


NOBODY wrote:

> Hi Joe,
>
> I was wondering many little things...
>
> 1-Can I create many statement/preparedstatement all mixed, all opened and
> with different SQL, simultaneously, on the same unique connection (per
> thread of course) without closing any of these statement?

Yes, subject perhaps to DBMS limits. For instance, in Oracle an open
statement or result set requires a DBMS-side cursor, of which there is
a configurable limit allowed for a single connection. Some other DBMSes
have no such limit.

> 2-if so, for how long these statement are valid? as long as the
> connection is?

They are valid for as long as the connection is open, or as long as
the objects to which their query plan (prepared statements) remain
unaltered. A statement referring to a table or procedure which is
altered by another connection, typically become defunct and must be
closed and recreated. Some other DBMSes require special settings to
allow a statement to be valid across transactions....

> 3-what is the cost on serverside when one's program keeps let's say, 20
> (pooled) connection, each connection having, let's say, 30 (cached)
> [prepared]statements each (i.e. 600 [prepared]statement total)?

Some DBMSes will incur no special burden, and others may incur memory
costs.
Joe Weinstein at BEA

> Joe Weinstein <joeNOSPAM@bea.com> wrote in
> news:3FD9F52A.7040206@bea.com:
>
>
>>Christopher Dean wrote:
>>
>>
>>>Anybody ever run into any problems caching a statement along with a
>>>connection. Since I use one connection per thread and one statement
>>>at time this seemed like a logical thing to do...
>>
>>Caching statements, especially prepared/callable statements is a good
>>thing. Depending on how complicated your code is, make sure that if
>>there are multiple levels of the stack in a thread using the same
>>connection, that no two levels try to use the same statement at a
>>time. You can cache multiple instances of a statement for the same
>>SQL. Depending on how complicated your code is, you may want code to
>>reset any non-default statement state when it is returned to the
>>cache, so if one caller sets maxRows(1) etc, the next user doesn't
>>magically start getting only one row from his queries too. Another
>>gotcha is if any cached statements refer to tables or procedures that
>>get altered in the DBMS while your application is still using open
>>connections and statements. Any cached prepared statements referring
>>to altered objects will fail and must be closed and replaced. Those
>>hints should do for a start...
>>
>>Joe Weinstein at BEA
>>
>>
>
>
>
>



Relevant Pages

  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... SQL CE/Mobile has a background thread that flushes the changes ... they run for long hours and we have not seen memory leaks so far. ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... So it is a kind of asynchronous commit which happens every 10 ... I am not sure of this claim as we have many applications built on SQL CE ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Access 2003 adp/proxy security - A substitute for SYSTEM_USER()
    ... We need a way for SQL to know the nt username that initiated the proxy ... Many stored procedures and views use a UDF that also depends on ... SyUserLogin and use HostIDand HostName() to find the right row. ... We've played with the Connection dialog settings, ...
    (microsoft.public.access.security)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • Access 2003 adp/proxy security - A substitute for SYSTEM_USER()
    ... We need a way for SQL to know the nt username that initiated the proxy ... Many stored procedures and views use a UDF that also depends on ... SyUserLogin and use HostIDand HostName() to find the right row. ... We've played with the Connection dialog settings, ...
    (microsoft.public.sqlserver.connect)