PreparedStatement and SYSDATE Performance Tuning

Thread view:

johnhurt@gmail.com - 22 Aug 2005 19:38 GMT

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?

Reply to this Message

Joan - 22 Aug 2005 21:28 GMT

> Hi,
>
[quoted text clipped - 23 lines]
>
> 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.

Reply to this Message

Bill Karwin - 22 Aug 2005 23:50 GMT

> 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.

Reply to this Message

PreparedStatement and SYSDATE Performance Tuning

Free Magazines