SQL Server 高性能写入的一些总结
-- =============================================-- Author: JKhuang-- Create date: 7/8/2012-- Description: A table stores the user information.-- =============================================CREATE TABLE [dbo].[jk_users]( -- This is the reference to Users table, it is primary key. [ID] [bigint] IDENTITY(1,1) NOT NULL, [user_login] [varchar](60) NOT NULL, [user_pass] [varchar](64) NOT NULL, [user_nicename] [varchar](50) NOT NULL, [user_email] [varchar](100) NOT NULL, [user_url] [varchar](100) NOT NULL, -- This field get the default from function GETDATE(). [user_registered] [datetime] NOT NULL CONSTRAINT [DF_jk_users_user_registered] DEFAULT (getdate()), [user_activation_key] [varchar](60) NOT NULL, [user_status] [int] NOT NULL CONSTRAINT [DF_jk_users_user_status] DEFAULT ((0)), [display_name] [varchar](250) NOT NULL
//// Creates a database connection.var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString());conn.Open();//// This is a massive SQL injection vulnerability, //// don't ever write your own SQL statements with string formatting!string sql = String.Format( @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')", userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);var cmd = new SqlCommand(sql, conn);cmd.ExecuteNonQuery();//// Because this call to Close() is not wrapped in a try/catch/finally clause, //// it could be missed if an exception occurs above. Don't do this!conn.Close(); //// Creates a database connection.var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString());conn.Open();//// This is a massive SQL injection vulnerability, //// don't ever write your own SQL statements with string formatting!string sql = String.Format( @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')", userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);var cmd = new SqlCommand(sql, conn);cmd.ExecuteNonQuery();//// If throws an exception on cmd dispose.cmd.Dispose();//// conn can't be disposed.conn.Close();conn.Dispose();SqlCommand cmd = null;SqlConnection conn = null;try{ //// Creates a database connection. conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString()); conn.Open(); //// This is a massive SQL injection vulnerability, //// don't ever write your own SQL statements with string formatting! string sql = String.Format( @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')", userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey); cmd = new SqlCommand(sql, conn); cmd.ExecuteNonQuery();}finally{ //// Regardless of whether there is an exception, //// we will dispose the resource. if (cmd != null) cmd.Dispose(); if (conn != null) conn.Dispose(); string sql = String.Format( @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')", userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);//// Creates a database connection.using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString()))using (var cmd = new SqlCommand(sql, conn)){ //// Your code here. //// Creates a database connection.using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString())){ conn.Open(); string sql = string.Format( @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)"); using (var cmd = new SqlCommand(sql, conn)) { //// Parameterized SQL to defense injection attacks 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(); } //// 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; while (cnt++ < 10000) { string sql = string.Format(@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key) VALUES (@user_login, @user_pass, @user_nicename, @user_email, @user_status, @display_name, @user_url, @user_activation_key)"); using (var cmd = new SqlCommand(sql, conn)) { //// Parameterized SQL to defense injection attacks 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(); } } } sw.Stop();} -- =============================================-- Author: JKhuang-- Create date: 08/16/2012-- Description: Creates stored procedure to insert-- data into table jk_users.-- =============================================ALTER PROCEDURE [dbo].[SP_Insert_jk_users] @user_login varchar(60), @user_pass varchar(64), @user_nicename varchar(50), @user_email varchar(100), @user_url varchar(100), @user_activation_key varchar(60), @user_status int, @display_name varchar(250) 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_activation_key, user_registered) VALUES (@user_login, @user_pass, @user_nicename, @user_email, @user_status, @display_name, @user_url, @user_activation_key, GETDATE());END
//// 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();}/// <summary>/// Gets the data rows./// </summary>/// <returns></returns>DataRow[] GetDataRows(int rowCnt){ //// Creates a custom table. var dt = new DataTable("jk_users"); dt.Columns.Add(new DataColumn("user_login", typeof(System.String))); dt.Columns.Add(new DataColumn("user_pass", typeof(System.String))); dt.Columns.Add(new DataColumn("user_nicename", typeof(System.String))); dt.Columns.Add(new DataColumn("user_email", typeof(System.String))); dt.Columns.Add(new DataColumn("user_url", typeof(System.String))); dt.Columns.Add(new DataColumn("user_registered", typeof(System.DateTime))); dt.Columns.Add(new DataColumn("user_activation_key", typeof(System.String))); dt.Columns.Add(new DataColumn("user_status", typeof(System.Int32))); dt.Columns.Add(new DataColumn("display_name", typeof(System.String))); //// Initializes data row. var dr = dt.NewRow(); dr["user_login"] = "JK_RUSH"; dr["user_pass"] = "D*<1C2jK#-"; dr["user_nicename"] = "JK"; dr["user_email"] = "jkhuang@gamil.com"; dr["user_status"] = 1; dr["display_name"] = "JK_RUSH"; dr["user_url"] = "http://www.cnblogs.com/rush"; dr["user_activation_key"] = "347894102386"; dr["user_registered"] = DateTime.Now; //// Creates data row array. var dataRows = new DataRow[rowCnt]; for (int i = 0; i < rowCnt; i++) { dataRows[i] = dr; } return dataRows;}//// Creates 10001 data rows. var dataRows = GetDataRows(10001);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();}图5 数据写入时间
-- =============================================-- Author: JKhuang-- Create date: 08/16/2012-- Description: Declares a user table paramter.-- =============================================CREATE TYPE jk_users_bulk_insert AS TABLE ( user_login varchar(60), user_pass varchar(64), user_nicename varchar(50), user_email varchar(100), user_url varchar(100), user_activation_key varchar(60), user_status int, display_name varchar(250))
-- =============================================-- 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
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 you to insert tons of data with low overhead var param = new SqlParameter("@userTable", SqlDbType.Structured) { Value = dt }; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); }}sw.Stop(); 现在，我们重新执行写入操作发现写入效率与SqlBulkCopy相当。
1.1.3总结
本文通过博客系统用户表设计的例子，介绍我们在设计过程中容易犯 的错误和代码的缺陷，例如：SQL注入、数据库资源释放等问题；进而使用一些常用的代码优化技巧对代码进行优化，并且通过分析数据库写入的性能开销（连接 时间、解析器、数据库连接、约束处理、VARCHAR和磁盘IO），我们使用存储过程、数据库事务、SqlBulkCopy和表参数等方式降低数据库的开 销。