Les performances de SQL Server SELECT INTO vs INSERT INTO pour les tables temporaires
Par : Jignesh Raiyani | Mis à jour : 2017-01-04 | Commentaires (9) | Connexe : Plus de > T-SQL
Webinaire gratuit MSSQLTips : Meilleures pratiques de développement pour SQL Server
Assistez à ce webinaire pour découvrir les meilleures pratiques de développement pour SQL Server. Andy Warren partagera ses nombreuses années d’expérience pour donner quelques conseils sur ce qui a le mieux fonctionné pour lui et sur la façon dont vous pouvez utiliser certaines de ces connaissances.
Problème
SQL Server inclut du code SELECT…INTO et INSERT…INTO pour insérer des données dans des tables temporaires. Pouvez-vous fournir quelques échantillonset indiquer quelle option est la plus performante ?
Solution
Le serveur SQL inclut les deux options pour les tables temporaires :
- Table temporaire locale
- Table temporaire globale
Vous devez ajouter le préfixe ‘#’ pour les tables temporaires locales et ‘##’ pour les tables temporaires globales. Ces objets seront créés dans la base de données système TempDB. Les tables temporaires sont stockées dans la base de données TempDB avec un nom d’objet unique et créées au niveau de la connexion ou de la session. Ces tables ne sont visibles et accessibles qu’au sein de la session. Nous pouvons supprimer la table temporaire à l’aide de la commande DROP TABLE ou la table temporaire sera supprimée automatiquement lorsque la session sera déconnectée. En outre, SQL Servermaintient des statistiques pour les tables temporaires.
La création et la suppression des tables temporaires nécessitent l’accès et la modification des pages de carte d’allocation TempDB (IAM, SGAM et PES). Les tables temporaires sont mises en cache dans le pool de mémoire tampon par rapport aux tables permanentes sur disque. Avec la mise en cache des tables temporaires, SQL Server n’abandonne pas la table physiquement, mais la tronque et maintient les pages IAM et de données. Lorsque la table est créée ultérieurement, SQL Serverréutilise les pages antérieures, ce qui réduit le nombre de modifications de pages nécessaires.
Les tables temporaires peuvent être créées de deux manières :
- CREATE TABLE
- SELECT INTO
Généralement, les performances des deux options sont similaires pour une petite quantité de données. Les données sont insérées rapidement dans la table temporaire, mais si la quantité de données est importante, les performances des requêtes peuvent être médiocres. Cela se produit généralement avec les tables temporaires lorsque nous insérons un grand nombre de lignes.
Exemple d’INSERT INTO du serveur SQL
SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLCREATE TABLE #list_to_table(Id BIGINT)SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML) INSERT INTO #list_to_tableSELECT f.x.value('.', 'BIGINT') AS IdFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table
Les temps de parse et de compilation du serveur SQL :
CPU time = 0 ms, elapsed time = 2 ms.
Table '#list_to_table____000000000015'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
Lorsqu’il y a une grande quantité de valeurs séparées par des virgules comme paramètre d’entrée,le code peut être retardé de quelques secondes pendant l’insertion des données dans le temptable.
Exemple de SELECT INTO du serveur SQL
La solution alternative que nous pouvons utiliser est la commande SELECT…INTO qui est généralement plus performante que la commande INSERT…SELECT.
SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLSELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)SELECT f.x.value('.', 'BIGINT') AS IdINTO #list_to_tableFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table
SQL Server parse and compile time:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
SQL Server INSERT…SELECT vs….SELECT vs. SELECT…INTO avec un ensemble de données plus important
Les exemples ci-dessus sont très petits, alors utilisons l’exemple ci-dessous pour voir comment celaperforme avec une grande quantité de données. Ce test aura plus d’un million de lignes.
Serveur SQL INSERT…. SELECT
CREATE TABLE #EMPLOYEEEEE (Emp_id BIGINT, f_name NVARCHAR(100), l_name NVARCHAR(100), Email NVARCHAR(100), is_active BIT) INSERT INTO #EMPLOYEESELECT Emp_id, f_name, l_name, Email, is_active FROM employee
SQL Server parse and compile time:
SQL Server Execution Times: CPU time = 1407 ms, elapsed time = 3241 ms.(1727264 row(s) affected)
SQL Server SELECT…INTO
SELECT Emp_id, f_name, l_name, Email, is_activeeINTO #EMPLOYEEFROM employee
Temps de parse et de compilation du serveur SQL:
SQL Server Execution Times: CPU time = 1499 ms, elapsed time = 489 ms.(1727264 row(s) affected)
Résultats et analyse
Comme nous pouvons le constater, le SELECT……INTO était considérablement plus rapide 489ms par rapport à3241ms.
La commande INSERT…INTO réutilisera les pages de données qui sont créées dans le cache pour les opérationsinsert/update/delete. Elle tronquera également la table lorsqu’elle sera abandonnée.La commande SELECT…INTO créera de nouvelles pages pour la création de la table, de manière similaire aux tables régulières, et les supprimera physiquement lorsque la table temporaire sera abandonnée.
Prochaines étapes
- Gardez ces considérations de performance à l’esprit lorsque vous développez votre code ou que vous devez régler un code qui ne fonctionne pas de manière optimale.
- Testez des scénarios supplémentaires dans votre environnement pour déterminer quelle technique de codage sera la plus performante.
- Ayez toujours un esprit ouvert aux différentes techniques de codage, testez chaque technique et laissez les performances dicter la réponse.
Dernière mise à jour : 2017-01-04
A propos de l’auteur
. auteur
Jignesh Raiyani est un développeur/DBA SQL Server avec une expérience dans la conception et le développement de procédures T-.Procédures SQL et l’optimisation des performances des requêtes.Voir tous mes conseils
- Plus de conseils pour les développeurs de bases de données…
.