 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
johnhurt@gmail.com
Guest
|
 Posted: Mon Aug 22, 2005 6:38 pm Post subject: PreparedStatement and SYSDATE Performance Tuning |
 |
|
Hi,
I have a quick question. Which is faster as a preparedstatement in a
loop (ie, doesn't get reparsed)? Does anyone have a definitive answer
and not a hunch like me?
1) insert into aaa values (?,?,SYSDATE)
2) insert into aaa values (?,?,?) where 3rd ? is a java.util.Date
Regarding re-parsing, would SYSDATE be considered a literal or a
function-return-value and therefore force a similar statement to be
re-parsed?
Lets say that Oracle is very smart, and doesn't reparse if SYSDATE is
the only function or part of a PreparedStatement that changes. In that
case would...
insert into aaa values (?,?,TRUNC(SYSDATE))
be re-parsed or not?
|
|
| Back to top |
|
 |
Joan
Guest
|
| Posted: Mon Aug 22, 2005 8:28 pm Post subject: Re: PreparedStatement and SYSDATE Performance Tuning |
|
|
<johnhurt (AT) gmail (DOT) com> wrote
| Quote: | Hi,
I have a quick question. Which is faster as a preparedstatement
in a
loop (ie, doesn't get reparsed)? Does anyone have a definitive
answer
and not a hunch like me?
1) insert into aaa values (?,?,SYSDATE)
2) insert into aaa values (?,?,?) where 3rd ? is a
java.util.Date
Regarding re-parsing, would SYSDATE be considered a literal or
a
function-return-value and therefore force a similar statement
to be
re-parsed?
Lets say that Oracle is very smart, and doesn't reparse if
SYSDATE is
the only function or part of a PreparedStatement that changes.
In that
case would...
insert into aaa values (?,?,TRUNC(SYSDATE))
be re-parsed or not?
If you have a computer maybe you can test it. Not that I'm |
implying
that you are lazy or anything.
|
|
| Back to top |
|
|
Bill Karwin
Guest
|
| Posted: Mon Aug 22, 2005 10:50 pm Post subject: Re: PreparedStatement and SYSDATE Performance Tuning |
|
|
[email]johnhurt (AT) gmail (DOT) com[/email] wrote:
| Quote: | Regarding re-parsing, would SYSDATE be considered a literal or a
function-return-value and therefore force a similar statement to be
re-parsed?
|
Probably depends on the RDBMS you are using. I have no idea about the
definitive answer with respect to Oracle.
Try writing a program that does this: prepare such a query with SYSDATE,
and execute insert for a few records, with a few moments of delay in
between. See if the SYSDATE value inserted is progressively later, for
each inserted row. If so, then it's being evaluated as a function at
the time you execute the parameterized query. If the value of the date
is the same for each row, it's probably evaluated at the time you
prepared the query and inserted as a constant.
Btw, this may have very negligible impact on the overall performance of
your app. If performance is your goal, you should concentrate on the
areas most likely to result in high cost to performance, instead of
focusing on tiny details which may make very little difference.
Regards
Bill K.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
