SQL Server 2016 enhanced the bulk insert activities (BULK INSERT), leveraging the CPUs vector instructions. The vector based approach allows the bulk data to be processed faster.
For example, when inserting data from a text file the integer conversion leverages SSE instructions when the character length is more than 4 characters and trace flag 4809 is disabled.
Take the value of '12345678' needing to be converted to the integer value 1,2345,678. A common algorithm used to determine the integer value of each position one might loop over the character array subtracting the value of character '0' from each entry so it can be casted to a BYTE value and shifted to accommodate base 10 mathematics.
Using a single vector instruction, all 8 characters (BYTES) can subtract the '0' character value. The pseudocode looks like this:
In a single SSE instruction the value is adjusted instead of requiring 8 loops to process the bytes.
'It Just Runs Faster'
- SQL Server 2016 takes advantage of CPU vector instructions to improve bulk insert performance.
Suresh Kandoth -Senior SQL Server Escalation Engineer
Bob Dorr - Principal SQL Server Software Engineer
DEMO - It Just Runs Faster: Bulk Insert Character To Integer Conversion
This demonstration shows the use of vector instructions to perform character string to integer conversions during the BULK INSERT command.
In order to demonstrate the vector instruction capabilities, this demonstration is a narrow reproduction focused on a single vector improvement, not the only vector based improvement. The demonstration highlights ~10% performance increase on a very simple data set.
Use SQL Server Management Studio (SSMS) to connect to a SQL Server 2012 or 2014 instance.
Paste the script below in a new query window and execute (ATL+X) the script to populate demonstration data.