 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Janusz
Guest
|
 Posted: Tue Jan 18, 2005 12:22 pm Post subject: Oracle JDBC Driver ps.setTimestamp() slows down query execut |
 |
|
Hello,
I have the following problem. I perform simple sql query to Oracle database,
there are 2 date variables in the query.
final String sSql = "select count(*) from test where start_date > ? and
start_date < ?";
When I set the date variables using
preparedStatement.setTimestamp(1, new Timestamp(startDate.getTime()));
preparedStatement.setTimestamp(2, new Timestamp(endDate.getTime()));
this simple query executes 10500 miliseconds [ms]
but when the dates are hardcoded in SQL like this:
final String sSql = "select count(*) from test where start_date >
'2005-01-14' and start_date < '2005-01-18'";
the query runs 50 [ms]
In SQLPlus this simple query is also very fast - 30 [ms]
I can't believe that preparedStatement.setTimestamp(..,..) makes Oracle
parse the date so long - 10 seconds ?
I have a proper jdbc driver from page:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
I have tested the problem on 2 versions of Oracle: Oracle 8.1.7 and Oracle
9.2.0.1
for each version I used proper jdbc driver, and on both versions I
encountered this problem.
Do you have any ideas why the query with preparedStatement.setTimestamp()
executes for so long ??
Table test contains about 2 000 000 rows, test.start_date is indexed, the
query returns 1 number : 19112.
The full source code:
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = getConnection(IDbConst.STORAGE);
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -4);
Date startDate = cal.getTime();
Date endDate = new Date();
//final String sSql = "select count(*) from test where
start_date > ? and start_date < ?";
final String sSql = "select count(*) from test where
start_date > '2005-01-14' and start_date < '2005-01-18'";
ps = conn.prepareStatement(sSql);
// ps.setTimestamp(1, new Timestamp(startDate.getTime()));
// ps.setTimestamp(2, new Timestamp(endDate.getTime()));
long time = System.currentTimeMillis();
rs = ps.executeQuery();
System.err.println("SQL EXE time: " +
(System.currentTimeMillis() - time));
} catch (Exception ex) {
_LOGGER.error(ex);
} finally {
SQLUtil.close(rs);
SQLUtil.close(ps);
}
To have slower version (with ps.setTimeStamp(..)), unrem the 3 remmed lines,
and rem specific lines.
Thanks in advance.
Janusz
|
|
| Back to top |
|
 |
Joe Weinstein
Guest
|
| Posted: Tue Jan 18, 2005 4:28 pm Post subject: Re: Oracle JDBC Driver ps.setTimestamp() slows down query ex |
|
|
Hi.
In some cases, the Oracle driver seems to instantiate a java.util.Calendar for
every call to setTimestamp().
Janusz wrote:
| Quote: | Hello,
I have the following problem. I perform simple sql query to Oracle database,
there are 2 date variables in the query.
final String sSql = "select count(*) from test where start_date > ? and
start_date < ?";
When I set the date variables using
preparedStatement.setTimestamp(1, new Timestamp(startDate.getTime()));
preparedStatement.setTimestamp(2, new Timestamp(endDate.getTime()));
this simple query executes 10500 miliseconds [ms]
but when the dates are hardcoded in SQL like this:
final String sSql = "select count(*) from test where start_date
'2005-01-14' and start_date < '2005-01-18'";
the query runs 50 [ms]
In SQLPlus this simple query is also very fast - 30 [ms]
I can't believe that preparedStatement.setTimestamp(..,..) makes Oracle
parse the date so long - 10 seconds ?
I have a proper jdbc driver from page:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
I have tested the problem on 2 versions of Oracle: Oracle 8.1.7 and Oracle
9.2.0.1
for each version I used proper jdbc driver, and on both versions I
encountered this problem.
Do you have any ideas why the query with preparedStatement.setTimestamp()
executes for so long ??
Table test contains about 2 000 000 rows, test.start_date is indexed, the
query returns 1 number : 19112.
The full source code:
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = getConnection(IDbConst.STORAGE);
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -4);
Date startDate = cal.getTime();
Date endDate = new Date();
//final String sSql = "select count(*) from test where
start_date > ? and start_date < ?";
final String sSql = "select count(*) from test where
start_date > '2005-01-14' and start_date < '2005-01-18'";
ps = conn.prepareStatement(sSql);
// ps.setTimestamp(1, new Timestamp(startDate.getTime()));
// ps.setTimestamp(2, new Timestamp(endDate.getTime()));
long time = System.currentTimeMillis();
rs = ps.executeQuery();
System.err.println("SQL EXE time: " +
(System.currentTimeMillis() - time));
} catch (Exception ex) {
_LOGGER.error(ex);
} finally {
SQLUtil.close(rs);
SQLUtil.close(ps);
}
To have slower version (with ps.setTimeStamp(..)), unrem the 3 remmed lines,
and rem specific lines.
Thanks in advance.
Janusz
|
|
|
| Back to top |
|
|
Janusz
Guest
|
| Posted: Tue Jan 18, 2005 7:16 pm Post subject: Re: Oracle JDBC Driver ps.setTimestamp() slows down query ex |
|
|
Uzytkownik "Joe Weinstein" <joeNOSPAM (AT) bea (DOT) com> napisal w wiadomosci
news:41ED3911.10803 (AT) bea (DOT) com...
| Quote: |
Hi.
In some cases, the Oracle driver seems to instantiate a java.util.Calendar
for
every call to setTimestamp().
|
But instantiating java.util.Calendar wouldn't be the cause of the delay
of 10 seconds :-(
| Quote: | Janusz wrote:
Hello,
I have the following problem. I perform simple sql query to Oracle
database,
there are 2 date variables in the query.
final String sSql = "select count(*) from test where start_date > ? and
start_date < ?";
When I set the date variables using
preparedStatement.setTimestamp(1, new
Timestamp(startDate.getTime()));
preparedStatement.setTimestamp(2, new Timestamp(endDate.getTime()));
this simple query executes 10500 miliseconds [ms]
but when the dates are hardcoded in SQL like this:
final String sSql = "select count(*) from test where start_date
'2005-01-14' and start_date < '2005-01-18'";
the query runs 50 [ms]
In SQLPlus this simple query is also very fast - 30 [ms]
I can't believe that preparedStatement.setTimestamp(..,..) makes Oracle
parse the date so long - 10 seconds ?
I have a proper jdbc driver from page:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
I have tested the problem on 2 versions of Oracle: Oracle 8.1.7 and
Oracle
9.2.0.1
for each version I used proper jdbc driver, and on both versions I
encountered this problem.
Do you have any ideas why the query with
preparedStatement.setTimestamp()
executes for so long ??
Table test contains about 2 000 000 rows, test.start_date is indexed,
the
query returns 1 number : 19112.
The full source code:
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = getConnection(IDbConst.STORAGE);
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -4);
Date startDate = cal.getTime();
Date endDate = new Date();
//final String sSql = "select count(*) from test where
start_date > ? and start_date < ?";
final String sSql = "select count(*) from test where
start_date > '2005-01-14' and start_date < '2005-01-18'";
ps = conn.prepareStatement(sSql);
// ps.setTimestamp(1, new
Timestamp(startDate.getTime()));
// ps.setTimestamp(2, new Timestamp(endDate.getTime()));
long time = System.currentTimeMillis();
rs = ps.executeQuery();
System.err.println("SQL EXE time: " +
(System.currentTimeMillis() - time));
} catch (Exception ex) {
_LOGGER.error(ex);
} finally {
SQLUtil.close(rs);
SQLUtil.close(ps);
}
To have slower version (with ps.setTimeStamp(..)), unrem the 3 remmed
lines,
and rem specific lines.
Thanks in advance.
Janusz
|
|
|
| Back to top |
|
|
Joe Weinstein
Guest
|
| Posted: Tue Jan 18, 2005 8:13 pm Post subject: Re: Oracle JDBC Driver ps.setTimestamp() slows down query ex |
|
|
Janusz wrote:
| Quote: | Uzytkownik "Joe Weinstein" <joeNOSPAM (AT) bea (DOT) com> napisal w wiadomosci
news:41ED3911.10803 (AT) bea (DOT) com...
Hi.
In some cases, the Oracle driver seems to instantiate a java.util.Calendar
for
every call to setTimestamp().
But instantiating java.util.Calendar wouldn't be the cause of the delay
of 10 seconds 
|
Right, sorry. I didn't look at the code well enough.
Does the table you're querying have an index on start_date?
I guess it does, and the table is big. I am guessing that
the DBMS is *not* using the index when a parameter value
is sent. This may be because the DBMS is receiving the
timestamp values in some way that requires it to convert
the table data to compare it. Because the table data is
converted, the index order cannot be guaranteed, so the
table skips the index. If you can use the DBMS's performance
tools to find out how the query plan differs, that will probably
show the issue. I would post this in the oracle dbms group too.
Joe Weinstein at BEA
| Quote: |
Janusz wrote:
Hello,
I have the following problem. I perform simple sql query to Oracle
database,
there are 2 date variables in the query.
final String sSql = "select count(*) from test where start_date > ? and
start_date < ?";
When I set the date variables using
preparedStatement.setTimestamp(1, new
Timestamp(startDate.getTime()));
preparedStatement.setTimestamp(2, new Timestamp(endDate.getTime()));
this simple query executes 10500 miliseconds [ms]
but when the dates are hardcoded in SQL like this:
final String sSql = "select count(*) from test where start_date
'2005-01-14' and start_date < '2005-01-18'";
the query runs 50 [ms]
In SQLPlus this simple query is also very fast - 30 [ms]
I can't believe that preparedStatement.setTimestamp(..,..) makes Oracle
parse the date so long - 10 seconds ?
I have a proper jdbc driver from page:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
I have tested the problem on 2 versions of Oracle: Oracle 8.1.7 and
Oracle
9.2.0.1
for each version I used proper jdbc driver, and on both versions I
encountered this problem.
Do you have any ideas why the query with
preparedStatement.setTimestamp()
executes for so long ??
Table test contains about 2 000 000 rows, test.start_date is indexed,
the
query returns 1 number : 19112.
The full source code:
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = getConnection(IDbConst.STORAGE);
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -4);
Date startDate = cal.getTime();
Date endDate = new Date();
//final String sSql = "select count(*) from test where
start_date > ? and start_date < ?";
final String sSql = "select count(*) from test where
start_date > '2005-01-14' and start_date < '2005-01-18'";
ps = conn.prepareStatement(sSql);
// ps.setTimestamp(1, new
Timestamp(startDate.getTime()));
// ps.setTimestamp(2, new Timestamp(endDate.getTime()));
long time = System.currentTimeMillis();
rs = ps.executeQuery();
System.err.println("SQL EXE time: " +
(System.currentTimeMillis() - time));
} catch (Exception ex) {
_LOGGER.error(ex);
} finally {
SQLUtil.close(rs);
SQLUtil.close(ps);
}
To have slower version (with ps.setTimeStamp(..)), unrem the 3 remmed
lines,
and rem specific lines.
Thanks in advance.
Janusz
|
|
|
| Back to top |
|
|
Marek
Guest
|
| Posted: Tue Jan 18, 2005 8:48 pm Post subject: Re: Oracle JDBC Driver ps.setTimestamp() slows down query ex |
|
|
I am guessing that
| Quote: | the DBMS is *not* using the index when a parameter value
is sent. This may be because the DBMS is receiving the
timestamp values in some way that requires it to convert
the table data to compare it. Because the table data is
converted, the index order cannot be guaranteed, so the
table skips the index. If you can use the DBMS's performance
tools to find out how the query plan differs, that will probably
show the issue. I would post this in the oracle dbms group too.
Joe Weinstein at BEA
|
Thanks a lot, I will check the query plan tomorrow morning :-)
Janusz
| Quote: |
Janusz wrote:
Hello,
I have the following problem. I perform simple sql query to Oracle
database,
there are 2 date variables in the query.
final String sSql = "select count(*) from test where start_date > ? and
start_date < ?";
When I set the date variables using
preparedStatement.setTimestamp(1, new
Timestamp(startDate.getTime()));
preparedStatement.setTimestamp(2, new
Timestamp(endDate.getTime()));
this simple query executes 10500 miliseconds [ms]
but when the dates are hardcoded in SQL like this:
final String sSql = "select count(*) from test where start_date
'2005-01-14' and start_date < '2005-01-18'";
the query runs 50 [ms]
In SQLPlus this simple query is also very fast - 30 [ms]
I can't believe that preparedStatement.setTimestamp(..,..) makes Oracle
parse the date so long - 10 seconds ?
I have a proper jdbc driver from page:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
I have tested the problem on 2 versions of Oracle: Oracle 8.1.7 and
Oracle
9.2.0.1
for each version I used proper jdbc driver, and on both versions I
encountered this problem.
Do you have any ideas why the query with
preparedStatement.setTimestamp()
executes for so long ??
Table test contains about 2 000 000 rows, test.start_date is indexed,
the
query returns 1 number : 19112.
The full source code:
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = getConnection(IDbConst.STORAGE);
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -4);
Date startDate = cal.getTime();
Date endDate = new Date();
//final String sSql = "select count(*) from test where
start_date > ? and start_date < ?";
final String sSql = "select count(*) from test where
start_date > '2005-01-14' and start_date < '2005-01-18'";
ps = conn.prepareStatement(sSql);
// ps.setTimestamp(1, new
Timestamp(startDate.getTime()));
// ps.setTimestamp(2, new Timestamp(endDate.getTime()));
long time = System.currentTimeMillis();
rs = ps.executeQuery();
System.err.println("SQL EXE time: " +
(System.currentTimeMillis() - time));
} catch (Exception ex) {
_LOGGER.error(ex);
} finally {
SQLUtil.close(rs);
SQLUtil.close(ps);
}
To have slower version (with ps.setTimeStamp(..)), unrem the 3 remmed
lines,
and rem specific lines.
Thanks in advance.
Janusz
|
|
|
| Back to top |
|
|
Janusz
Guest
|
| Posted: Tue Jan 18, 2005 8:51 pm Post subject: Re: Oracle JDBC Driver ps.setTimestamp() slows down query ex |
|
|
I am guessing that
| Quote: | the DBMS is *not* using the index when a parameter value
is sent. This may be because the DBMS is receiving the
timestamp values in some way that requires it to convert
the table data to compare it. Because the table data is
converted, the index order cannot be guaranteed, so the
table skips the index. If you can use the DBMS's performance
tools to find out how the query plan differs, that will probably
show the issue. I would post this in the oracle dbms group too.
Joe Weinstein at BEA
|
Thanks a lot, I will check the query plan tomorrow morning
Janusz
|
|
| Back to top |
|
|
Janusz
Guest
|
| Posted: Wed Jan 19, 2005 10:09 am Post subject: Re: Oracle JDBC Driver ps.setTimestamp() slows down query ex |
|
|
| Quote: | I am guessing that
the DBMS is *not* using the index when a parameter value
is sent. This may be because the DBMS is receiving the
timestamp values in some way that requires it to convert
the table data to compare it. Because the table data is
converted, the index order cannot be guaranteed, so the
table skips the index. If you can use the DBMS's performance
tools to find out how the query plan differs, that will probably
show the issue.
|
Joe, you were perfectly right, I generated the sql execution plan,
when ps.setTimestamp(..) is used - Oracle does not use index which is set on
this DATE field (when query executed through jdbc)
|
|
| Back to top |
|
|
Joe Weinstein
Guest
|
| Posted: Wed Jan 19, 2005 5:46 pm Post subject: Re: Oracle JDBC Driver ps.setTimestamp() slows down query ex |
|
|
Janusz wrote:
| Quote: | I am guessing that
the DBMS is *not* using the index when a parameter value
is sent. This may be because the DBMS is receiving the
timestamp values in some way that requires it to convert
the table data to compare it. Because the table data is
converted, the index order cannot be guaranteed, so the
table skips the index. If you can use the DBMS's performance
tools to find out how the query plan differs, that will probably
show the issue.
Joe, you were perfectly right, I generated the sql execution plan,
when ps.setTimestamp(..) is used - Oracle does not use index which is set on
this DATE field (when query executed through jdbc)
|
Glad to help. I would suggest posting your findings to the oracle database newsgroup
and filing a TAR with Oracle if you have a support contract. Please post the plan
you see with the hard-coded date and the plan you see with the parameter.
Good luck,
Joe Weinstein at BEA
|
|
| Back to top |
|
|
Juha Laiho
Guest
|
| Posted: Thu Jan 20, 2005 4:53 pm Post subject: Re: Oracle JDBC Driver ps.setTimestamp() slows down query ex |
|
|
"Janusz" <januszstu (AT) wytnij (DOT) spam.wp.pl> said:
| Quote: | I have the following problem. I perform simple sql query to Oracle database,
there are 2 date variables in the query.
final String sSql = "select count(*) from test where start_date > ? and
start_date < ?";
When I set the date variables using
preparedStatement.setTimestamp(1, new Timestamp(startDate.getTime()));
preparedStatement.setTimestamp(2, new Timestamp(endDate.getTime()));
this simple query executes 10500 miliseconds [ms]
but when the dates are hardcoded in SQL like this:
final String sSql = "select count(*) from test where start_date
'2005-01-14' and start_date < '2005-01-18'";
the query runs 50 [ms]
In SQLPlus this simple query is also very fast - 30 [ms]
|
After seeing Joe's responses (about use of indexes etc), I'll try to add
something; check a couple of variations of your parametrized query.
What happens if you, instead of providing the timestamps with setTimestamp,
provide them as strings (as you do in your hardcoded SQL example)?
How about using an explicit TO_DATE() (hmm.. was it TO_DATE() or was there
something else for greater accuracy?) conversion around your ?-marks in
the query?
Also, check that the index makes sense; so, chek index statistics, and try
to get an idea whether the index is at least somewhat correctly balanced.
And remember, the analyze results will be discarded if they're too old,
so even just re-analyzing an index may help.
--
Wolf a.k.a. Juha Laiho Espoo, Finland
(GC 3.0) GIT d- s+: a C++ ULSH++++$ P++@ L+++ E- W+$@ N++ !K w !O !M V
PS(+) PE Y+ PGP(+) t- 5 !X R !tv b+ !DI D G e+ h---- r+++ y++++
"...cancel my subscription to the resurrection!" (Jim Morrison)
|
|
| 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
