Quels sont les bons principes pour déterminer lequel est le plus rapide et pourquoi ? Je penserais que Microsoft optimiserait pour rendre insert into , tout aussi rapide, pour une programmation soignée.
Les principes que j’essaie de suivre lorsque j’analyse quelque chose comme cette question sont :
- Éviter de faire des hypothèses inutiles.
- Lire la documentation officielle.
- Tester la charge de travail. La quantité de tests dépend de la rapidité dont j’ai besoin pour le code.
Je connais deux éléments de documentation qui répondent à votre question. Le premier est un article de blog disant que SELECT INTO pour les tables temporaires a un comportement différent pour les écritures avides à partir de SQL Server 2014. C’est une question de conception. Je ne pense donc pas qu’il soit correct de dire que la différence est minime pour les petites tables. Si quoi que ce soit, l’optimisation décrite dans le billet de blog semble conçue pour les petites tables :
Le changement dans SQL Server 2014 est de relaxer le besoin de flush ces pages, aussi rapidement, vers les fichiers de données TEMPDB. Lors d’un select into … #tmp … ou d’un create index WITH SORT IN TEMPDB, le serveur SQL reconnaît désormais que cette opération peut être de courte durée. Les pages associées à une telle opération peuvent être créées, chargées, interrogées et libérées dans une très petite fenêtre de temps.
Par exemple : Vous pourriez avoir une procédure stockée qui s’exécute en 8ms. Dans cette procédure stockée, vous sélectionnez dans … #tmp … puis vous utilisez le #tmp et le déposez lorsque la procédure stockée se termine.
Avant le changement de SQL Server 2014, le select into pouvait écrire toutes les pages accumulées sur le disque. Le SQL Server 2014, comportement eager write, ne force plus ces pages sur le disque aussi rapidement que les versions précédentes. Ce comportement permet aux pages d’être stockées en RAM (pool de mémoire tampon), d’être interrogées et la table abandonnée (retirée du pool de mémoire tampon et renvoyée à la liste libre) sans jamais aller sur le disque tant que la mémoire est disponible. En évitant les entrées/sorties physiques lorsque cela est possible, les performances de la TEMPDB, opération en vrac, sont considérablement améliorées et cela réduit également l’impact sur les ressources du chemin d’entrée/sortie.
Le deuxième élément de documentation explique que l’insertion parallèle dans les tables temp avec INSERT INTO ... SELECT est disponible sans TABLOCK hint dans SQL Server 2016 mais nécessite un TABLOCK hint dans SP1 et dans les versions futures.
Le problème est d’abord corrigé dans le Service Pack 1 de SQL Server 2016 . Après avoir appliqué SQL Server 2016 SP1, les INSERTS parallèles dans INSERT..SELECT vers des tables temporaires locales sont désactivés par défaut, ce qui réduit la contention sur la page PFS et améliore les performances globales pour une charge de travail simultanée. Si des INSERTS parallèles vers des tables temporaires locales sont souhaités, les utilisateurs doivent utiliser l’indice TABLOCK pendant l’insertion dans la table temporaire locale.
Pour revenir à votre déclaration initiale, vous ne pouvez pas déduire logiquement laquelle des deux sera plus rapide. Ce qui est plus rapide dépend de la façon dont Microsoft a conçu le logiciel et des caractéristiques de votre charge de travail. Faire des suppositions sur le temps nécessaire pour créer les définitions de colonnes n’est tout simplement pas utile. Les tests sont utiles. Si vos tests suggèrent que SELECT INTO est plus rapide, alors faites-le. Pour ce que ça vaut, je travaille également sur le chargement d’entrepôts de données avec un œil attentif aux performances et je n’ai pas vu la différence entre les deux approches être quelque chose qui vaut la peine de s’inquiéter.