Pourquoi les requêtes basées sur des ensembles relationnels sont-elles meilleures que les curseurs?

la raison principale dont je suis au courant est que les opérations basées sur des ensembles peuvent être optimisées par le moteur en les exécutant sur plusieurs threads. Par exemple, pensez à un quicksort - vous pouvez séparer la liste que vous triez en plusieurs "morceaux" et trier chacun séparément dans leur propre fil. Les moteurs SQL peuvent faire des choses similaires avec d'énormes quantités de données dans une requête basée sur un jeu.

lorsque vous effectuez des opérations basées sur le curseur, le moteur ne peut fonctionner que de façon séquentielle et l'opération doit être monothread.

en plus de "laisser le SGBD faire le travail" (ce qui est une excellente solution), il y a quelques autres bonnes raisons de laisser la requête dans le SGBD:

• C'est (subjectivement) plus facile à lire. en regardant le code plus tard, est-ce que vous préféreriez essayer et analyser une procédure stockée complexe (ou le code côté client) avec des boucles et des choses, ou est-ce que vous préféreriez regarder une déclaration SQL concise?
• il évite les allers-retours réseau. Pourquoi pousser toutes ces données au client et en renvoyer d'autres? Pourquoi bousiller le réseau si vous n'en avez pas besoin?
• C'est du gaspillage. votre SGBD et le(s) serveur (s) de l'application auront besoin d'amortir une partie ou la totalité de ces données pour travailler dessus. Si vous n'avez pas de mémoire infinie, vous allez probablement page sur d'autres données; pourquoi jeter éventuellement des choses importantes de la mémoire à la mémoire tampon un ensemble de résultats qui est la plupart du temps inutile?
• Pourquoi pas vous? Vous avez acheté (ou sur) un très des SGBD fiables et très rapides. Pourquoi ne pas l'utiliser?

les requêtes basées sur Set sont (habituellement) plus rapides parce que:

1. Ils ont plus d'informations pour l'optimiseur de requête afin d'optimiser
2. ils peuvent effectuer des lectures par lots à partir du disque
3. il y a moins de journalisation impliquée pour les retours en arrière, les journaux de transactions, etc.
4. moins d'écluses sont prises, ce qui diminue les frais généraux
5. la logique basée sur les ensembles est le centre d'intérêt des RDBMSs, donc ils ont été fortement optimisés pour cela (souvent, au détriment des procédures la performance)

extraire des données au niveau intermédiaire pour les traiter peut être utile, cependant, parce qu'il supprime la surcharge de traitement du serveur DB (qui est la chose la plus difficile à mettre à l'échelle, et fait normalement d'autres choses aussi bien). De plus, vous n'avez normalement pas les mêmes frais généraux (ou avantages) au palier intermédiaire. Des choses comme la journalisation transactionnelle, le verrouillage et le blocage intégrés, etc. - ils sont parfois nécessaires et utiles, d'autres fois ils sont juste un gaspillage de ressources.

un simple curseur avec une logique procédurale Vs un exemple basé sur set (T-SQL) qui assignera un indicatif régional basé sur le central téléphonique:

--Cursor
DECLARE @phoneNumber char(7)
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
   SELECT PhoneNumber FROM Customer WHERE AreaCode IS NULL
OPEN c
FETCH NEXT FROM c INTO @phoneNumber
WHILE @@FETCH_STATUS = 0 BEGIN
   DECLARE @exchange char(3), @areaCode char(3)
   SELECT @exchange = LEFT(@phoneNumber, 3)

   SELECT @areaCode = AreaCode 
   FROM AreaCode_Exchange 
   WHERE Exchange = @exchange

   IF @areaCode IS NOT NULL BEGIN
       UPDATE Customer SET AreaCode = @areaCode
       WHERE CURRENT OF c
   END
   FETCH NEXT FROM c INTO @phoneNumber
END
CLOSE c
DEALLOCATE c
END

--Set
UPDATE Customer SET
    AreaCode = AreaCode_Exchange.AreaCode
FROM Customer
JOIN AreaCode_Exchange ON
    LEFT(Customer.PhoneNumber, 3) = AreaCode_Exchange.Exchange
WHERE
    Customer.AreaCode IS NULL

vous vouliez des exemples concrets. Mon entreprise avait un curseur qui prenait plus de 40 minutes pour traiter 30 000 enregistrements (et il y avait des moments où j'avais besoin de mettre à jour plus de 200 000 enregistrements). Il a fallu 45 secondes pour faire la même tâche sans le curseur. Dans un autre cas, j'ai enlevé un curseur et envoyée le temps de traitement de plus de 24 heures à moins d'une minute. L'un était un insert utilisant la clause des valeurs au lieu d'un select et l'autre était une mise à jour qui utilisait des variables au lieu d'une jointure. Une bonne règle de le pouce est que si c'est un insert, mettre à jour, ou supprimer, vous devriez chercher une façon basée sur les ensembles pour effectuer la tâche.

curseurs ont leurs utilisations (ou le code ne serait pas leur en premier lieu), mais ils devraient être extrêmement rares lors de la recherche d'une base de données relationnelle (sauf Oracle qui est optimisé pour les utiliser). Un endroit où ils peuvent être plus rapides est quand ils font des calculs basés sur la valeur de l'enregistrement précédent (exécution des totaux). Mais même cela devrait être testé.

un autre cas limité d'utilisation d'un curseur est d'effectuer un traitement par lots. Si vous essayez de faire trop à la fois dans la mode set-based il peut verrouiller la table à d'autres utilisateurs. Si vous avez un ensemble vraiment grand, il peut être préférable de le décomposer en inserts plus petits basés sur des ensembles, mises à jour ou suppressions qui ne tiendront pas le verrou trop longtemps et puis exécuter à travers les ensembles en utilisant un curseur.

Une troisième utilisation d'un curseur est à exécuter stockée système de déclenchement par l'intermédiaire d'un groupe de valeurs d'entrée. Puisque ceci est limité à un ensemble généralement petit et que personne ne doit jouer avec le procs système, c'est une chose acceptable pour un administrateur de faire. Je ne recommande pas de faire la même chose avec un utilisateur créé stocké proc afin de traiter un grand lot et de réutiliser le code. Il est préférable d'écrire une version basée sur un jeu qui sera un meilleur performer que la performance devrait abuser de la réutilisation de code dans la plupart des cas.

je pense que la vraie réponse est, comme toutes les approches de programmation, que cela dépend de laquelle est la meilleure. En général, un langage basé sur un ensemble sera plus efficace, parce que c'est ce qu'il a été conçu pour faire. Il y a deux endroits où un curseur est un avantage:

1. vous mettez à jour un grand ensemble de données dans une base de données où le verrouillage des lignes n'est pas acceptable (pendant les heures de production peut-être). Une mise à jour basée sur un jeu a la possibilité de verrouiller une table pour plusieurs secondes (ou minutes), lorsqu'un curseur (s'il est écrit correctement) ne l'est pas. Le curseur peut se déplacer à travers les lignes de mise à jour une par une et vous n'avez pas à vous soucier d'affecter quoi que ce soit d'autre.

2. L'avantage D'utiliser SQL est que L'essentiel du travail d'optimisation est géré par le moteur de base de données dans la plupart des circonstances. Avec les moteurs db de la classe enterprise, les concepteurs ont pris des mesures draconiennes pour s'assurer que le système est efficace dans le traitement des données. L'inconvénient est que SQL est un langage basé sur des ensembles. Vous devez être capable de définir un ensemble de données à utiliser. Bien que cela semble facile, dans certaines circonstances, il ne l'est pas. Une requête peut être si complexe que les optimiseurs internes du moteur ne peuvent pas créer efficacement un chemin d'exécution, et devinez ce qui se passe... votre boîte super puissante avec 32 processeurs utilise un seul thread pour exécuter la requête parce qu'il ne sait pas faire quoi que ce soit d'autre, donc vous perdez du temps processeur sur le serveur de base de données qui en général, il n'y a qu'un seul serveur d'application par opposition à plusieurs serveurs d'application (donc, retour à la raison 1, vous rencontrez des contentions de ressources avec d'autres choses qui doivent être exécutées sur le serveur de base de données). Avec un langage basé sur les lignes (C#, PHP, JAVA etc.), vous avez plus de contrôle sur ce qui se passe. Vous pouvez récupérer un ensemble de données et le forcer à exécuter comme vous le voulez. (Séparer les données définies pour exécuter plusieurs threads, etc.). La plupart du temps, il ne va toujours pas être efficace que de l'exécuter sur le moteur de base de données, parce qu'il devra toujours accéder au moteur pour mettre à jour la ligne, mais quand vous devez faire 1000+ calculs pour mettre à jour une ligne (et disons que vous avez un million de lignes), un serveur de base de données peut commencer à avoir des problèmes.

je pense qu'il s'agit d'utiliser la base de données a été conçu pour être utilisé. Les serveurs de bases de données relationnelles sont spécialement développés et optimisés pour répondre au mieux aux questions exprimées dans la logique de jeu.

Fonctionnellement, la pénalité pour les curseurs ne varient énormément d'un produit à l'autre. Certains (la plupart?) les rdbmss sont construits au moins partiellement sur les moteurs isam. Si la question est pertinente et que le placage est assez mince, il peut être aussi efficace d'utiliser un curseur. Mais c'est une des choses que vous devriez être intimement familier avec, en termes de votre marque de sgbd, avant de l'essayer.

comme on l'a dit, la base de données est optimisée pour les opérations de set. Littéralement les ingénieurs se sont assis et débogué / accordé cette base de données pour de longues périodes de temps. Les chances que vous les optimisiez sont plutôt minces. Il ya toutes sortes de trucs amusants que vous pouvez jouer avec si vous avez un ensemble de données à travailler avec comme batching Disk lit/écrit ensemble, la mise en cache, multi-threading. Aussi certaines opérations ont élevé des frais généraux, mais si vous le faites à un tas de données à la fois le coût de chaque morceau de données est faible. Si vous travaillez seulement une rangée à la fois, beaucoup de ces méthodes et opérations ne peuvent tout simplement pas se produire.

par exemple, regardez juste la façon dont la base de données se joint. En regardant expliquer les plans, vous pouvez voir plusieurs façons de faire jointures. Très probablement avec un curseur vous allez ligne par ligne dans une table et puis sélectionnez les valeurs dont vous avez besoin d'une autre table. Fondamentalement, il est comme une boucle imbriquée que sans l'étroitesse de la boucle (qui est très probablement compilé en langage machine et super optimisé.) SQL Server a tout un tas de façons de se joindre. Si les lignes sont triées, il utilisera un type d'algorithme de fusion, si une table est petite, il peut transformer une table en une table de recherche de hachage et faire la jointure en effectuant des recherches O(1) à partir d'une table dans la table de recherche. Il y a un certain nombre de stratégies de jointure que beaucoup de SGBD ont qui vous battra en levant les valeurs d'une table dans un curseur.

il suffit de regarder l'exemple de la création d'un hash table de recherche. De construire la table est probablement des opérations m si vous rejoignez deux tables une de longueur n et une de longueur m où m est la plus petite table. Chaque recherche doit être en temps constant, donc c'est n opérations. donc, fondamentalement, l'efficacité d'une jointure de hachage est d'environ m (setup) + n (recherches). Si vous le faites vous-même et en supposant qu'il n'y a pas de recherche/index, alors pour chacune des N lignes vous devrez rechercher des enregistrements m (en moyenne cela équivaut à des recherches m/2). Donc, en gros, le niveau des opérations va de m + n (joindre un tas d'enregistrements à la fois) à m * n / 2 (Faire des recherches à travers un curseur). Les opérations sont aussi des simplifications. Selon le type de curseur, aller chercher chaque ligne d'un curseur peut être le même que faire un autre select de la première table.

les serrures vous tuent aussi. Si vous avez des curseurs sur une table, vous fermez les lignes (dans SQL server, cela est moins sévère pour les curseurs statiques et forward_only...mais la majorité du code de curseur que je vois ouvre juste un curseur sans en spécifier aucun de ces options). Si vous effectuez l'opération dans un ensemble, les lignes seront toujours verrouillées mais pour une durée moindre. Aussi l'optimiseur peut voir ce que vous faites et il peut décider qu'il est plus efficace pour verrouiller l'ensemble de la table au lieu d'un tas de lignes ou de pages. Mais si vous aller ligne par ligne, l'optimiseur a aucune idée.

l'autre chose est que j'ai entendu que dans le cas D'Oracle il est super optimisé pour faire des opérations de curseur de sorte qu'il est loin près de la même pénalité pour set basé opérations versus curseurs dans Oracle comme il est dans SQL Server. Je ne suis pas un expert Oracle donc je ne peux pas en être sûr. Mais plus d'une personne Oracle m'a dit que les curseurs sont beaucoup plus efficaces en Oracle. Donc, si vous avez sacrifié votre fils premier-né pour Oracle vous pouvez ne pas avoir à vous soucier des curseurs, consultez votre local très bien payé Oracle DBA :)

l'idée derrière la préférence pour faire le travail dans les requêtes est que le moteur de base de données peut optimiser en le reformulant. C'est aussi la raison pour laquelle vous voulez lancer expliquer sur votre requête, pour voir ce qu'est la base de données en fait, le faire. (par exemple, tirer parti des indices, des tailles de tableaux et parfois même des connaissances sur la distribution des valeurs dans les colonnes.)

cela dit, pour obtenir une bonne performance dans votre cas concret, vous devrez peut-être contourner ou enfreindre les règles.

Oh, une autre raison pourrait être les contraintes: incrémenter une colonne unique par une pourrait être acceptable si les contraintes sont vérifiées après les mises à jour, mais génère une collision si fait un par un.

jeu de base se fait en une seule opération curseur autant d'opérations que le rang du curseur

La VRAIE réponse est aller chercher une E. F. Codddes livres et de la brosse sur de l'algèbre relationnelle. Alors obtenez un bon livre sur notation Big O. Après près de deux décennies en elle, C'est, IMHO, l'une des grandes tragédies de la MIS ou CS degré moderne: très peu en fait étudier le calcul. Vous savez...le "calcul" de la partie "ordinateur"? Structured Query Language (et tous ses supersets) est simplement une application pratique de l'algèbre relationnelle. Oui, les RDBM ont gestion de la mémoire optimisée et lire / écrire, mais la même chose pourrait être dit pour les langages de procédure. Comme je l'ai lu, la question originale n'est pas sur L'IDE, le logiciel, mais plutôt sur l'efficacité d'une méthode de calcul par rapport à une autre.

même une familiarisation rapide avec la notation Big O commencera à faire la lumière sur la raison pour laquelle, lorsqu'il s'agit d'ensembles de données, l'itération est plus coûteuse qu'un énoncé déclaratif.

il suffit de dire, dans la plupart des cas, il est plus rapide/plus facile de laisser la base de données le faire pour vous.

le but de la base de données dans la vie est de stocker/extraire/manipuler des données dans des formats prédéfinis et d'être vraiment rapide. Votre VB.NET/ASP.NET le code est probablement loin d'être aussi rapide qu'un moteur de base de données dédié. En tirant parti de ce qui est d'une utilisation rationnelle des ressources.