Plusieurs instructions INSERT vs seul INSERT ayant plusieurs valeurs

Question

Je suis en exécutant une comparaison des performances entre l'utilisation de 1000 instructions INSERT:

INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('6f3f7257-a3d8-4a78-b2e1-c9b767cfe1c1', 'First 0', 'Last 0', 0)
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('32023304-2e55-4768-8e52-1ba589b82c8b', 'First 1', 'Last 1', 1)
...
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('f34d95a7-90b1-4558-be10-6ceacd53e4c4', 'First 999', 'Last 999', 999)

..rapport à l'aide de la seule instruction INSERT avec 1000 valeurs:

INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
VALUES 
('db72b358-e9b5-4101-8d11-7d7ea3a0ae7d', 'First 0', 'Last 0', 0),
('6a4874ab-b6a3-4aa4-8ed4-a167ab21dd3d', 'First 1', 'Last 1', 1),
...
('9d7f2a58-7e57-4ed4-ba54-5e9e335fb56c', 'First 999', 'Last 999', 999)

À ma grande surprise, les résultats sont à l'opposé de ce que je pensais:

• 1000 instructions INSERT: 290 msec.
• 1 instruction INSERT avec 1000 VALEURS: 2800 msec.

Le test est exécuté directement dans MSSQL Management Studio avec générateur de profils SQL Server est utilisé pour la mesure (et j'ai obtenu des résultats similaires en cours d'exécution à partir de code C# à l'aide de SqlClient, ce qui est encore plus surprenant compte tenu de tous les DAL couches allers-retours)

Cela peut-il être raisonnable ou d'une autre manière de l'expliquer? Comment un soi-disant plus rapide des résultats de la méthode en 10 fois (!) pire rendement?

Je vous remercie.

EDIT: Fixation des plans d'exécution pour les deux:

Answer 1

Plus: SQL Server 2012 montre une certaine amélioration de la performance dans ce domaine, mais ne semble pas s'attaquer aux questions spécifiques indiquées ci-dessous. Cette devrait apparemment être corrigé dans la prochaine version majeure après SQL Server 2012!

Votre plan montre le seul inserts sont paramétrés à l'aide de procédures (éventuellement auto paramétrable) afin d'analyser/temps de compilation pour ceux-ci devraient être minimes.

Je pensais regarder cela d'un peu plus si donc mis en place une boucle (script) et tenté d'ajuster le nombre d' VALUES de clauses et de l'enregistrement de la compilation.

J'ai ensuite divisé le temps de compilation par le nombre de lignes pour obtenir la moyenne de la compilation par la clause. Les résultats sont ci-dessous

Jusqu'à 250 VALUES clauses présentes au moment de la compilation / nombre de clauses a une légère tendance à la hausse, mais rien de trop dramatique.

Mais ensuite, il ya un changement soudain.

Cette section des données est indiqué ci-dessous.

+------+----------------+-------------+---------------+---------------+
| Rows | CachedPlanSize | CompileTime | CompileMemory | Duration/Rows |
+------+----------------+-------------+---------------+---------------+
|  245 |            528 |          41 |          2400 | 0.167346939   |
|  246 |            528 |          40 |          2416 | 0.162601626   |
|  247 |            528 |          38 |          2416 | 0.153846154   |
|  248 |            528 |          39 |          2432 | 0.157258065   |
|  249 |            528 |          39 |          2432 | 0.156626506   |
|  250 |            528 |          40 |          2448 | 0.16          |
|  251 |            400 |         273 |          3488 | 1.087649402   |
|  252 |            400 |         274 |          3496 | 1.087301587   |
|  253 |            400 |         282 |          3520 | 1.114624506   |
|  254 |            408 |         279 |          3544 | 1.098425197   |
|  255 |            408 |         290 |          3552 | 1.137254902   |
+------+----------------+-------------+---------------+---------------+

Le plan de mise en cache de taille qui a été de plus en plus de façon linéaire tombe tout d'un coup, mais compile-time augmente 7 fois et CompileMemory tire vers le haut. C'est le point de coupure entre le plan d'une auto paramétrées, un (1 000 paramètres) à une non paramétrées. Par la suite, il semble pour obtenir linéairement de moins en moins efficace (en termes de nombre de valeur clauses traitées dans un temps donné).

Je ne sais pas pourquoi cela devrait être. On peut penser que lorsqu'il est en train de compiler un plan pour certaines valeurs littérales il doit effectuer certaines activités qui ne sont pas à l'échelle linéaire (comme le tri).

Il ne semble pas affecter la taille du cache de plan de requête lorsque j'ai essayé une requête entièrement composé de lignes en double et ne nuit ni à l'ordre de la sortie de la table de constantes (et que vous insérez dans un tas de temps consacré au tri serait inutile de toute façon, même si c'était le cas).

En outre, si un index cluster est ajoutée à la table, le plan montre encore un tri explicite étape de sorte qu'il ne semble pas être le tri au moment de la compilation pour éviter un tri au moment de l'exécution.

J'ai essayé de regarder ce dans un débogueur, mais les symboles publics pour ma version de SQL Server 2008 ne semblent pas être disponibles si au lieu de cela, j'ai dû regarder l'équivalent UNION ALL de la construction dans SQL Server 2005.

Typique de trace de la pile est en dessous

sqlservr.exe!FastDBCSToUnicode()  + 0xac bytes  
sqlservr.exe!nls_sqlhilo()  + 0x35 bytes    
sqlservr.exe!CXVariant::CmpCompareStr()  + 0x2b bytes   
sqlservr.exe!CXVariantPerformCompare<167,167>::Compare()  + 0x18 bytes  
sqlservr.exe!CXVariant::CmpCompare()  + 0x11f67d bytes  
sqlservr.exe!CConstraintItvl::PcnstrItvlUnion()  + 0xe2 bytes   
sqlservr.exe!CConstraintProp::PcnstrUnion()  + 0x35e bytes  
sqlservr.exe!CLogOp_BaseSetOp::PcnstrDerive()  + 0x11a bytes    
sqlservr.exe!CLogOpArg::PcnstrDeriveHandler()  + 0x18f bytes    
sqlservr.exe!CLogOpArg::DeriveGroupProperties()  + 0xa9 bytes   
sqlservr.exe!COpArg::DeriveNormalizedGroupProperties()  + 0x40 bytes    
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x18a bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!CQuery::PqoBuild()  + 0x3cb bytes  
sqlservr.exe!CStmtQuery::InitQuery()  + 0x167 bytes 
sqlservr.exe!CStmtDML::InitNormal()  + 0xf0 bytes   
sqlservr.exe!CStmtDML::Init()  + 0x1b bytes 
sqlservr.exe!CCompPlan::FCompileStep()  + 0x176 bytes   
sqlservr.exe!CSQLSource::FCompile()  + 0x741 bytes  
sqlservr.exe!CSQLSource::FCompWrapper()  + 0x922be bytes    
sqlservr.exe!CSQLSource::Transform()  + 0x120431 bytes  
sqlservr.exe!CSQLSource::Compile()  + 0x2ff bytes   

Donc, la sortie de noms dans la trace de la pile, il semble passer beaucoup de temps à comparer des chaînes de caractères.

Le présent article indique que DeriveNormalizedGroupProperties est associé avec ce que l'on appelait la normalisation étape de traitement de la requête

Cette étape est maintenant appelé liaison ou algebrizing et il prend l'expression arbre d'analyse de la sortie de la précédente analyser la scène et les sorties d'un algebrized expression de l'arbre (processeur de requête de l'arbre) pour aller de l'avant à l'optimisation (trivial plan d'optimisation dans ce cas) [ref].

J'en ai essayé un plus expérience (Script) qui a été ré-exécuter l'analyse d'origine mais en regardant les trois cas différents.

1. Les Nom et prénom des Chaînes de caractères de longueur de 10 caractères avec pas de doublons.
2. Les Nom et prénom des Chaînes de caractères de longueur 50 caractères, sans doublons.
3. Les Nom et prénom des Chaînes de caractères de longueur de 10 caractères avec tous les doublons.

On voit clairement que plus les cordes, la pire des choses et qu'à l'inverse, les plus de doublons, mieux les choses se. Comme mentionné précédemment, les doublons ne pas affecter le plan de mise en cache taille, donc je présume qu'il doit y avoir un processus d'identification en double lors de la construction de la algebrized expression de l'arbre lui-même.

Modifier

Un endroit où cette information est mise à contribution est montré par @Lieven ici

SELECT * 
FROM (VALUES ('Lieven1', 1),
             ('Lieven2', 2),
             ('Lieven3', 3))Test (name, ID)
ORDER BY name, 1/ (ID - ID) 

Parce qu'au moment de la compilation, il peut déterminer que l' Name colonne a pas de doublons, il saute de la commande par le secondaire 1/ (ID - ID) expression au moment de l'exécution (le genre dans le plan n'a qu'un seul ORDER BY de la colonne) et aucune erreur de division par zéro est soulevée. Si des doublons sont ajoutés à la table, alors l'opérateur de tri montre deux ordres de colonnes et l'erreur est générée.

Answer 2

Il n’est pas trop surprenant : le plan d’exécution pour l’insert minuscule est calculé une fois et ensuite réutilisé 1000 fois. L’analyse et la préparation du plan sont rapide, parce qu’il n’a que quatre valeurs del avec. Un plan de 1000 lignes, en revanche, doit aborder avec 4000 valeurs (ou 4000 paramètres si vous paramétré vos tests c#). Cela pourrait facilement dévorer les gains de temps, que vous gagnez en éliminant 999 allers et retours vers SQL Server, en particulier si votre réseau n’est pas trop lent.

Answer 3

La question a probablement à voir avec le temps qu'il faut pour compiler la requête.

Si vous souhaitez accélérer les insertions, ce que vous devez vraiment faire est de les envelopper dans une transaction :

Dans c#, vous pouvez également envisager à l’aide d’un paramètre de tableau d’une valeur. Émettre plusieurs commandes en un seul lot, séparez-les par des points-virgules, est une autre approche qui aidera aussi.