Linee guida per l'ottimizzazione dell'importazione di massa
Data aggiornamento: 14 aprile 2006
In questo argomento sono disponibili linee guida per ottimizzare le prestazioni in diversi scenari di importazione di massa:
Importazione di dati da un unico client o flusso in una tabella vuota.
Importazione di dati da un unico client o flusso in una tabella parzialmente popolata, non vuota.
[!NOTA] L'importazione di dati in una tabella non vuota è definita importazione di massa incrementale. In caso di importazione di massa incrementale è essenziale stabilire se rimuovere gli indici prima dell'operazione.
Importazione di dati in parallelo al blocco a livello di tabella da più client o flussi.
Copia di dati tra istanze di Microsoft SQL Server.
In questo argomento è inoltre incluso un riepilogo dei blocchi e delle registrazioni a livello di tabella durante le operazioni di importazione di massa.
Importazione di dati da un unico client o flusso in una tabella vuota
Quando si importano dati in una tabella vuota da un unico client o flusso, è consigliabile attenersi alla procedura riportata di seguito.
Specificare il qualificatore TABLOCK, disponibile come hint o opzione in tutti e tre i metodi di importazione di massa. Se si specifica TABLOCK, il blocco a livello di tabella verrà applicato per l'intera durata dell'operazione di massa, eliminando l'overhead causato dal blocco di singole righe. Per ulteriori informazioni, vedere Controllo della modalità di blocco per l'importazione di massa.
Ridurre al minimo la registrazione. Per ulteriori informazioni, vedere Prerequisiti per la registrazione minima nell'importazione di massa.
Gestire gli indici come illustrato di seguito.
Quando si utilizza bcp, BULK INSERT o INSERT ... SELECT * FROM OPENROWSET(BULK...), se la tabella è vuota e dispone di un indice cluster e i dati nel file di dati sono ordinati in modo corrispondente alle colonne chiave dell'indice cluster, eseguire le operazioni seguenti:- Eseguire l'importazione di massa dei dati con l'indice cluster già a disposizione.
- Specificare l'hint ORDER, oltre all'hint TABLOCK. Per ulteriori informazioni, vedere Controllo dell'ordinamento durante l'importazione di massa di dati.
Nel caso di una tabella vuota, questa soluzione risulta significativamente più rapida rispetto alla creazione dell'indice cluster dopo l'importazione dei dati, poiché consente di eliminare il passaggio relativo all'ordinamento.
[!NOTA] Se nella tabella non vuota sono disponibili indici, viene eseguita la registrazione completa delle importazioni di massa, anche se si utilizza il modello di recupero con registrazione minima delle transazioni di massa. Per decidere se rimuovere gli indici, valutare se i vantaggi derivanti dall'importazione di massa in una tabella priva di indici siano superiori al costo della rimozione e rigenerazione degli indici.
Se si esegue l'importazione di massa di dati in una tabella vuota con indici e si specificano le dimensioni del batch, la tabella risulterà non vuota dopo il primo batch. A partire dal secondo batch, verrà eseguita la registrazione completa dei dati. Nel caso delle tabelle vuote con indici, prendere in considerazione l'esecuzione dell'importazione di massa in un unico batch.
[!NOTA] Quando non si specifica la dimensione del batch, per impostazione predefinita in Query Optimizer di SQL Server viene utilizzata una dimensione predefinita come dimensione del file di dati. Per ottimizzare le prestazioni, è possibile utilizzare il qualificatore ROWS_PER_BATCH o KILOBYTES_PER_BATCH come hint per Query Optimizer in merito al numero approssimativo di righe disponibili nel file di dati. Per ulteriori informazioni, vedere Managing Bulk Copy Batch Sizes.
In genere l'importazione di massa di dati in una tabella priva di indici risulta più veloce che una tabella con indici. Se quindi in una tabella vuota sono presenti indici, è consigliabile rimuoverli prima di importare dati nella tabella e rigenerarli successivamente. Se i dati non vengono ordinati in base a una colonna chiave cluster e se la tabella è vuota, rimuovere tutti gli indici, importare i dati e quindi creare nuovi indici.
Importazione di dati da un unico client o flusso in una tabella non vuota
L'importazione di dati in una tabella che contiene già dati, ovvero una tabella non vuota, viene definita importazione di massa incrementale. In caso di importazione di massa incrementale è essenziale stabilire se rimuovere gli indici prima dell'operazione. Sono disponibili due opzioni. È possibile mantenere gli indici o eliminarli e ricrearli in seguito.
Quando si importano dati da un unico client o flusso in una tabella non vuota, la decisione in merito al mantenimento degli indici dipende dalla quantità di nuovi dati importati rispetto alla quantità di dati esistenti nella tabella:
- Se la quantità di dati importati è ridotta rispetto alla quantità dei dati esistenti, la rimozione e rigenerazione degli indici potrebbe risultare controproducente, poiché è probabile che il tempo necessario per la rigenerazione degli indici sia superiore rispetto al tempo risparmiato durante l'operazione di massa.
- Se invece si esegue l'importazione di una quantità relativamente elevata di nuovi dati, la rimozione degli indici dalla tabella prima dell'esecuzione dell'operazione di massa può consentire di ottimizzare le prestazioni, senza comportare incrementi significativi nel tempo necessario per l'indicizzazione.
Nella tabella seguente viene indicata la quantità minima di nuovi dati che deve essere presente in una tabella per giustificare la rimozione degli indici. Tale quantità minima è proporzionata alla quantità totale dei dati nella tabella e dipende dai tipi e dalle combinazioni degli indici. Se i nuovi dati superano la percentuale suggerita per un determinato tipo di indice o di raggruppamento di indici, prendere in considerazione la rimozione degli indici prima di un'operazione di massa e la rigenerazione degli indici in un secondo momento. Questi numeri sono sensibili allo schema dei dati esistenti e dei dati da caricare. Di conseguenza, i numeri vengono forniti esclusivamente come linea guida generale.
| Indici | Quantità relativa di nuovi dati |
|---|---|
Solo indice cluster |
30% |
Indice cluster e un indice non cluster |
25% |
Indice cluster e due indici non cluster |
25% |
Un solo indice non cluster |
100% |
Due indici non cluster |
60% |
Importazione di dati in parallelo con il blocco a livello di tabella da più client o flussi
Se SQL Server è in esecuzione in un computer con più processori ed è possibile suddividere in file di dati distinti i dati di cui eseguire l'importazione di massa in una tabella, è possibile ottimizzare le prestazioni tramite l'importazione parallela di dati nella tabella da più client. Quando si esegue l'importazione di massa da più client in una tabella, è necessario che ogni client disponga del proprio file di dati di input.
Per l'importazione di dati in una tabella da più client tenere presente quanto segue:
È possibile che più flussi di importazione di massa si blocchino a vicenda.
Per evitare tale problema, in SQL Server è disponibile un blocco interno speciale, denominato blocco aggiornamenti di massa. Per ottenere un blocco aggiornamenti di massa, è necessario specificare l'opzione TABLOCK con ogni flusso di importazione di massa, senza bloccare altri flussi di importazione di massa. Questa soluzione consente di evitare conflitti di accesso alla tabella tra i client. Il blocco aggiornamenti di massa è tuttavia disponibile solo in una tabella priva di indice, vuota o non vuota. Se si specifica TABLOCK su una tabella con indici, l'importazione di massa parallela non è possibile. Per ulteriori informazioni, vedere Controllo della modalità di blocco per l'importazione di massa.
Se nella tabella sono presenti indici, è possibile avvalersi del blocco aggiornamenti di massa, rimuovendo tutti gli indici prima di eseguire l'importazione di massa dei dati. È quindi possibile eseguire l'importazione di massa dei dati in parallelo tramite TABLOCK e infine rigenerare l'indice o gli indici. Si noti inoltre che se nella tabella non vuota sono disponibili indici, viene eseguita la registrazione completa delle importazioni di massa, anche se si utilizza il modello di recupero con registrazione minima delle transazioni di massa. Per decidere se rimuovere gli indici, valutare se i vantaggi derivanti dall'importazione di massa in una tabella priva di indici siano superiori al costo della rimozione e rigenerazione degli indici.[!NOTA] Se si rimuovono indici secondari, valutare se rigenerarli in parallelo, creando ogni indice secondario da un client distinto.
Per evitare di rimuovere e rigenerare gli indici, è possibile eseguire un'importazione parallela, senza specificare l'hint TABLOCK. In tale caso, è tuttavia possibile che i diversi flussi di importazione di massa si blocchino a vicenda. Non sono inoltre disponibili le ottimizzazioni relative alla registrazione di massa. Per ridurre al minimo i blocchi, è possibile specificare una dimensione inferiore del batch e utilizzare l'hint ORDER per eliminare il passaggio relativo all'ordinamento durante l'operazione di importazione di massa.
I dati devono essere suddivisi in più file di input, uno per ogni client. Per un utilizzo efficiente della CPU, è consigliabile che le dimensioni dei file di dati siano analoghe.
Per ulteriori informazioni, vedere Importazione di dati in parallelo tramite il blocco a livello di tabella.
Blocchi e registrazioni a livello di tabella durante l'importazione di massa
Nella tabella seguente è disponibile un riepilogo delle modalità con cui vengono determinati i tipi di blocchi dallo schema della tabella durante un'operazione di importazione di massa. Nella tabella seguente viene inoltre indicato se la tabella è vuota, se l'opzione TABLOCK è impostata per l'operazione e quale tipo di registrazione si verifica se il database utilizza il modello di recupero con registrazione minima delle transazioni di massa.
[!NOTA] Dopo il primo batch riuscito, la tabella non è più vuota.
| Tabella di destinazione dell'importazione di massa | La tabella è vuota? | L'opzione TABLOCK è impostata? | Blocchi | Registrazione con i modelli di recupero con registrazione minima delle transazioni di massa e con registrazione minima |
|---|---|---|---|---|
Heap |
Sì |
Sì |
BU-Tab |
Registrazione di massa |
Heap |
Sì |
No |
IX-Tab |
Registrazione completa |
Heap |
No |
Sì |
BU-tab |
Registrazione di massa |
Heap |
No |
No |
IX-Tab |
Registrazione completa |
Heap con un indice non cluster |
Sì |
Sì |
SCH-M |
Registrazione di massa |
Heap con un indice non cluster |
Sì |
No |
IX-Tab |
Registrazione completa |
Heap con un indice non cluster |
No |
Sì |
SCH-M |
|
Heap con un indice non cluster |
No |
No |
IX-Tab |
Registrazione completa |
Indice cluster |
Sì |
Sì |
SCH-M |
Registrazione di massa |
Indice cluster |
Sì |
No |
IX-Tab |
Registrazione completa |
Indice cluster |
No |
Sì |
X-TAB |
Registrazione completa |
Indice cluster |
No |
No |
IX-Tab |
Registrazione completa |
Copia di dati tra istanze di SQL Server
Per eseguire la copia di massa di dati da un'istanza di SQL Server a un'altra, utilizzare bcp per esportare i dati della tabella in un file di dati. Scegliere quindi uno dei metodi di importazione di massa disponibili per importare i dati dal file alla tabella. Eseguire le operazioni di esportazione e importazione di massa utilizzando il formato nativo o Unicode nativo.
[!NOTA] Per ulteriori informazioni su tali formati, vedere Utilizzo del formato nativo per l'importazione o l'esportazione di dati e Utilizzo del formato Unicode nativo per importare o esportare dati.
Se la tabella di origine dispone di un indice cluster o si desidera eseguire un'importazione di massa di dati in una tabella con indice cluster:
- Eseguire l'esportazione di massa dei dati dalla tabella di origine utilizzando bcp con l'opzione query per un'istruzione SELECT e specificando una clausola ORDER BY appropriata per creare un file di dati ordinato. Per ulteriori informazioni, vedere Utilità bcp.
- Quando si esegue l'importazione di massa dei dati in SQL Server. Utilizzare il qualificatore ORDER, supportato solo da bcp e BULK INSERT. Per ulteriori informazioni, vedere Controllo dell'ordinamento durante l'importazione di massa di dati.
Per ulteriori informazioni, vedere Copia di dati tra server.
Vedere anche
Riferimento
Oggetto Databases di SQL Server
Concetti
Eliminazione di indici
Ottimizzazione delle prestazioni dell'importazione di massa
Altre risorse
Utilità bcp
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Guida in linea e informazioni
Cronologia modifiche
| Versione | Cronologia |
|---|---|
14 aprile 2006 |
|