Re: Beginning SQL statements with a parenthesis in JDBC

[Mike Millson <mgm@atsga.com>]

If you're talking Oracle + JDBC, prepared statements are not necessarily
faster than statements. According to O'Reilly's Oracle JDBC, due to the
overhead of using a PreparedStatement vs. Statement object, it takes at
least 65 executions before a PreparedStement object is faster than a
Statement object. For a small number of executions, a PreparedStatement
object is not faster than a Statement object. (chap 11, pg 216-217). So,
I think it depends on the situation  (as always) whether to use a
PreparedStatement or Statement.

Mike

On Wed, 2003-02-19 at 10:33, Brian Lee wrote:
> That's true, but most db's logic will go with the most used statements 
> staying in the cache longest.
> 
> So if you call your prepared statement 100 times and then call 100 different 
> statements, your prepared statement will probably stay in the cache.
> 
> On Oracle the SGA can be configured however you like, so you can have 1meg 
> or 1 gig.
> 
> But the bottom line is that it's wiser to use prepared statements.
> 
> BAL
> 
> >From: Curt Smith <chsmith@speakeasy.net>
> >To: Unlisted-recipients :;
> >CC: ajug-members@www.ajug.org
> >Subject: Re: Beginning SQL statements with a parenthesis in JDBC
> >
> >True, but there's a finite SQL cache, no?  If you execute most recently an
> >SQL
> >... where empNo = NNN
> >where NNN is not constant, but varies across all keys, then your well 
> >formed
> >prepared statement will be aged out of the cache, thus loosing it's 
> >precompiled
> >benefit due to just one malformed prepared statement.   Might this be 
> >plausable
> >for most DB SQL statement caches, even Informix's?
> >
> >curt
> >
> >Brian Lee wrote:
> >
> >>On Oracle and SQLServer each statement will be cached and not reparsed 
> >>again (for n time). So "SELECT * FROM emp WHERE empNo = ?" will be cached 
> >>as 1 statement and "SELECT * FROM emp WHERE empNo = 1" will be cached as a 
> >>separate statement.
> >>
> >>So if you execute the same query over and over, both statement would still 
> >>be cached (assuming you always query for empNo 1). The difference is that 
> >>the statement with the bind param will be cached even if you send in 
> >>different values for empNo.
> >>
> >>It's still a better idea to use PreparedStatements if not for the data 
> >>binding and not having to build giant sql strings.
> >>
> >>BAL
> >>
> >>>From: Curt Smith <chsmith@speakeasy.net>
> >>>To: ajug-members <ajug-members@www.ajug.org>
> >>>Subject: Re: Beginning SQL statements with a parenthesis in JDBC
> >>>Date: Wed, 19 Feb 2003 09:10:34 -0500
> >>>
> >>>I've learned that there's a science to designing prepared statements and
> >>>_effecive_ use of bind parameters.   I've seen where clauses without
> >>>bind params flush the SQL statement cache and effectively ruin the
> >>>performance advantage of even properly designed prepared statements.
> >>>
> >>>I've not seen a single source for good where clause design using bind
> >>>params?  More importantly a resource for the many anti-patterns?
> >>>
> >>>curt
> >>
> >>
> >>
> >>_________________________________________________________________
> >>The new MSN 8: advanced junk mail protection and 2 months FREE*  
> >>http://join.msn.com/?page=features/junkmail
> >>
> >
> >--
> >
> >Curt Smith
> >chsmith@speakeasy.net
> >(w) 404-463-0973
> >(h) 404-294-6686
> 
> 
> _________________________________________________________________
> STOP MORE SPAM with the new MSN 8 and get 2 months FREE*  
> http://join.msn.com/?page=features/junkmail
> 
>