The duration was roughly 1.3 seconds with a CPU usage of 1 second. The table has 5231 pages (1 IAM, 18 index pages and 5212 data pages) and, considering that everything was selected, SQL server had to scan through the whole table.
INTO’ statement that selected all the rows (776,286) from ‘FactProductInventory’ into the target table ‘FactProductInventoryNew’.Īs we can see in the above results, SQL Server brings the source table’s pages into the buffer cache (read-ahead reads) and then performs 5231 logical reads, which is exactly what you’d expect to see. For those who are interested fn_dblog() provides more details, which I personally found more useful because you read thought the transaction log and see row by row what is happening. I also tracked the log space usage by looking at the ‘DBCC SQLPERF(LOGSPACE)’ view. I enabled ‘STATISTICS TIME’ and ‘STATISTICS IO’ to view query (execution time and CPU) and disk I/O (read and writes) statistics.
SQL BULK COPY LOG FULL
The database was in the FULL recovery model and its transaction log file was 109MB with 98.3% of free space. The table has 776,286 rows for a total size of 41MB. Scenarioįor testing purposes, I used the ‘FactProductInventory’ clustered table in the AdventureWorksDW2012 database. Of course, ‘SELECT … INTO’ does produce the destination table on the fly based on the result of the SELECT statement, however, I was curious about why it was faster. The most obvious thought is that the first one runs faster because it benefits from ‘minimal logging’ and creates the target table automatically.
A colleague suggested I use the ‘SELECT … INTO’ statement instead and that got me thinking: why is this faster or any better? I ran this in a test environment first, where the query took longer than 1 hour to complete and the database’s transaction log file grew by 50GB, even though the table was only 18GB. First, I created the destination table and then I ran the ‘INSERT … SELECT’ statement. I recently moved some data for a customer to a brand-new table due to the bad design of the source table. Optionally, use bcp.exe from a release prior to SQL Server 2005 (9.x).In this blog post I will explore the difference between the ‘SELECT … INTO’ and ‘INSERT … SELECT’ statements. To resolve this error, fix the data to match the target data type. Beginning in SQL Server 2005 (9.x), if data types do not match between the file and the target table, bcp.exe will raise an error if there is any data that would have to be truncated to fit into the target table. However, in that situation, bcp.exe simply truncated extra fields. Prior to SQL Server 2005 (9.x), bcp.exe converted numeric data to a money data type, if the target table had a money data type. This is because in the later versions, bcp.exe no longer performs implicit data type conversion. When using bcp.exe in SQL Server 2005 (9.x) or later, you might see errors in situations where there were no errors prior to SQL Server 2005 (9.x). Minimal logging reduces the possibility of a bulk load operation filling the log space and is also more efficient than full logging.įor information on using minimal logging, see Prerequisites for Minimal Logging in Bulk Import. Under certain conditions, minimally logging is possible. For large data loads, this can cause the transaction log to fill rapidly. With the Full Recovery model, all row-insert operations performed by bulk load are fully logged in the transaction log. Applications must use the SQL Server-specific bulk-copy functions to perform bulk copy operations.
The ODBC SQLBulkOperations function has no relationship to the SQL Server bulk copy functions. sqlncli11.dll is distributed with the SQL Server Native Client ODBC driver. sqlncli11.lib is distributed with the SQL Server Native Client ODBC driver. Must be in the library path of the linker and specified as a file to be linked. sqlncli.h must be included in the ODBC application performing bulk copy operations and must be in the application's include path when it is compiled. Includes function prototypes and constant definitions for bulk copy functions. The specialized bulk copy support is in the following files:
SQL BULK COPY LOG UPGRADE
This driver-specific extension provides an easy upgrade path for existing DB-Library applications that use bulk copy functions.
SQL BULK COPY LOG DRIVER
When connected to an instance of SQL Server version 7.0 or later, the SQL Server Native Client ODBC driver supports the DB-Library functions that perform SQL Server bulk copy operations. The ODBC standard does not directly support SQL Server bulk copy operations.
0 kommentar(er)
