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:

1. Poziom izolacji -> nic
2. Samodzielne sterowanie transakcją -> nic
3. Semafor na bazie danych (na wszelki wypadek) -> nic
4. Connection własny zamiast z datesource’a z serwera aplikacyjnego -> nic
5. Zwykły Statement zamiast PreparedStatement -> tu zwycięstwo bo dla statement działa szybko
6. 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.