Performance comparison of insert and direct insert execution using stored procedures

comparison | stored Procedure | performance | execution
Recently wrote a program to import IP and the region of the list of a plain text file into the database, the first use is to execute the INSERT statement directly with SqlCommand, and then know that SQL Server can optimize the stored procedures, eliminating the time to parse the statement, Faster than using INSERT statements directly. I put the INSERT statement in a temporary stored procedure, used this temporary stored procedure for the insert operation, and roughly tested the timing of the execution of the two.

Stored procedures are:

CREATE PROCEDURE #InsertIPData
@StartIP nvarchar (50),
@EndIP nvarchar (50),
@Country nvarchar (50),
@Local nvarchar (100)
As
INSERT into [Ipwry] VALUES (@StartIP, @EndIP, @Country, @Local)

The INSERT statement is:

INSERT into [Ipwry] VALUES (@StartIP, @EndIP, @Country, @Local)


This stored procedure is executed with SqlDataAdapter update, which is a total of 180,485 records.

The results are as follows:

The first second time the third average
Direct Insert 133 seconds 132 SEC 135 Second 133.3 sec
Stored procedure 121 seconds 122 seconds 120 seconds 121 seconds


Test environment: Windows Server 2003, SQL Server v8.0.818, Framework 1.1.4322 SP1 (Winform);
Pentium 4 2.0GHz, 512MB RAM

This result shows that executing a large number of INSERT statements with stored procedures is about 10% faster than executing the INSERT statement directly.