Quali sono i buoni principi per capire quale è più veloce e perché? Penserei che Microsoft ottimizzerebbe per rendere insert into, altrettanto veloce, per una programmazione attenta.
I principi che cerco di seguire quando analizzo qualcosa come questa domanda sono:
- Evitare di fare supposizioni inutili.
- Leggere la documentazione ufficiale.
- Testare il carico di lavoro. La quantità di test dipende da quanto veloce ho bisogno che il codice sia.
Sono a conoscenza di due pezzi di documentazione che affrontano la tua domanda. Il primo è un post sul blog che dice che SELECT INTO per le tabelle temporanee ha un comportamento diverso per le scritture desiderose a partire da SQL Server 2014. Questo è per progettazione. Quindi non credo che sia corretto dire che la differenza è minima per le tabelle piccole. Semmai, l’ottimizzazione descritta nel post del blog sembra progettata per le tabelle più piccole:
Il cambiamento in SQL Server 2014 è quello di rilassare la necessità di eseguire il flush di queste pagine, così rapidamente, nei file di dati TEMPDB. Quando si fa una select in … #tmp … o si crea un indice WITH SORT IN TEMPDB il SQL Server ora riconosce che questa può essere un’operazione di breve durata. Le pagine associate a tale operazione possono essere create, caricate, interrogate e rilasciate in una finestra di tempo molto piccola.
Per esempio: Si potrebbe avere una stored procedure che viene eseguita in 8ms. In quella stored procedure si seleziona in … #tmp … poi si usa il #tmp e lo si rilascia al completamento della stored procedure.
Prima della modifica di SQL Server 2014 la select in potrebbe aver scritto tutte le pagine accumulate su disco. Il comportamento di SQL Server 2014, eager write, non costringe più queste pagine sul disco così rapidamente come le versioni precedenti. Questo comportamento permette alle pagine di essere memorizzate in RAM (buffer pool), interrogate e la tabella abbandonata (rimossa dal buffer pool e restituita all’elenco libero) senza mai andare su disco finché la memoria è disponibile. Evitando l’I/O fisico quando possibile, le prestazioni del TEMPDB, l’operazione in blocco è significativamente aumentata e riduce anche l’impatto sulle risorse del percorso di I/O.
Il secondo pezzo di documentazione spiega che l’inserimento parallelo in tabelle temporanee con INSERT INTO ... SELECT è disponibile senza un TABLOCK suggerimento in SQL Server 2016 ma richiede un TABLOCK suggerimento in SP1 e nelle versioni future.
Il problema viene risolto per la prima volta in SQL Server 2016 Service Pack 1 . Dopo aver applicato SQL Server 2016 SP1, le INSERT parallele in INSERT..SELECT alle tabelle temporanee locali sono disabilitate per impostazione predefinita che riduce la contesa sulla pagina PFS e migliora le prestazioni complessive per il carico di lavoro concorrente. Se si desiderano INSERT paralleli a tabelle temporanee locali, gli utenti dovrebbero utilizzare il suggerimento TABLOCK durante l’inserimento nella tabella temporanea locale.
Tornando alla tua affermazione originale, non puoi dedurre logicamente quale delle due sarà più veloce. Ciò che è più veloce dipende da come Microsoft ha progettato il software e dalle caratteristiche del vostro carico di lavoro. Fare congetture sulla quantità di tempo necessaria per creare le definizioni delle colonne non è utile. I test sono utili. Se i vostri test suggeriscono che SELECT INTO è più veloce di quello. Per quello che vale, lavoro anche sul caricamento del data warehouse con un occhio attento alle prestazioni e non ho visto la differenza tra i due approcci essere qualcosa di cui valga la pena preoccuparsi.