Discussion :

[Targan]

Bonjour,

voila j'ai une problématique simple, mais l'analyse se révèle compliquée pour moi.

Imaginons une base (oracle) ayant une table contenant des valeurs simples style varchar.
Le besoin est de tester la présence ou non d'un certain nombre de valeurs.

Est il plus judicieux de répéter n fois (par une boucle for dans le prog) un :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 select my_value from my_table where my_value = "la_valeur_à_tester"

ou de tenter de faire une requête "plus globale" pour tester en 1 seule requete ?

Si c'est faisable en 1 seule requete, quelle en serait son écriture ?

Merci.

nb: on peut estimer (je n'ai pas les chiffres) qu'il existe des milliers (voir centaines de milliers) de valeurs dans la table, et qu'on recherche entre 0 et 3000 valeurs. La limitation pour une seule requete ne serait elle finalement pas la limitation de la taille textuelle de la requete ?

[edit] mes valeurs à retrouver sont fournies par un élément externe, ce n'est pas une sous requete.

[CinePhil]

La syntaxe générale pour ce genre de chose est celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT les_colonnes_souhaitees
FROM la_table
WHERE la_colonne IN (val1, val2, valn)

Il faudrait donc que l'outil externe construise la liste de valeurs à chercher séparées par des virgules et préparer la requête avec cette liste.

Mais je ne sais pas quelle longueur maximale de liste est supportée. Et avec 3000 valeurs à chercher, je me demande s'il ne serait pas plus rapide de créer une table temporaire et d'y insérer les valeurs puis de faire une jointure avec cette table temporaire.

[Targan]

ok et donc dans le cas d'une requete globale en select...in , faire un découpage toutes les 1000 valeurs vu qu'Oracle n'accepte que 1000 valeurs "externes" dans un clause IN ?

Est ce que d'un point de vue conception / optimisation / perfs il est préférable de faire une seule requete IN comprenant une clause where conséquente à une multitude de requetes simples ?

[edit] l'idée de la table temporaire en jointure est sympathique anyway.

Merci.

[Waldar]

La question est comment récupérez-vous votre liste de valeurs ?
Cela influe sur le choix de la réponse technique.

[Targan]

Elle arrive dans une liste de chaine de caractère extraite depuis un fichier, et cette liste est elle même testée pour en définir une nouvelle plus complète ensuite.

[Waldar]

Est-ce possible d'agrémenter d'un petit exemple ?

[Targan]

Voici un bout de code (java) de l'existant.

Il s'agit de parcourir une hashmap, et de vérifier l'existence dans la base de chaque valeur.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
public static List<String> getAlreadyExist(HashMap<String, String>  myHashMap) throws Exception {
 
...
 
List<String> alreadyExist = new ArrayList<String>();
for (String cle : myHashMap.keySet()) {
  List<String> lstNames = getNames(myHashMap.get(cle));
  for (String curName : lstNames) {
    String request = "SELECT * FROM TOTO WHERE NOM = '" + curName + "'";
    rs = stmt.executeQuery(request);
    if (rs.next())
      alreadyExist.add(curName);
  }
}

Ce code a été fait par une tierce personne.

Bref on se retrouve avec une liste (hashmap) qui récupère une liste pour chaque valeur.
Du coup, en fonction du nombre de valeur, y a n requetes executées pour un truc qui pourrait probablement être fait en 1 seule.


Je vais probablement tenter d'exploser tout cela pour construire une requete dynamiquement avec un WHERE IN...
Ne sachant pas le nombre "moyen" de valeurs à tester, faut que je split le IN pour mettre 1000 valeurs max par clause IN (limitation Oracle).

Qu'en pensez vous ?

Merci.

nb: j'ai volontairement changé qq noms dans l'exemple ;p

[CinePhil]

Et la hashMap, elle est générée comment ? Pas à la main j'espère !

[Waldar]

Pour la partie HashMap Java, aucune idée je ne sais pas ce que c'est, donc difficile de vous répondre, est-ce que ce sont des données déjà récupérées dans votre base ou bien saisies par un utilisateur ?

Pour la partie SQL, c'est mauvais.
La requête ne doit pas être en clair dans le code, il n'y a pas de variable de liaison, pas de preparated statement, c'est probablement difficile de faire pire !

[Targan]

voila pourquoi je tente de faire un peu de ménage, et que je réfléchis à des optimisations.

Voir 300 requêtes quasi identique dans le log ne m'enchante guère...

Concernant la hashmap, c'est une simple liste clé/valeur. Elle est renseignée par des infos issues d'un fichier, tout cela est fait en amont et ne provient pas de la base....

[Waldar]

Quelle est votre version d'Oracle ?

Est-ce possible de voir un extrait de ce fichier ?
Pas de soucis à ce que vous modifiez les données, c'est la logique qui m'intéresse.

Que faites-vous une fois que vous savez si la valeur existe ou n'existe pas ?

[CinePhil]

Ce qui serait à mon avis plus rapide, c'est de générer un fichier texte formaté pour qu'il puisse être injecté directement dans une table temporaire Oracle. Ne connaissant pas Oracle, je ne sais ce qu'il est possible de faire dans ce domaine.

Après un éventuel indexage de cette table temporaire, il est ensuite facile de faire une jointure avec. Ainsi le processus est constant quel que soit le nombre de valeurs à chercher et la jointure est l'opération la plus optimisée qui soit dans un SGBDR. Et il n'y a plus besoin que d'une seule requête.

[Targan]

@Waldar: non je ne peux pas mettre un extrait du fichier, c'est un fichier un peu particulier et assez gros.

@Cinephil: oui la solution a été proposée et semble intéressante. Maintenant, je ne sais pas non plus si il est possible de le donner à Oracle directement ou si il faut se farcir les INSERT ce qui au final serait moins performant que le SELECT pour chaque, non ?


Je dois vraiment avancer, donc je vais clore le débat. Les solutions proposées sont intéressantes, et laisses pas mal de possibilité. Je vais voir en fonction du temps que j'ai si je peux tester des optimisations....car c'est tjs la même histoire, pour le moment j'ai laissé le traitement par défaut, on verra.

Merci à tous.

[nuke_y]

La méthode que je préfère: table temporaire dans laquelle on insére les n valeurs + jointure.

La méthode à la rigueur (lecture seule): n requêtes bien faites sur 1 valeur chacune

La méthode qui-marche-mais-qui-mérite-le-fouet:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from ma_table WHERE my_value in ('valeur1', 'valeur2', 'valeur', ..., 'valeurN')

Limitée à 1000 valeurs.

La méthode qui-marche-mais-qui-mérite-la-mort:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from ma_table WHERE instr('valeur1-valeur2-valeur3-...-valeurN', my_value)>0

A noter que si la concaténation des n valeurs > 4000 caractères, ça ne marchera pas non plus.

[Waldar]

Je ne peux toujours pas me prononcer sur la meilleure méthode puisque je n'ai pas pu voir le fichier de départ.

Mais à partir d'Oracle 10g la meilleure méthode sera une external table (qui pointera directement sur le fichier) en jointure, comme ça il n'y a rien à faire si ce n'est déposer le fichier.

Pas de requête SQL dans le code java non plus, plutôt un appel à une procédure.

Edit : prenez trente minutes et lisez ce papier :
http://method-r.com/downloads/doc_do...e-cary-millsap

[nuke_y]

Bonne idée l'External Table mais je ne connais pas les limitations. S'il faut que le programme JAVA dépose un fichier sur un disque accessible par le serveur Oracle, ça peut poser de gros souci aux DBA.

[Targan]

Bien que ce soit surement la meilleure solution, cette solution n'est pas envisageable dans mon cas...

Le fichier est bien trop complexe, les valeurs qui nous intéresse ne sont qu'une infime partie de celui-ci, comprises dans des balises spéciales, trop de pb de droit & co....

Je crois que pour le moment je ne vais toucher à rien, c'est à dire laisser cette méthode là :

La méthode à la rigueur (lecture seule): n requêtes bien faites sur 1 valeur chacune

Mais pour ma culture personnelle, Nuke, pourquoi le SELECT....WHERE...IN est il plus à bannir que les n requêtes SELECT ?

Idem, pourquoi l'insertion + jointure est il plus intéressant ? Que la jointure soit le plus performant ok, mais les insertions ne sont elles pas pénalisants ? Ce système ne serait il pas viable à condition que notre table temporaire nous sert régulièrement, mais en cas de oneshot ?

Merci pour vos précisions.

@Waldar: merci de tenter d'approfondir plus le sujet, et désolé de ne pouvoir te donner plus d'éléments....je suis un peu coincé de ce côté là, en plus j'ai toutes les données sur un autre poste duquel je ne peux rien extraire :s

[Waldar]

Pas de soucis, je me doute que si vous le ne faites pas c'est que vous ne pouvez pas. Lisez le lien que j'ai mis un peu plus haut, il est bien expliqué la différence avec l'utilisation d'un PreparedStatement et des variables de liaisons par rapport à votre code.

Comme en plus il y a des exemples java, je me dis que vous pouvez l'adapter rapidement à votre cas pour facilement améliorer les performances.

Si vous faites un test d'existence j'imagine que vous avez bien indexé votre colonne de recherche, faites "SELECT CurName" au lieu de "SELECT *", comme ça vous ne regardez que l'index.

[nuke_y]

Parce que le IN est limité à 1000 valeurs. Donc dans ton cas c'est une solution inadaptée puisque tu vas régulièrement dépasser les 1000 valeurs. Maintenant un spécialiste en optimisation JAVA+Oracle te prouveras peut-être que j'ai tort d'un point de vue performances et qu'il faut au contraire exploiter au maximum le IN, quitte à faire 3 requêtes de 1000 valeurs chacunes. C'est un peu un effet buffer. Si tu constates que faire 3000 requêtes est 1000 fois plus long que 3 requêtes avec 1000 valeurs dans le IN, et bien choisi la moins longue.

De même pour l'insertion dans une table temporaire. Un spécialiste te prouvera peut-être que d'un point de vue perfs c'est une erreur, mais en structurant ton analyse en 2 étapes, tu diminues énormément les risques de rencontrer une limite du système. Ca rendra donc ta méthode plus stable.

[Targan]

@Waldar: J'avoue que ce code m'exaspère pas mal car j'hérite du code + de la base, et on code dans des conditions exécrables...Il m'est carrément impossible de faire des tests de requêtes et de comparer les temps d'execution par ex

@Nuke: pour le IN, malgré la limitation, je pensais tester avec une construction dynamique de la requete, et rajouter des conditions IN toutes les 1000 valeurs pour ne pas faire 3 requêtes de IN, mais une seule avec des my_value IN () OR my_value IN () OR my_value IN (). C'est un peu moche mais c'est la seule parade à priori de la limitation des 1000 valeurs.
Par contre, faut que je fasse attention à la limitation de mon "conteneur" de la requête car j'aurais une limitation à 64k il me semble :p