SQL where ID IN (id1, id2, ..., idn)

J'ai besoin d'écrire une requête pour récupérer une grosse liste d'id.

Nous soutenons plusieurs backends (MySQL, Firebird, sql server, Oracle, PostgreSQL ...) donc j'ai besoin d'écrire un standard SQL.

La taille de la pièce d'identité peut être grande, la requête serait généré par programme. Alors, quelle est la meilleure approche?

1) l'Écriture d'une requête à l'aide de

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

Ma question ici est de. Ce qui se passe si n est très grand? Aussi, ce sujet de la performance?

2) l'Écriture d'une requête à l'aide de OU

SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn

Je pense que cette approche n'a pas de n limite, mais que dire de la performance si n est très grand?

3) l'Écriture d'une solution de programmation:

  foreach (id in myIdList)
  {
      item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = " + id);
      myObjectList.Add(item);
  }

Nous avons connu quelques problèmes avec cette approche lorsque le serveur de base de données est interrogée sur le réseau. Normalement est préférable de faire une requête qui récupère tous les résultats, mieux que beaucoup de petites requêtes. Peut-être que je me trompe.

Ce serait une bonne solution pour ce problème?

  • Option 1 réduit de manière significative le serveur SQL temps de réponse, la sélection de 7k ID, dont certains n'existaient pas. Normalement, la requête a pris environ 1300ms, il se réduit à 80ms à l'aide de IN ! J'ai fait le mien en tant que votre solution 1 + 3. Juste la dernière question était une, longue chaîne de requête envoyée à SQL à exécuter.
InformationsquelleAutor Daniel Peñalba | 2011-04-27
  1. 89

    L'Option 1 est la seule bonne solution.

    Pourquoi?

    • L'Option 2 est identique, mais vous répéter le nom de la colonne beaucoup de temps; de plus, le moteur SQL ne sait pas immédiatement que vous voulez vérifier si la valeur est l'une des valeurs dans une liste fixe. Cependant, un bon moteur SQL peut l'optimiser pour avoir des performances égales comme avec IN. Il y a encore de la lisibilité question si...

    • L'Option 3 est tout simplement horrible en terme de performance. Il envoie une requête à chaque boucle et les marteaux de la base de données avec les petites requêtes. Elle empêche également d'utiliser toutes les optimisations de la valeur "est l'un de ceux-ci dans une liste donnée

    • Je suis d'accord, mais notez que la liste est limitée dans de nombreux SGBDR et donc vous auriez besoin d'utiliser @Ed Guiness est une solution, mais ici, les tables temporaires diffèrent entre les SGBDR. (Efficace pour les problèmes complexes que vous ne pouvez pas utiliser uniquement de la norme SQL)
    InformationsquelleAutor ThiefMaster
  2. 24

    Une approche alternative pourrait être d'utiliser un autre tableau pour contenir les valeurs d'id. Cette autre table peut alors être interne rejoint sur votre TABLE de contraindre les lignes retournées. Cela aura l'avantage majeur que vous n'aurez pas besoin de SQL dynamique (problématique dans le meilleur des cas), et vous n'aurez pas infiniment longue, DANS la clause.

    Vous serait tronquer cette autre table, insérez votre grand nombre de lignes, alors peut-être créer un index à l'aide de la jointure de la performance. Il serait aussi laissez-vous détacher de l'accumulation de ces lignes à partir de la récupération de données, peut-être vous donner plus d'options pour optimiser les performances.

    Mise à jour: Même si vous pouvez utiliser une table temporaire, je ne voulais pas insinuer que vous devez ou même le. Une table permanente utilisée pour les données temporaires est une solution commune avec les mérites, au-delà de celle décrite ici.

    • Mais comment pourriez-vous donner la liste des identifiants que vous avez besoin? (Voir, vous ne pouvez pas sélectionner une plage ou quelque chose comme ça).
    • la liste des Identifiants qui auraient pu être obtenues à l'aide d'un select instruction sur une autre table. La liste est passée comme les autres de table vous sont inner joining contre.
    InformationsquelleAutor Ed Guiness
  3. 10

    Ce Ed Guiness proposé est vraiment un booster la performance , j'ai eu une requête de ce

    select * from table where id in (id1,id2.........long list)

    ce que j'ai fait :

                                DECLARE @temp table(
                                        ID  int
                                        )
                            insert into @temp 
                            select * from dbo.fnSplitter('#idlist#')

    Alors interne rejoint le temp avec table principale :

    select * from table inner join temp on temp.id = table.id

    Et la performance s'est améliorée considérablement.

    • Salut, est-fnSplitter une fonction de MSSQL? Parce que je n'étais pas capable de le trouver.
    • Ce n'est pas une chose standard. Ils doivent dire qu'ils ont écrit que, à cet effet, ou, par exemple, avait une application qui est déjà fait.
    • fnSplitter est une fonction créée par Ritu, vous pouvez trouver sur internet/google pareille
    InformationsquelleAutor Ritu
  4. 7

    Première option est certainement la meilleure option.

    SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

    Cependant considérant que la liste des identifiants est très grand, dire des millions, vous devriez envisager de tailles de segment comme ci-dessous:

    • Vous diviser liste d'Id dans des blocs de numéro de fixe, de 100
    • Taille de bloc doit être décidées basé sur la taille de la mémoire de votre serveur
    • Supposons que vous avez 10000 Id, vous aurez 10000/100 = 100 morceaux
    • Processus de segment résultant de 100 appels de base de données pour sélectionner

    Pourquoi devriez-vous diviser en morceaux?

    Vous n'obtiendrez jamais de débordement de la mémoire exception qui est très commun dans les scénarios comme la vôtre.
    Vous aurez optimisé nombre d'appels de base de données, résultant en une meilleure performance.

    Il a toujours travaillé comme un charme pour moi. Espérons que cela fonctionne pour mes collègues développeurs ainsi 🙂

    InformationsquelleAutor Adarsh Kumar
  5. 3

    Dans la plupart des systèmes de base de données, IN (val1, val2, …) et une série de OR sont optimisés pour le même plan.

    La troisième voie serait de l'importation de la liste de valeurs dans une table temporaire et se joindre à elle qui est plus efficace dans la plupart des systèmes, si il y a beaucoup de valeurs.

    Vous pouvez lire cet article:

    InformationsquelleAutor Quassnoi
  6. 3

    Échantillon 3 serait le moins performant de tous car vous frapper jusqu'à la base de données un nombre incalculable de fois sans raison apparente.

    Charger les données dans une table temporaire, puis de le rejoindre sur ce serait de loin le plus rapide. Après que l'EN doit travailler un peu plus rapidement que le groupe de Rup.

    InformationsquelleAutor judda
  7. 3

    Faire le SELECT * from Matable where id in () de commande sur une table SQL Azure, avec plus de 500 millions de disques a abouti à un temps d'attente de > 7min!

    Au lieu de résultats renvoyés immédiatement:

    select b.id, a.* from MyTable a
join (values (250000), (2500001), (2600000)) as b(id)
ON a.id = b.id

    Utiliser une jointure.

    InformationsquelleAutor JakeJ
  8. 2

    Je pense que tu veux dire SqlServer, mais sur Oracle il est difficile de limiter le nombre d'éléments que vous pouvez spécifier: 1000.

    • Même SQL Server cesse de fonctionner après ~40k DANS les éléments. Selon MSDN:, y Compris un très grand nombre de valeurs (plusieurs milliers) dans une clause peut consommer les ressources et les erreurs de retour 8623 ou 8632. Pour contourner ce problème, stocker les éléments de la liste dans un tableau.
    InformationsquelleAutor flq