La même requête est plus lente avec hibernate

Les utilisateurs n'aiment ni interrompre leur travail ni regarder le sablier
Répondre
Phil
Administrateur du site
Messages : 266
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

La même requête est plus lente avec hibernate

Message par Phil »

Merci à Lionel pour sa question : "avec la base oracle, une requête sur une table de personnes avec un filtre sur la date de naissance prend 20s lorsqu'elle est lancée par hibernate alors que la réponse est immédiate depuis la ligne de commande. pourquoi ?"

(note : la requête fournie pour recherche sur la date de naissance était de la forme select c1 from t1 where c2 between :1 and :2 , le between peut surprendre mais le type DATE d'Oracle Database est en fait un DATETIME précis à la seconde, le programmeur n'était peut-être pas absolument certain que la partie time est toujours à minuit dans la colonne)

Réponse :
Une trace via le snapper donnait un DIRECT PATH READ, la requête entraînait un FULL TABLE SCAN de la table au lieu de passer par l'index. Une interrogation de v$sql_bind_capture donnait des timestamps pour les bind variables alors que la colonne est de type date.
Nous sommes sur un problème similaire à celui décrit ici : http://pgphil.ovh/traqueur_96_03.php
La colonne est de type DATE dans la table alors que les variables définies dans le programme sont de type TIMESTAMP. Une fonction INTERNAL_FUNCTION est appliquée sur la colonne c2 pour effectuer la conversion implicite, ce qui empêche de tirer parti de l'index et même dans ce cas de créer un function (ou expression) based index comme contournement.
De manière générale, les conversions implicites sont très souvent possibles mais à éviter absolument avec Oracle Database. Démonstration :

Code : Tout sélectionner

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

create table t1 as
with g as (select rownum id from dual connect by level <= 500000)
select rownum c1 , current_date - dbms_random.value()*10000 c2 from g;  2    3
Table created.

create index t1_i1 on t1(c2);
Index created.

desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 C2                                                 DATE

explain plan for select c1 from t1 where c2 between to_timestamp('25-04-1997 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and to_timestamp('25-04-1997 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   308 |   356   (3)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T1   |    14 |   308 |   356   (3)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - filter(INTERNAL_FUNCTION("C2")>=TIMESTAMP' 1997-04-25
              00:00:00.000000000' AND INTERNAL_FUNCTION("C2")<=TIMESTAMP' 1997-04-25
              23:59:59.000000000')

Note
-----
   - dynamic sampling used for this statement (level=2)


explain plan for select c1 from t1 where c2 between to_date('25-04-1997 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and to_date('25-04-1997 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
Explained.

select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1429545322

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    14 |   308 |   346   (1)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    14 |   308 |   346   (1)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |  1938 |       |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("C2">=TO_DATE(' 1997-04-25 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "C2"<=TO_DATE(' 1997-04-25 23:59:59', 'syyyy-mm-dd
              hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement (level=2)


Avec java et les dates Oracle, une solution généralement présentée est d'utiliser le type java.sql.Timestamp et de réaliser quelque chose du style
PreparedStatement req = connection.prepareStatement(
"SELECT c1 " +
"FROM t1" +
"WHERE c2 >= CAST(? AS DATE) " +
"AND c2 < CAST(? AS DATE)");

Nous comparons des dates avec des dates donc si l'index sur c2 est bien en place le tour est joué.

Et PostgreSQL ? Dans le cas présenté sur le site, http://pgphil.ovh/traqueur_96_03.php , PostgreSQL refusait complètement la conversion implicite (ce qui est très bien !) et il fallait vraiment être motivé pour obtenir le problème de performance.
Avec des dates et des timestamps, PostgreSQL accepte d'effectuer des comparaisons mais cela ne pose pas le même problème qu'avec Oracle Database :

Code : Tout sélectionner

select version();
                                                          version
---------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.1 on x86_64-pc-linux-gnu (Debian 10.1-2.pgdg90+1), compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit

create table t1 as select generate_series(1, 500000, 1) c1, (current_date - (ceil(random()*10000) || ' days')::interval)::date c2;
SELECT 100000

create index t1_i1 on t1(c2);
CREATE INDEX

\d t1
                     Table « public.t1 »
 Colonne |  Type   | Collationnement | NULL-able | Par défaut
---------+---------+-----------------+-----------+------------
 c1      | integer |                 |           |
 c2      | date    |                 |           |
Index :
    "t1_i1" btree (c2)


explain select c1 from t1 where c2 between to_timestamp('25-04-1997 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and to_timestamp('25-04-1997 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
                                                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.97..193.42 rows=53 width=4)
   Recheck Cond: ((c2 >= to_timestamp('25-04-1997 00:00:00'::text, 'DD-MM-YYYY HH24:MI:SS'::text)) AND (c2 <= to_timestamp('25-04-1997 23:59:59'::text, 'DD-MM-YYYY HH24:MI:SS'::text)))
   ->  Bitmap Index Scan on t1_i1  (cost=0.00..4.96 rows=53 width=0)
         Index Cond: ((c2 >= to_timestamp('25-04-1997 00:00:00'::text, 'DD-MM-YYYY HH24:MI:SS'::text)) AND (c2 <= to_timestamp('25-04-1997 23:59:59'::text, 'DD-MM-YYYY HH24:MI:SS'::text)))
(4 lignes)

Il vaut mieux toujours comparer une colonne de type X avec des variables de type X mais ici comparer une colonne de type DATE avec des TIMESTAMPS n'empêche pas de tirer parti de l'index.
Cdlt. Phil - pgphil.ovh
Répondre