Dzisiaj technicznie…

Testujemy nową aplikacje na bazie Oracle (dotychczas pracowałem na DB2 i PostgreSQL) i jedno zapytanie okazało się absurdalnie długo wykonywać, czyli około 2 minut, chociaż w czasie wcześniejszych testów trwało to poniżej sekundy.

Postać zapytania używanego w serwerze aplikacyjnym (działa długo):

SELECT count(*) FROM foo WHERE foo.last_changes_date > ?
Postać zapytania testowanego wcześniej w SQLPlus (klient Oracle’a) i SQuirreL (prosty klient w Javie), to działa szybko:
SELECT count(*) FROM foo
WHERE foo.last_changes_date > to_date('20130627', 'yyyymmdd')
Błąd był ciekawy, bo początkowo myślałem że jest związany z czymś w naszym serwerze aplikacyjnym. Po kolei eliminowałem:

Poziom izolacji -> nic
Samodzielne sterowanie transakcją -> nic
Semafor na bazie danych (na wszelki wypadek) -> nic
Connection własny zamiast z datesource’a z serwera aplikacyjnego -> nic
Zwykły Statement zamiast PreparedStatement -> tu zwycięstwo bo dla statement działa szybko
W PreparedStatement zamiast setTimestamp(…) to setDate(…) -> tu znowu z Date działa szybko…

Wstępna diagnoza: wolno działa jak się przekazuje jako argument filtrowania timestamp a szybko jak date, czyli dla uproszczonego przypadku testowego w SQLPlus:
SELECT count(*) FROM foo
WHERE foo.last_changes_date > to_timestamp('20130627 00:00:00', 'yyyymmdd HH24:MI:SS')
Czas trwania: 121.595 [s] (!)
SELECT count(*) FROM foo
WHERE foo.last_changes_date > to_date('20130627', 'yyyymmdd')
Czas trwania: 0.256 [s]
Dalej, okazuje się że do metody to_date można przekazać godzinę i to działa (!), dwa poniższe zapytania zadziałają i zwrócą różne (prawidłowe) liczby rekordów:
SELECT count(*) FROM foo
WHERE foo.last_changes_date > to_date('20130627 00:00:00', 'yyyymmdd HH24:MI:SS')
SELECT count(*) FROM foo
WHERE foo.last_changes_date > to_date('20130627 18:00:00', 'yyyymmdd HH24:MI:SS')
Problem okazał się być znany: http://stackoverflow.com/. Wynika z tego że jak się użyje timestamp’a to zamiast użycia indeksu jest full-scan z użyciem metody konwertującej timestamp -> date i trwa to długo.
Jakie okazało się rozwiązanie? Przy ustawianiu atrybutów PreparedStatement wystarczy zamiast:
pstmt.setTimestamp(1, lastSyncTimestamp);
zrobić:
pstmt.setObject(1, lastSyncTimestamp, java.sql.Types.DATE);
Magia, człowiek myślał że zna już wszystko w JDBC a tu taka niespodzianka… 4 godziny z wieczoru w plecy.