Gestione dei batch per l'importazione bulk
In questa sezione vengono illustrate le modalità di gestione per la determinazione delle dimensioni dei batch in un'operazione di importazione bulk. Per impostazione predefinita, tutte le righe di un file di dati vengono importate come un singolo batch di dimensioni sconosciute in un'unica transazione. In questo caso, se l'importazione viene interrotta a causa di un errore prima del completamento, viene eseguito il rollback dell'intera transazione e nella tabella di destinazione non vengono aggiunti dati. L'operazione non riuscita deve essere quindi riavviata dall'inizio del file di dati.
L'importazione di un file di dati di grandi dimensioni in un singolo batch può risultare problematica, pertanto bcp e BULK INSERT consentono di importare i dati in una serie di batch, ognuno dei quali costituisce una parte del file di dati. Ogni batch viene importato e registrato in una transazione separata e, dopo il commit di una transazione, viene eseguito il commit delle righe importate dalla transazione. Se si verifica un errore nell'operazione, viene eseguito il rollback solo delle righe importate dal batch corrente ed è possibile riprendere l'importazione dei dati dal batch interrotto anziché dall'inizio del file di dati.
[!NOTA]
Per informazioni sul funzionamento dei batch, vedere Batch.
In alternativa, se si sceglie di non limitare le dimensioni dei batch, per migliorare le prestazioni è inoltre possibile stimare le dimensioni del file di dati nel comando. Tale stima verrà utilizzata da Query Processor durante la creazione del piano di query relativo all'operazione.
[!NOTA]
Per la specifica delle dimensioni del batch o del file di dati, l'accuratezza non è fondamentale.
Nella tabella seguente sono riepilogati i qualificatori che supportano queste alternative.
Comando |
Dimensioni del batch |
Righe inviate per batch |
Kilobyte inviati per batch |
|---|---|---|---|
bcp1 |
-bbatch_size |
-h "ROWS_PER_BATCH = bb" |
-h "KILOBYTES_PER_BATCH = cc" |
BULK INSERT2 |
BATCHSIZE = batch_size |
ROWS_PER_BATCH = rows_per_batch |
KILOBYTES_PER_BATCH = kilobytes_per_batch .gif "Nota") Nota
Se si utilizza questa opzione con BATCHSIZE, verrà generato un errore.
|
INSERT ... SELECT * FROM OPENROWSET (BULK...) |
—3 |
ROWS_PER_BATCH = rows_per_batch |
—3 |
1 In un comando bcp, non utilizzare l'opzione -bbatch_size insieme all'hint ROWS_PER_BATCH o KILOBYTES_PER_BATCH. In caso contrario, verrà generato un errore.
2 In un comando BULK INSERT, se si utilizza BATCHSIZE con ROWS_PER_BATCH o KILOBYTES_PER_BATCH, BATCHSIZE avrà la priorità.
3 Per OPENROWSET non sono disponibili le opzioni BATCHSIZE o KILOBYTES_PER_BATCH.
Nelle sezioni seguenti viene illustrato l'utilizzo di questi qualificatori.
Specifica delle dimensioni approssimate del batch
Se si importa un numero estremamente elevato di righe, può essere utile suddividere i dati in batch. Dopo avere completato ogni batch, la transazione verrà registrata. Se l'importazione bulk viene interrotta, verrà eseguito il rollback della sola transazione (batch) corrente.
[!NOTA]
L'esecuzione di operazioni bulk registrate comporta l'inserimento di una copia dei dati importati nel log delle transazioni. È pertanto possibile che le dimensioni del log aumentino rapidamente, ma dopo aver completato ogni batch è possibile eseguire il backup del log in modo da recuperare spazio sul log.
Per importare il file di dati in una serie di batch con dimensioni approssimative simili a quelle specificate, utilizzare il qualificatore seguente:
Per bcp: -b
Per BULK INSERT: BATCHSIZE
Ogni batch di righe viene inserito come una transazione separata. Se l'importazione bulk viene interrotta prima del completamento, verrà eseguito il rollback della sola transazione corrente. Ad esempio, se un file di dati contiene 1000 righe e vengono utilizzati batch di dimensione 100, l'operazione verrà registrata in MicrosoftSQL Server come 10 singole transazioni ciascuna delle quali inserisce 100 righe nella tabella di destinazione. Se l'importazione bulk viene interrotta alla riga 750, verranno rimosse soltanto le 49 righe precedenti poiché SQL Server esegue il rollback della transazione corrente. La tabella di destinazione conterrà comunque le prime 700 righe.
In SQL Server l'operazione di caricamento viene ottimizzata automaticamente in base alla dimensione del batch e ciò può comportare un miglioramento delle prestazioni. In generale, è consigliabile specificare la massima dimensione del batch possibile. Batch di grandi dimensioni consentono infatti di ottenere le migliori prestazioni per le importazioni bulk. Esistono tuttavia alcune eccezioni. Se nella tabella di destinazione sono presenti uno o più indici, è possibile che un batch di grandi dimensioni determini richieste di memoria eccessive per l'ordinamento. Inoltre, durante un caricamento eseguito in parallelo senza utilizzare l'opzione TABLOCK, una dimensione di batch maggiore può determinare ulteriori blocchi.
[!NOTA]
Non è possibile utilizzare le dimensioni di batch per l'importazione bulk di dati da un'istanza di SQL Server a un file di dati.
Specifica delle dimensioni approssimative di un file di dati
Se non si specificano le dimensioni di batch per l'operazione di importazione bulk, è possibile definire le dimensioni approssimative del file di dati per consentire a Query Processor di allocare le risorse nel piano di query. Per definire le dimensioni approssimative del file di dati, è possibile stimare il numero di righe o il numero di kilobyte di dati, come illustrato di seguito:
Stima delle righe per batch
Per stimare il numero di righe, utilizzare l'hint o l'opzione ROWS_PER_BATCH. Se si specifica un valore > 0, Query Processor utilizza il valore di ROWS_PER_BATCH come hint per l'allocazione delle risorse nel piano di query. Il valore deve appartenere allo stesso ordine del numero effettivo di righe.
Benché tutte le righe del file di dati vengano copiate in un'istanza di SQL Server mediante un unico batch, bcp visualizza un messaggio che informa che sono state inviate 1000 righe a SQL Server per ogni 1000 righe aggiunte. Questo messaggio informativo viene visualizzato indipendentemente dalla dimensione del batch specificata.
Stima dei kilobyte per batch
Per stimare le dimensioni del file di dati in kilobyte, utilizzare l'hint o l'opzione KILOBYTES_PER_BATCH. SQL Server ottimizza l'importazione bulk in base al valore specificato.
[!NOTA]
Se si esegue l'importazione bulk di un file di dati di grandi dimensioni senza specificare le dimensioni del batch o le opzioni di ottimizzazione con registrazione minima, è possibile che il log delle transazioni sia pieno prima del completamento dell'operazione. Per evitare questo problema, è possibile aumentare le dimensioni del log delle transazioni oppure consentirne l'aumento automatico.
Vedere anche