Truncate table updating statistics

-- change to sqlcmd mode truncate table Test Table GO BULK INSERT dbo.

If you are unsure, then test the run time first with option Medium for tables with between 6,000 and 60,000 records, option Low for tables with over 60,000 records.

These options approximate the SAPDBA default settings for the CBO method.

This can probably be attributed to the overhead of having a separate call for each insert.

I didn't however expect the multiple row insert to perform so much better than the bulk insert, almost 4 times faster in each case.

The last thing we will need in order to run this test is a script to call each of the test cases.

This script is shown below and can be called from SSMS, but notice the use of the :r command which means you will have to run this script in sqlcmd mode for it to execute successfully.

Any constraint checks or index updates would have to be done regardless of the method of insert so we can exclude them as they would not have any effect on the performance between the INSERT methods.

For each type of statement we will compare inserting 10, 1 records.

For this test we will create a test table with a primary key (to be used by the update statements) and load some sample data into it. -- Create Test Upd Table for UPDATE test CREATE TABLE [dbo].[Test Upd Table]( [pk] [int] IDENTITY(1,1) PRIMARY KEY, [col1] [int] NULL, [col2] [int] NULL, [col3] [int] NULL, [col4] [int] NULL, [col5] [int] NULL, [col6] [datetime] NULL, [col7] [datetime] NULL, [col8] [datetime] NULL, [col9] [datetime] NULL, [col10] [datetime] NULL, [col11] [varchar](20) NULL, [col12] [varchar](20) NULL, [col13] [varchar](20) NULL, [col14] [varchar](20) NULL, [col15] [varchar](20) NULL ) ON [PRIMARY] GO DECLARE @val INT SELECT @val=1 WHILE @val For this test we are going to perform an update of 9 columns in the table first using a single statement and then using an individual statement for each update.

Tags: , ,