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