SQL Server High Performance write

Using Stored Procedures

In the previous example, we hardcode the SQL code directly into the client code, so the database needs to parse the SQL statements in the client using the parser, so we can use stored procedures instead to reduce the time overhead of the parser, and more importantly, because SQL is executed dynamically, So we can modify the SQL statements in the stored procedure without recompiling and publishing the program.

The field user_registered in the user table sets the default value (GETDATE ()), so we can improve the performance of the system by eliminating the table default constraint, in short, we need to provide the value of the field user_registered.

Next, let's omit the default value constraint in the user table and increase the stored procedure, with the following code:

--=============================================--author:jkhuang--Create date:08/16/2012--De Scription:creates stored procedure to insert--data into table jk_users.--=========================================== ==alter PROCEDURE [dbo]. [Sp_insert_jk_users] @user_login varchar, @user_pass varchar, @user_nicename varchar, @user_em  AIL varchar, @user_url varchar, @user_activation_key varchar, @user_status int, @display_name varchar (asbegin) SET NOCOUNT on;--The stored procedure allows SQL server to avoid virtually all parser Workinsert Into Jk_users (User_login, User_pass, User_nicename, User_email, User_status,display_name, User_url, User_activatio N_key, user_registered) VALUES (@user_login, @user_pass, @user_nicename, @user_email, @user_status, @display_name, @ User_url, @user_activation_key, GETDATE ()); END 

We defined the stored procedure sp_insert_jk_users to insert data into the table, and when we re-executed the code, we found that the time to insert the data was reduced to 6.7401 seconds.

Figure 3 Data Write time

Working with database transactions

Think about whether the data can be extended to the database, can write in bulk? If the delay is allowed to be written to the database, then we can use transaction to delay data writing.

Database transaction is a logical unit in the execution process of database management system, which consists of a limited database operation sequence. When SQL Server ensures that the transaction executes successfully, the data is written to the database, and the transaction is rolled back instead.

If we perform 10 separate operations on the database, SQL Server will need to allocate 10 lock overhead, but if all of these operations are encapsulated in a single transaction, then SQL Server will only need to allocate one lock cost at a time.

    Calc Insert 10000 records consume time.    var sw = stopwatch.startnew ();    Creates a database connection. using (var conn = new SqlConnection (configurationmanager.connectionstrings["SQLCONN2"]. ToString ())) {Conn.        Open ();        int cnt = 0; SqlTransaction trans = conn.        BeginTransaction ();                while (cnt++ < 10000) {using (var cmd = new SqlCommand ("Sp_insert_jk_users", conn)) { Parameterized SQL to defense injection attacks CMD.                CommandType = CommandType.StoredProcedure;                Uses transcation to batch insert data.                To avoid lock and connection overhead. Cmd.                Transaction = trans; Cmd.                Parameters.Add ("@user_login", userlogin); Cmd.                Parameters.Add ("@user_pass", Userpass); Cmd.                Parameters.Add ("@user_nicename", usernicename); Cmd.               Parameters.Add ("@user_email", useremail); Cmd.                Parameters.Add ("@user_status", userstatus); Cmd.                Parameters.Add ("@display_name", displayName); Cmd.                Parameters.Add ("@user_url", Userurl); Cmd.                Parameters.Add ("@user_activation_key", Useractivationkey); Cmd.            ExecuteNonQuery ();        }}////If no exception, commit transcation.    Trans.commit (); } SW. Stop ();}

Figure 4 Data Write time

Using SqlBulkCopy

By using transactions to encapsulate the write operation, when we rerun the code, we find that the data write is significantly faster, in just 4.5109 seconds, because a transaction simply allocates a lock resource, reducing the time-consuming allocation lock and database join.

Of course, we can also use sqlbulkcopy to achieve a large number of data write operations, the implementation code is as follows:

var sw = stopwatch.startnew ();////creates a database connection.using (var conn = new SqlConnection (ConfigurationManager. connectionstrings["SQLCONN2"]. ToString ())) {Conn.    Open ();        using (var bulkcopy = new SqlBulkCopy (conn)) {////maping the data columns.        BULKCOPY.COLUMNMAPPINGS.ADD ("User_login", "User_login");        BULKCOPY.COLUMNMAPPINGS.ADD ("User_pass", "User_pass");        BULKCOPY.COLUMNMAPPINGS.ADD ("User_nicename", "user_nicename");        BULKCOPY.COLUMNMAPPINGS.ADD ("User_email", "User_email");        BULKCOPY.COLUMNMAPPINGS.ADD ("User_url", "User_url");        BULKCOPY.COLUMNMAPPINGS.ADD ("user_registered", "user_registered");        BULKCOPY.COLUMNMAPPINGS.ADD ("User_activation_key", "User_activation_key");        BULKCOPY.COLUMNMAPPINGS.ADD ("User_status", "user_status");        BULKCOPY.COLUMNMAPPINGS.ADD ("Display_name", "display_name");        Bulkcopy.destinationtablename = "Dbo.jk_users";        Insert data into DataTable. Bulkcopy.writetoServer (DataRows); } SW. Stop ();}

Figure 5 Data Write time

Above, we implement data in bulk by transaction and SqlBulkCopy to write to the database, but in fact every time we call CMD. The ExecuteNonQuery () method generates a round-trip message from the client application to the database, so we want to see if there is a way to write a message only once.

Working with Table parameters

If you use SQL Server 2008, it provides a new feature table variable (table Parameters) that aggregates the entire table data into one parameter to the stored procedure or SQL statement. Its attention to performance overhead is the aggregation of data into parameters (O (data volume)).

Now, we modify the previous code to define our table variables in SQL Server, as follows:

--=============================================--Author:        jkhuang--Create date:08/16/2012--Description:    Declares a user table paramter.--=============================================create TYPE Jk_users_bulk_insert as TABLE (    user_login varchar,    user_pass varchar,    user_nicename varchar),    User_email varchar (+),    user_url varchar (+),    user_activation_key varchar,    user_status int,    Display_ Name varchar (250))

Above, we define a table parameter Jk_users_bulk_insert, and then we define a stored procedure to accept the table parameter Jk_users_bulk_insert, which is defined as follows:

--=============================================--Author:        jkhuang--Create date:08/16/2012--Description:    Creates a stored procedure, receive--a jk_users_bulk_insert argument.--============================================= CREATE PROCEDURE sp_insert_jk_users @usersTable jk_users_bulk_insert READONLY asinsert into Jk_users (User_login, User_ Pass, User_nicename, User_email, User_url, User_activation_key, User_status, Display_name, user_registered) SELECT user _login, User_pass, User_nicename, User_email, User_url, User_activation_key, User_status, Display_name, GETDATE () from @ Userstable

Then we call the stored procedure in the client code and pass the table as a parameter to the stored procedure.

var sw = stopwatch.startnew (); using (var conn = new SqlConnection (configurationmanager.connectionstrings["SQLCONN2"]. ToString ())) {    Conn. Open ();    Invokes the stored procedure.    using (var cmd = new SqlCommand ("Sp_insert_jk_users", conn))    {        cmd.commandtype = CommandType.StoredProcedure;        Adding a "structured" parameter allows the insert tons of data with low overhead        var param = new SqlParameter ( "@userTable", sqldbtype.structured) {Value = dt};        Cmd. Parameters.Add (param);        Cmd. ExecuteNonQuery ();    }} Sw. Stop ();

Now, we re-execute the write operation to find that the write efficiency is equivalent to SqlBulkCopy.