Oracle JDBC date manipulation

Consider following SQL queries:

select count(1)
from request
where request_date between
to_date(?, 'DD-MM-YYYY HH24:MI:SS') and
to_date(?, 'DD-MM-YYYY HH24:MI:SS')

 

select count(1)
from request
where request_date between ? and ?

It seems both queries should be similar. Moreover, I thought the second one would be the fastest. However, the sample below shows another reality.

import oracle.jdbc.pool.OracleDataSource;

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * Sample class demonstrating Oracle 10g date manipulation
 */
public class OracleTest extends JdbcDaoSupport {

  // Query using Oracle date conversion
  static final String countRequests1 =
    "select count(1) " +
        "from request " +
    "where request_date between to_date(?, 'DD-MM-YYYY HH24:MI:SS') and " +
                            "to_date(?, 'DD-MM-YYYY HH24:MI:SS')";

  // Query using JDBC date conversion
  static final String countRequests2 =
    "select count(1) " +
        "from request " +
    "where request_date between ? and ?";

  // Compare two queries
  private void doQueries() throws Exception {

    // Oracle Datasource
    OracleDataSource ds = new OracleDataSource();
    ds.setURL("jdbc:oracle:thin:@127.0.0.1:2000:PREMIDBP");
    ds.setUser("sdf_monitor");
    ds.setPassword("sdf_monitor");
    SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(ds);

    // Date parameters
    Calendar c = Calendar.getInstance();
    Date dateTo = (Date)c.getTime().clone();
    c.add(Calendar.MINUTE, -1);
    c.set(Calendar.MILLISECOND, 0);
    Date dateFrom = c.getTime();

    // String parameters
    String dateFromString = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss").format(dateFrom);
    String dateToString = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss").format(dateTo);

    // Query countRequests1
    long initTime = System.nanoTime();
    jdbcTemplate.queryForInt(countRequests1, dateFromString, dateToString);
    System.out.println("Query 1: " + (System.nanoTime() - initTime) / 1000000 + " ms.");

    // Query countRequests2
    initTime = System.nanoTime();
    jdbcTemplate.queryForInt(countRequests2, dateFrom, dateTo);
    System.out.println("Query 2: " + (System.nanoTime() - initTime) / 1000000 + " ms.");

  }

  // Launcher
  public static void main(String... args) throws Exception {
        new OracleTest().doQueries();
  }

}

With my data results are:

Query 1: 1208 ms.
Query 2: 20038 ms.

Curious behaviour.

Edited!

An explanation can be always found (Spanish link): JDBC 9.x y las variables de Timestamp de Java

3 comentarios en “Oracle JDBC date manipulation

  1. Very very strange…

    1. Try using >= and <= instead of “between”.

    2. To discard the driver as the source of a possible problem… do you have the latest ojdbc5.jar / ojdbc6.jar version downloadable from Oracle.com?

    3. Do you have indexes created over those columns? Have you analized the explain plan, whether it uses the index in one case but not in the other?

    I’m really curious about this issue… Right now we are optimizing our queries and we have some surprises about the behaviour of the oracle autooptimizer, I have one strange case in with oracle doesn’t use an index when it should… (I have heards that some Oracle versions don’t do the right opt job with “in” or “between” expressions)

    Let me know if you have time to investigate it.

    Y coño, la próxima vez escribo en cristiano…

  2. 1. Both are equivalents for Oracle autooptimizer
    2. I’m using ojdbc5.jar
    3. Date column is indexed and here go explain plans for query 1 and query 2

    Query 1
    SELECT STATEMENT ALL_ROWSCost: 3 Bytes: 8 Cardinality: 1
    2 SORT AGGREGATE Bytes: 8 Cardinality: 1
    1 INDEX RANGE SCAN INDEX SDF_MONITOR.IDX_FECHA Cost: 3 Bytes: 16 Cardinality: 2


    Query 2
    SELECT STATEMENT ALL_ROWSCost: 6,051 Bytes: 8 Cardinality: 1
    3 SORT AGGREGATE Bytes: 8 Cardinality: 1
    2 FILTER
    1 INDEX FAST FULL SCAN INDEX SDF_MONITOR.IDX_FECHA Cost: 6,051 Bytes: 43.452.336 Cardinality: 5.431.542

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s