Les instructions INSERT vs INSERT unique avec plusieurs VALEURS

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 ms.
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 performance?

Merci.

EDIT: Fixation des plans d'exécution pour les deux:
Les instructions INSERT vs INSERT unique avec plusieurs VALEURS

ces sont propres tests, rien n'est en cours d'exécution en parallèle, aucune répétition de données (chaque requête est avec des données différentes, bien sûr, pour éviter la simple mise en cache)
existe-il des déclencheurs impliquées?
J'ai converti un programme de TVP pour obtenir passé les 1000 limite sur les valeurs et l'ai un gros gain de performance. Je vais courir une comparaison.
pertinentes: simple-talk.com/sql/performance/...

InformationsquelleAutor Borka | 2011-12-26

122

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 fixe 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 de VALUES clauses et 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 en 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 il ya un changement soudain.

Que la 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 un. 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é).

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 construction dans SQL Server 2005.

Typique de trace de la pile est en dessous de
```
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.

Cet article KB indique que DeriveNormalizedGroupProperties est associé avec ce que l'on appelait le la normalisation étape de traitement de la requête de

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) [réf].

J'en ai essayé un plus expérience ( Script ), qui a été de ré-exécuter les tests originaux 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 lieu où cette information est l'effet de levier est illustré 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 la 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 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.
- Le nombre magique que vous avez est Nombredelignes / ColumnCount = 250. Modifier votre requête à utiliser seulement trois colonnes et le changement va se produire à 333. Le chiffre magique de 1000 pourrait être quelque chose comme le nombre maximum de paramètres utilisés dans un plan de mise en cache. Il coutures pour être "plus facile" pour générer un plan avec un <ParameterList> qu'une <ConstantScan><Values><Row> liste.
- D'accord. Les 250 ligne un avec 1000 valeurs obtient auto de paramétrer l'251 ligne n'est pas ainsi que semble l'être la différence. Pas sûr, mais pourquoi. Peut-être qu'il passe du temps à trier les valeurs littérales de la recherche de doublons ou de quelque chose quand il a ces.
- C'est un peu fou problème, je viens de recevoir griefed par elle. C'est une excellente réponse merci
- Voulez-vous dire que le nombre magique est Nombredelignes * ColumnCount = 1000 ?
- Oui. Lorsque le nombre total d'éléments est plus de 1000 (Nombredelignes * ColumnCount) le plan de requête modifié pour utiliser <ConstantScan><Values><Row> au lieu de <ParameterList>.
- Avez-vous déjà suivi pour voir si cette amélioration dans sql server 2014 ou 2016?
InformationsquelleAutor Martin Smith
23

Il n'est pas trop surprenant: le plan d'exécution pour le petit insert est calculé une fois, et ensuite réutilisé 1000 fois. L'analyse et l'élaboration d'un plan est rapide, car il ne dispose que de quatre valeurs à del avec. 1000 ligne plan, d'autre part, doit faire face à 4000 valeurs (ou 4000 paramètres si vous avez bien paramétré votre C# tests). Cela pourrait prendre facilement le gain de temps vous gagnez en éliminant 999 allers-retours vers SQL Server, surtout si votre réseau n'est pas trop lent.

InformationsquelleAutor dasblinkenlight
9

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

Si vous voulez accélérer les semelles, est-ce que vous avez vraiment besoin de faire est de les envelopper dans une transaction:
```
BEGIN TRAN;
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);
COMMIT TRAN;
```
À partir de C#, vous pouvez également envisager l'utilisation d'une table d'une valeur de paramètre. Exécuter plusieurs commandes en un seul lot, en les séparant par des points-virgules, est une autre approche qui aidera également.
- Re: "l'Émission de plusieurs commandes en un seul lot": qui aide un peu, mais pas beaucoup. Mais je suis certainement d'accord avec les deux autres options de l'emballage dans une TRANSACTION (ne TRANS en fait de travailler ou doit-il seulement être TRAN?) ou à l'aide d'une TVP.
InformationsquelleAutor RickNZ
1

J'ai couru dans une situation similaire à essayer de convertir un tableau avec plusieurs 100k lignes avec un programme C++ (MFC/ODBC).

Puisque cette opération a pris un temps très long, j'ai pensé que le regroupement de plusieurs insère dans un (jusqu'à 1000 en raison de MSSQL limitations). Je pense que beaucoup de simples instructions insert, serait de créer une surcharge de semblable à ce qui est décrit ici.

Cependant, il s'avère que la conversion a eu effectivement un peu plus longue:
```
        Method 1       Method 2     Method 3 
Single Insert  Multi Insert Joined Inserts
Rows    1000           1000         1000
Insert  390 ms         765 ms       270 ms
per Row 0.390 ms       0.765 ms     0.27 ms
```
Donc, 1000 appels seul à CDatabase::ExecuteSql chacun avec une seule instruction INSERT (méthode 1) sont environ deux fois plus rapide qu'un seul appel à CDatabase::ExecuteSql avec un multi-ligne d'instruction INSERT avec 1000 de la valeur des n-uplets (méthode 2).

Mise à jour: Alors, la prochaine chose que j'ai essayé était de regrouper 1000 séparer les états d'INSERTION dans une chaîne unique et demander au serveur d'exécuter (méthode 3). Il s'avère que c'est même un peu plus rapide que la méthode 1.

Edit: je suis à l'aide de Microsoft SQL Server Express Edition (64-bit) v10.0.2531.0

InformationsquelleAutor uceumern

Vous devez vous connecter pour publier un commentaire.