Performance of setString() and setDate() in Oracle JDBC?

Question

I'm have some problems about setString() and setDate() in Oracle JDBC. When I try take some tests about setString() and setDate(), I realize setDate() is faster than setString() in most case. Can someone help me to explain this? Here my test :

private void doSelectTest()
{
    System.out.println("[OUTPUT FROM SELECT]");
    //String query = "SELECT LAST_NAME FROM Employees WHERE HIRE_DATE  = ?";
    String query =  "SELECT LAST_NAME FROM Employees WHERE HIRE_DATE  = TO_DATE(?,'yyyy-MM-dd')";
    try
    {
        PreparedStatement st = conn.prepareStatement(query);
        //st.setDate(1,java.sql.Date.valueOf("2003-06-17"));
        st.setString(1,"2003-06-17");
        ResultSet r = st.executeQuery();
        System.out.println(query.toString());
    }
    catch (Exception ex)
    {
        System.err.println(ex.getMessage());
    }
}

Thanks for help.

Please back up your claim that *"setDate() is faster than setString()"* with some data. I would expect that the act of reading the records from the table to be the major part of performance. Without an index on `HIRE_DATE`, that would mean a full table scan, and that is so very much slower than any performance difference of sending the argument as `Date` or as `String`. — Andreas, Apr 17 '17 at 04:06
Did you understand problems? I'm using the same table to query. I skip all performance that relate index, partition, etc. I just want to ask setDate and setString in preparestatement. — Tea, Apr 17 '17 at 04:25
Yes, I understood problem, and I'd expect the performance difference between `Date` and `String` to be negligible compared to anything else going on when executing a query, *unless* it affects the access plan, i.e. prevents use of an index. You haven't given enough information to postulate that, hence my request for proof of your claim, so we can see what numbers you have to back it up. We can't really help explain your numbers, unless we know what they are. — Andreas, Apr 17 '17 at 04:59

score 3 · Accepted Answer · answered Apr 17 '17 at 03:27

First, I wonder how you are actually measuring the timing here. It's really easy to create a non-meaningful benchmark.

Second, you have a lot going on here besides just "setDate" and "setString" - some of it client-side, and some of it server side.

For your "setDate" example, you are also doing java.sql.Date.valueOf("2003-06-17")`, so you are also measuring the overhead of conversion of a String to a Date on the client.

For your "setString" example your sql statement contains "TO_DATE(?,'yyyy-MM-dd')", so again, you are measuring the conversion of a String to a date, but this time on the server-side (in the database).

So, I think that you are comparing apples to oranges, and probably not very accurately.

Thanks. I think this is the best answer – Tea Apr 17 '17 at 12:50 — Tea, Apr 17 '17 at 12:50

score 0 · Answer 2 · answered Apr 17 '17 at 03:00

0

A Date is usually represented by a single long value. It can thus be transfered as 64-bits. A String is a length plus however many characters. Plus, the server has to parse and decode it (probably to a long).

answered Apr 17 '17 at 03:00

Elliott Frisch

198,278
20
158
249

Performance of setString() and setDate() in Oracle JDBC?

2 Answers2