Discussion :

[alaks]

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 :

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

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

[cavo789]

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).

[alaks]

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)

[CinePhil]

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 ?

[ylarvor]

- 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...

[alaks]

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.

É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

[CinePhil]

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é.

[alaks]

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.

[Monstros Velu]

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 ^^

[alaks]

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.

[Antoun]

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.

[alaks]

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...

[Antoun]

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

Avec un CREATE SELECT dans ta proc stock ?

[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 ...

[Antoun]

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.

[alaks]

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 ...

[pacmann]

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.

[alaks]

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.

[Antoun]

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...

[ylarvor]

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.