#548 Select statement very slow with date parameter

[Robert]

When we execute a complex select statement which involves joins of more tables and it contains paramters of type Date, it lasts approximatelly 30 seconds to return.

When the dynamic date is coded directly into the sql statement (only other parameters of type String are used), then the same statement returns in 1 second the same result set.

[Geoffrey Arnold]

Logged In: YES
user_id=717860
Originator: NO

Hey Robert,

We were experiencing the same problem in Sybase and the workaround was to inline the date value directly into the query, ie. don't use parameters for dates. This is definitely an issue with JTDS.

Geoff.

[Robert]

Logged In: YES
user_id=1947363
Originator: YES

Hi Geoffrey,

our problem is that our application has to run on Ms SQL Server and Oracle and possibly other databases and in different language variants. Therefore encoding the date into sql is not possible.

However, my colleague found a solution in the meantime. In the jdbc connection string, we added parameter prepareSql=0 (or 1,2, but definetelly not 3). 3 is default value for ms sql, but it makes problems. With 0,1,and 2 it works perfectly with date parameters.

I hope it helps you as well
Robert

[Geoffrey Arnold]

Logged In: YES
user_id=717860
Originator: NO

Hey Robert,

We are using prepareSql=2 and the issue still exists.

I just discovered that using PreparedStatement.setString() instead of PreparedStatement.setDate() is also a suitable workaround for this bug.

Fortunately our application is Sybase-specific so using a String representation of java.sql.Date allows us to continue to realize the performance benefits of prepared statements while working around this bug.

I sympathize with you position though.

Geoff.

[Geoffrey Arnold]

Logged In: YES
user_id=717860
Originator: NO

Hey Robert,

By any chance are you using Spring? Could it be an issue with the way Spring determines the parameter type?

Geoff.

[m.hilpert]

Logged In: YES
user_id=667728
Originator: NO

That's interesting! I also noticed a big performance problem when date parameters are involved. However, in our case it's reading from a ResultSet that is very slow. My profiler showd much time for
getDatetimeValue() :

---

TdsCore.getNextRow()
TdsCore.nextToken()
TdsCore.tdsRowToken()
TdsData.readData() 34%
getDatetimeValue() 8%
read() 7%

---

The strange behaviour in our case is, that when reading from the same ResultSet that comes from a SELECT statement that selects the data from a table is much faster than the very same data returned by a stored procedure's ResultSet.

(see [ 1816672 ] ResultSet.next() slow (for procedure ResultsSets))

[m.hilpert]

Logged In: YES
user_id=667728
Originator: NO

I worked around this by changing JtdsPreparedStatement.java:

1. add constant:

   /* Date formatter with ISO 8601 pattern (short) "yyyyMMdd" and default locale. /
   private final SimpleDateFormat sdfYYYYMMDD = new SimpleDateFormat("yyyyMMdd", Locale.getDefault());

2. in line 733 format date object to string:

   public void setDate(int parameterIndex, Date x) throws SQLException {
   // setParameter(parameterIndex, x, java.sql.Types.DATE, 0, 0);
   setParameter(parameterIndex, sdfYYYYMMDD.format(x), java.sql.Types.VARCHAR, 0, 0);
   }

As SQLServer 7 understands the date format "yyyyMMdd", this works for us, at least.

We achieved a performance increase of 15% with this change in our application.

[m.hilpert]

Logged In: YES
user_id=667728
Originator: NO

... of course you also need the new imports:

import java.text.SimpleDateFormat;
import java.util.Locale;

;-)

[momo]

This really sounds like a bigger problem, but currently I'm not able to reproduce that. I added a test case to class ResultSetTest (testDatePerformance) that, at least, shows that prepared Statements can really be slightly slower than inlined values when not using batches. Maybe you could have a look and adopt this test to show what exactly goes wrong in your case?

Cheers,
momo