Sql Clause IN

Bonjour,

Suite à une recherche sur google je n'ais toujours pas trouvé une solution concrète à mon problème.
Ce que je souhaite faire c'est lancer des requêtes sql avec une clause in qui peut contenir un trés grand nombre de résultats, Et c'est à priori un type de requête qui sera souvent exécutée.

Exemple de requête

Code:

---

1 2 3

SELECT idref FROM Matable WHERE id IN (212, 787, 898 ,89898, 8989 , 444, ...

---

(il peut y en avoir 50000))
et la table Matable contient environ 3 millions de lignes.

Et donc comme on peut l'attendre l'execution de la requête précédente peut être un petit plus longue (~20 secondes) pour une application qui doit être réactive c'est pas terrible.
la question que je me poses c'est qu'elle serait la meilleur méthode pour répondre à ce genre de pb:
- les tables temporaires ? pas top j'ai des erreurs de mémoire insuffisantes
- une application externe qui charge la table et qui effectue le traitement à la place ? (ca prendra bp de place mémoire pour qu'un seul type de requête)

Alors si vous avez d'autres pistes je suis vraiment preneur.

Merci d'avance

Note : Utilisation de SQL Server 2005

Une idée : utilises une table de travail qui contiendra la liste des ID que tu veux rechercher. Fais un TRUNCATE de ta table pour la vider complétement puis un INSERT pour y ajouter chaque ID de ta clause IN. Ensuite, fais une simple requête SELECT qui utilisera cette table de travail et MaTable avec une liaison sur le champs commun ID.

Penses bien sûr à indexer le champs ID (voire le mettre en cléf primaire).

Dans ce cas là ca reviendrai à utilisé une table temporaire et ajouter les id dedans et de faire une jointure dessus. C'est ce que j'avais déja fait , mais c'est trop lent. J'ai tout de même essayer avec une table simple(table de travail) mais ca m'est tout autant de temps.

La difficulté c'est que ce type de requêtes peut être executé par différents utilisateurs en même temps et avec des valeurs dans la clause IN différentes (c'est pour ça que j'avais pensée aux tables temporaires mais les performances ne le permettent pas)

Comment sont déterminées les id qui doivent être dans la clause IN ?
Si c'est le résultat d'une requête, pourquoi ne pas faire une simple jointure ?

Citation:

---

- les tables temporaires ? pas top j'ai des erreurs de mémoire insuffisantes
- une application externe qui charge la table et qui effectue le traitement à la place ? (ca prendra bp de place mémoire pour qu'un seul type de requête)

---

Évidemment en SQL, ce serait mieux, mais avec 50 000 entrées dans un IN, la requête me parait un peu "obèse". Une solution intermédiaire entre une solution tout SQL et une solution pure .net serait pourquoi pas une procédure stockée en .net. Bien que cela demande un investissement en recherche/développement important pour une performance résultante inconnue, est ce que ce sera plus rapide que 20 secondes...:roll:

Citation:

---

Comment sont déterminées les id qui doivent être dans la clause IN ?
Si c'est le résultat d'une requête, pourquoi ne pas faire une simple jointure ?

---

Les valeurs sont générées par une application annexe. Cette application ne renvoit que des valeurs.

Citation:

---

Évidemment en SQL, ce serait mieux, mais avec 50 000 entrées dans un IN, la requête me parait un peu "obèse". Une solution intermédiaire entre une solution tout SQL et une solution pure .net serait pourquoi pas une procédure stockée en .net. Bien que cela demande un investissement en recherche/développement important pour une performance résultante inconnue, est ce que ce sera plus rapide que 20 secondes...

---

La procédure stockée elle effectuerai une insertion dans une table puis ferai la jointure et retournerai les résultats c'est bien ca ? prquoi une procédure stockée et pkoi en .net ?

Merci

Citation:

---

Envoyé par alaks

Les valeurs sont générées par une application annexe. Cette application ne renvoit que des valeurs.

---

Elle les renvoie sous quelle forme ? Un fichier texte ?
Cette application ne fait-elle pas elle-même appel à la même base de données ?
Si oui, n'y aurait-il pas moyen de récupérer la requête qu'utilise l'application pour générer ces données ?

Difficile de t'aider sans en savoir davantage sur ton système, cette application et le but recherché.

Citation:

---

Elle les renvoie sous quelle forme ? Un fichier texte ?
Cette application ne fait-elle pas elle-même appel à la même base de données ?
Si oui, n'y aurait-il pas moyen de récupérer la requête qu'utilise l'application pour générer ces données ?

Difficile de t'aider sans en savoir davantage sur ton système, cette application et le but recherché.

---

C'est vrai, jsui un peu distrait certaines fois.
L'application annexe c'est un moteur de recherche. Ce moteur de recherche permet de récupérer toutes les images indéxées par le mot clé spécifié en entrée.
Et en résultat je récupére une liste d'identifiant d'images que j'utilises pour récupérer les images et les afficher. Le fait est que comme le moteur de recherche ne retourne que des identifiants et j'ai besoin d'informations complémentaires sur ces images pour diverses raisons : telles que leur date de création, le format de plus haut niveau disponible et plus important encore leur identifiant de publiication(différe de l'identifiant retourné par le moteur par son format).
Jusque là j'avais pas de trop de problème, puisque j'utilisai l'asynchronisme pour telecharger toutes ses informations, donc ca donnait l'impression que c'était plutot rapide.
Mais aujourd'hui je dois transmettre cette liste d'identifiants avec ses informations complémentaires, à une application gérée par une société externe qui ne gére pas d'asynchronisme.
Cette application fait la même chose que notre interface web, mais sa spécification ne permet pas de faire autrement que de lui renvoyer toutes les informations en une seule fois.

Voila, jesperes avoir été un peu plus clair.

Un inner join de 3 000 000 par 50 000 qui met 20s, je trouve que ça fait beaucoup.

Alors questions bêtes :
-Y a-t-il un index sur la colonne id ? ^^
-Le serveur est-il bien dimensionné ? Je me rapelle avoir voulu optimiser une requête, avant d'avoir simplement recommandé de changer de serveur tellement il était sous-dimensionné.

De toutes façons, le "in" sera moins performant que la jointure, puisque la jointure est la raison de vivre du SQL ^^

oui il y a un index cluster sur la colonne id, et le serveur est a priori bien dimensionné (machine 64 bits 16go RAM, proc quad core).
C'est vrai qu'une jointure ca serait mieux, mais c difficile en ayant directement les valeurs.
une jointure dans mon cas ne pourrait se faire que par une table temporaire, mais la somme des opérations pour construire, ajouter et comparer les elements d'une table temporaire fait que j'arrive quasiment aux même perfs qu'une requete IN.

Citation:

---

Envoyé par alaks

une jointure dans mon cas ne pourrait se faire que par une table temporaire, mais la somme des opérations pour construire, ajouter et comparer les elements d'une table temporaire fait que j'arrive quasiment aux même perfs qu'une requete IN.

---

Ajoute un index sur ta table temporaire, et fais-le en jointure.

je viens d'effectuer un test :
Ajout de 5010 valeurs dans une table temporaire : ~7 secondes

Et ceci sans encore avoir fait de création d'index ni de jointure...

Citation:

---

Envoyé par alaks

je viens d'effectuer un test :
Ajout de 5010 valeurs dans une table temporaire : ~7 secondes

---

Avec un CREATE SELECT dans ta proc stock ?

Euh nn ct pas une procédure stockée :?. Je peux pas faire de create select puisque les valeurs que j'ai sont en dur.

Pr le précédent test d'insertion, j'ai essayé ces deux méthodes d'insertion (pr un résultat identique):
- insertion unitaire :
insert into #matable_tmp(id) values ('123342') (ceci 5000 fois)
- insertion "multiple" :

insert into #matable_tmp(id) values select '1212' union all select '4343' union all ...

Citation:

---

Envoyé par alaks

Euh nn ct pas une procédure stockée :?. Je peux pas faire de create select puisque les valeurs que j'ai sont en dur.

Pr le précédent test d'insertion, j'ai essayé ces deux méthodes d'insertion (pr un résultat identique):
- insertion unitaire :
insert into #matable_tmp(id) values ('123342') (ceci 5000 fois)
- insertion "multiple" :

insert into #matable_tmp(id) values select '1212' union all select '4343' union all ...

---

Dans ce cas, il faut que tu fasses un fichier et un LOAD DATA INFILE.

Citation:

---

Dans ce cas, il faut que tu fasses un fichier et un LOAD DATA INFILE

---

Tiens je connaissais pas ca, je vais tester pour voir ...

Salut !

Juste une petite question idiote : c'est interactif ton truc ?
Si oui, combien de lignes est sensée te retourner la requête ?
Parce que si c'est de l'ordre de 50 000, l'utilisateur risque de ne pas toutes les visualiser en même temps...
Et si c'est du "semi-interactif" (genre ça lui génère un export), ça vaut peut être le coup d'attendre vu la taille du résultat.

Citation:

---

Juste une petite question idiote : c'est interactif ton truc ?
Si oui, combien de lignes est sensée te retourner la requête ?
Parce que si c'est de l'ordre de 50 000, l'utilisateur risque de ne pas toutes les visualiser en même temps...
Et si c'est du "semi-interactif" (genre ça lui génère un export), ça vaut peut être le coup d'attendre vu la taille du résultat.

---

Je vois pas forcément ce que tu entends pas intéractif, mais comme je l'ai expliqué un peu plus haut, je ne peux pas faire de traitement différé, je dois retourner tous les résultats d'un coup. Typiquement l'utilisateur choisit le nombre de résultats qu'il veut visualiser sur son client lourd et le serveur doit lui retourner ts les résultats d'un coup. Et le souci c'est que cette application est gérée par une application externe:S.

Citation:

---

Envoyé par alaks

Tiens je connaissais pas ca, je vais tester pour voir ...

---

MySQL affirme que c'est environ 20 fois plus rapide qu'une série d'INSERT. Reste à voir quel est le temps de création du fichier...

la commande LOAD DATA INFILE de MySQL est probablement l'équivalent de la commande bulk insert de sql serveur. Une autre forme de l'outil bcp mais en inline. Il semble que BULK INSERT soit performant aussi pour un import de 50 000 lignes sous SQL Serveur.

Citation:

---

Envoyé par alaks

...Note : Utilisation de SQL Server 2005

---

Effectivement, c'est plutôt SQL Server qui importe dans ce cas... ;)

alaks : ce que je voulais dire un peu plus haut, c'est simplement que si je devais me taper un écran (client lourd où léger) avec 50k lignes, j'aurais du mal à les exploiter d'un seul coup...
Si c'est vraiment juste pour visualiser, on pourrait imaginer un système de pagination où tu envoies tes paquets de IN selon le besoin...

Citation:

---

Envoyé par pacmann

Effectivement, c'est plutôt SQL Server qui importe dans ce cas... ;)

---

Oui effectivement j'utilises bien SQL server, mais j'étais parti pour chercher l'equivalent du load init de mysql sous sql server.
Donc merci pour l'idée Antoun :)
Dés demain j'essai avec un bulk insert et je croise les doigts..

Citation:

---

Envoyé par pacmann

alaks : ce que je voulais dire un peu plus haut, c'est simplement que si je devais me taper un écran (client lourd où léger) avec 50k lignes, j'aurais du mal à les exploiter d'un seul coup...
Si c'est vraiment juste pour visualiser, on pourrait imaginer un système de pagination où tu envoies tes paquets de IN selon le besoin...

---

Oui je serai tout à fait d'accord avec toi dans la mesure ou si c'était moi qui contrôlait le client, or c'est pas le cas. Tous ce que je dois faire c'est lors d'un appel à cette fonction de recherche, je dois retourner tous les résultats.

désolé, j'étais sur l'idée qu'on faisait du MySQL :aie: (ça doit être l'âge).

Merci à ylarvor d'avoir rétabli et donné l'équivalence !

Impressionant le bulk insert, j'ai fait un bulk insert de 5 000 lignes ca a mise 0 secondes (enfin qq millisecondes qd mm); c'est terrible.
Par contre j'ai pas tester le temps que ça prendrait pour créer un fichier de 5000 lignes.

Donc ac cette méthode j'ai pu faire une jointure:

Code:

---

1 2	select idref from maTable A, tableCree B, where A.photoid = B.photoid

---

mais je crois qu'il me manque un index ou qq chose parce qu'elle mets 5 secondes. Je vais voir ça un peu plus en détails.

Citation:

---

Envoyé par alaks

mais je crois qu'il me manque un index ou qq chose parce qu'elle mets 5 secondes. Je vais voir ça un peu plus en détails.

---

Sur tableCree.photoID.

A un moment j'ai révé, mais finalement un peu moins :oops:

J'ai fait un script qui effectue toutes les opérations allant de la création de fichier à la jointure en passant par un bulk voici les résultats pour une liste de valeurs de 47000 :

* les temps sont en millisecondes enfin je crois :D

Temps création fichier : 0.0156
Temps création table : 0.156
Temps bulk insert: 1.01 ( en moyenne 0.550)
Temps création index : 0.260
Temps jointure : ... 8,9

Total : 8.921

Temps du drop : 0.01

C'est dommage que la jointure prennent autant de temps . (jointure entre un ensemble de 3 000 000 et 47 000 valeurs)

Tu as bien un index sur maTable.idref ?

Que raconte l'explain plan ?

je retrouve espoir j'y suis presque

Pour la jointure j'utilisais un index cluster, je me suis dit que c'etait pas terrible vu la quantité de données. Et le plan d'execution montrait que 100% des op se faisait sur l'index cluster(index scan).
Alrs j'ai créer un index non cluster sur id et j'ai ajouter idref en colonne incluse (un index bp plus petit que le précedent). La jointure se fait maintenant en 4.76 s, la total des opérations se fait en 5.56s.
Je suis sûr que ca peut être encore amélioré...

En tout cas merci pour vos conseils, qui m'ont été très bénéfique.

Idéalement il ne faudrait que IdRef dans l'index sur votre table principale, dans ce cadre précis, mais d'autres contraintes font que c'est peut-être impossible.

Citation:

---

Envoyé par Waldar

Idéalement il ne faudrait que IdRef dans l'index sur votre table principale, dans ce cadre précis, mais d'autres contraintes font que c'est peut-être impossible.

---

Euh, idRef est la seule colonne dans le select et id la colonne de jointure... je crois que l'index est parfait, non ?

En tous cas, ça m'intéresserait aussi de voir l'explain plan....

[EDIT]

Tiens, je suis peut être complètement à côté de la plaque, mais je testerais bien avec un index sur la TableCree.
Peut être qu'il provoquerait un merge sort join qui irait plus vite...

Voici le plan d'execution :

Pièce jointe 36859

Comme vous pourrez le voir, il y a un index sur la table TableCree.

Outre l'amélioration des perfs, il me restera plus qu'à tester si c'est possible de faire la même chose avec une table temporaire. Parce que mes tests ont portés jusqu'à présent sur une table "normale".

Compte-tenu que la TableCree va être intégralement parcourue, il ne faut pas l'indexer.

Mais je ne sais pas si celà va modifier le plan d'exécution ou accélerer la requête.

Si, ça va changer le plan :
S'il peut obtenir la donnée de l'index, il envisagera plus facilement le MERGE JOIN, car les données sont triables facilement.

Sinon, à mon avis, il aura plus de chance de choisir HASH JOIN...