程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SQL Server 高機能寫入的一些經歷總結

SQL Server 高機能寫入的一些經歷總結

編輯：MSSQL

SQL Server 高機能寫入的一些經歷總結。本站提示廣大學習愛好者：（SQL Server 高機能寫入的一些經歷總結）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server 高機能寫入的一些經歷總結正文

1.1.1 摘要

在開辟進程中，我們不時會碰到體系機能瓶頸成績，而惹起這一成績緣由可以許多，有能夠是代碼不敷高效、有能夠是硬件或收集成績，也有能夠是數據庫設計的成績。

本篇博文將針對一些經常使用的數據庫機能調休辦法停止引見，並且，為了編寫高效的SQL代碼，我們須要控制一些根本代碼優化的技能，所以，我們將從一些根本優化技能停止引見。

本文目次

代碼中的成績
數據庫機能開支
應用存儲進程
應用數據庫事務
應用SqlBulkCopy
應用表參數

1.1.2 注釋

假定，我們要設計一個博客體系，個中包括一個用戶表（User），它用來存儲用戶的賬戶名、暗碼、顯示稱號和注冊日期等信息。

因為時光的關系，我們曾經把User表設計好了，它包含賬戶名、暗碼（留意：這裡沒有斟酌隱私信息的加密存儲）、顯示稱號和注冊日期等，詳細設計以下：

-- =============================================
-- 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
)

optimization0

圖1 Users表設計

下面，我們界說了Users表，它包括賬戶名、暗碼、顯示稱號和注冊日期等10個字段，個中，ID是一個自增的主鍵，user_resistered用來記載用戶的注冊時光，它設置了默許值GETDATE()。

接上去，我們將經由過程客戶端代碼完成數據存儲到Users表中，詳細的代碼以下：

//// 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();

代碼中的成績
下面，我們應用再通俗不外的ADO.NET方法完成數據寫入功效，但年夜家能否發明代碼存在成績或可以改良的處所呢？

起首，我們在客戶端代碼中，創立一個數據庫銜接，它須要占用必定的體系資本，當操作終了以後我們須要釋放占用的體系資本，固然，我們可以手動釋放資本，詳細完成以下：

//// 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資本時拋出異常，那末在它前面的資本SqlConnection將得不到釋放。我們細心想一想當產生異常時，可以經由過程try/catch捕捉異常，所以不管能否產生異常都可使用finally檢討資本能否曾經釋放了，詳細完成以下：

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();
}

經由過程下面的finally方法處置了異常情形是很廣泛的，但為了更平安釋放資本，使得我們增長了finally和if語句，那末能否有更簡練的辦法完成資本的平安釋放呢？
其實，我們可使用using語句完成資本的釋放，詳細完成以下：
using語句：界說一個規模，將在此規模以外釋放一個或多個對象。

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.
}

下面的代碼應用了using語句完成資本的釋放，那末能否一切對象都可使用using語句完成釋放呢？

只要類型完成了IDisposable接口而且重寫Dispose()辦法可使用using語句完成資本釋放，因為SqlConnection和SqlCommand完成了IDisposable接口，那末我們可使用using語句完成資本釋放和異常處置。

在客戶端代碼中，我們應用拼接SQL語句方法完成數據寫入，因為SQL語句是靜態履行的，所以歹意用戶可以經由過程拼接SQL的方法實行SQL注入進擊。

關於SQL注入進擊，我們可以經由過程以下方法進攻：

•正則表達校驗用戶輸出
•參數化存儲進程
•參數化SQL語句
•添加數據庫新架構
•LINQ to SQL
接上去，我們將經由過程參數化SQL語句進攻SQL注入進擊，年夜家也能夠應用其他的辦法進攻SQL注入進擊，詳細完成代碼以下：

//// 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();
}
}

下面經由過程參數化SQL語句和using語句對代碼停止改良，如今代碼的可讀性更強了，並且也防止了SQL注入進擊和資本釋放等成績。

接上去，讓我們簡略的測試一下代碼履行時光，起首我們在代碼中添加辦法Stopwatch.StartNew()和Stopwatch.Stop()來盤算寫入代碼的履行時光，詳細代碼以下：

//// 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();
}

下面，我們往數據庫中寫入了10000條數據，履行時光為 7.136秒（我的機械很破了），如許體系機能照樣可以知足很多公司的需求了。

假設，用戶要求量增年夜了，我們還能包管體系能知足需求嗎？現實上，我們不該該知足於現有的體系機能，由於我們曉得代碼的履行效力還有很年夜的晉升空間。

接上去，將進一步引見代碼改良的辦法。

optimization1

圖2 數據寫入Users表

為了使數據庫取得更快的寫入速度，我們必需懂得數據庫在停止寫入操作時的重要耗時。

數據庫機能開支
銜接時光
當我們履行conn.Open()時，起首，必需樹立物理通道（例如套接字或定名管道），必需與辦事器停止首次握手，必需剖析銜接字符串信息，必需由辦事器對銜接停止身份驗證，必需運轉檢討以便在以後事務中掛號，等等

這一系列操作能夠須要一兩秒鐘時光，假如我們每次履行conn.Open()都有停止這一系列操作是很消耗時光的，為了使翻開的銜接本錢最低，ADO.NET應用稱為銜接池的優化辦法。

銜接池：削減新銜接須要翻開的次數，只需用戶在銜接上挪用 Open()辦法，池過程就會檢討池中能否有可用的銜接，假如某個池銜接可用，那末將該銜接前往給挪用者，而不是創立新銜接；運用法式在該銜接上挪用 Close()或Dispose() 時，池過程會將銜接前往到運動銜接池集中，而不是真正封閉銜接，銜接前往到池中以後，便可鄙人一個 Open 挪用中反復應用。

解析器的開支
當我們向SQL Server傳遞SQL語句INSERT INTO …時，它須要對SQL語句停止解析，因為SQL Server解析器履行速度很快，所以解析時光常常是可以疏忽不計，但我們依然可以經由過程應用存儲進程，而不是直SQL語句來削減解析器的開支。

數據庫銜接
為了供給ACID（事務的四個特征），SQL Server必需確保一切的數據庫更改是有序的。它是經由過程應用鎖來確保該數據庫拔出、刪除或更新操作之間不會互相抵觸（關於數據庫的鎖請參考這裡）。

因為，年夜多半數據庫都是面向多用戶的情況，當我們對User表停止拔出操作時，或許有成千上百的用戶也在對User表停止操作，所以說，SQL Server必需確保這些操作是有序停止的。

那末，當SQL Server正在做一切這些工作時，它會發生鎖，以確保用戶取得成心義的成果。SQL Server包管每條語句履行時，數據庫是完整可猜測的（例如：猜測SQL履行方法）和治理鎖都須要消耗必定的時光。

束縛處置
在拔出數據時，每一個束縛（如：外鍵、默許值、SQL CHECK等）須要額定的時光來檢測數據能否相符束縛；因為SQL Server為了包管每一個拔出、更新或刪除的記載都相符束縛前提，所以，我們須要斟酌能否應當在數據量年夜的表中增長束縛前提。

Varchar
VARCHAR是數據庫經常使用的類型，但它也能夠招致意想不到的機能開支；每次我們存儲可變長度的列，那末SQL Server必需做更多的內存治理；字符串可以很輕易地消費數百字節的內存的，假如我們在一個VARCHAR列中設置索引，那末SQL Server履行B-樹搜刮時，就須要停止O(字符串長度)次比擬，但是，整數字段比擬次數只受限於內存延遲和CPU頻率。

磁盤IO
SQL Server終究會將數據寫入到磁盤中，起首，SQL Server把數據寫入到事務日記中，當履行備份時，事務日記匯合並到永遠的數據庫文件中；這一系列操作由後台完成，它不會影響到數據查詢的速度，但每一個事物都必需具有屬於本身的磁盤空間，所以我們可以經由過程給事務日記和主數據文件分派自力的磁盤空間削減IO開支，固然，最好處理方法是盡量削減事務的數目。

正如年夜家所看到的，我們經由過程優化聯接時光、解析器的開支、數據庫聯接、束縛處置,、Varchar和磁盤IO等辦法來優化數據庫，接上去，我們將對後面的例子停止進一步的優化。

應用存儲進程
後面例子中，我們把SQL代碼直接Hardcode在客戶端代碼中，那末，數據庫就須要應用解析器解析客戶端中SQL語句，所以我們可以改用應用存儲進程，從而，削減解析器的時光開支；更主要的一點是，因為SQL是靜態履行的，所以我們修正存儲進程中的SQL語句也無需從新編譯和宣布法式。

User表中的字段user_registered設置了默許值（GETDATE()），那末我們經由過程清除表默許值束縛來進步體系的機能，簡而言之，我們須要供給字段user_registered的值。

接上去，讓我們省去User表中的默許值束縛和增長存儲進程，詳細代碼以下：

-- =============================================
-- 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)

AS
BEGIN
SET NOCOUNT ON;

-- The stored procedure allows SQL server to avoid virtually all parser work
INSERT 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

下面我們界說了存儲進程SP_Insert_jk_users向表中拔出數據，當我們從新履行代碼時，發明數據拔出的時光延長為6.7401秒。

optimization2

圖3數據寫入時光

應用數據庫事務

想一想數據能否可以延伸寫入到數據庫中，能否可以批量地寫入呢？假如許可延遲一段時光才寫入到數據庫中，那末我們可使用Transaction來延遲數據寫入。

數據庫事務是數據庫治理體系履行進程中的一個邏輯單元，由一個無限的數據庫操作序列組成。 SQL Server確保事務履行勝利後，數據寫入到數據庫中，反之，事務將回滾。

假如我們對數據庫停止十次自力的操作，那末SQL Server就須要分派十次鎖開支，但假如把這些操作都封裝在一個事務中，那末SQL Server只須要分派一次鎖開支。

//// 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();
}

optimization3 圖4 數據寫入時光

應用SqlBulkCopy
經由過程應用事務封裝了寫入操作，當我們從新運轉代碼，發明數據寫入的速度年夜年夜進步了，只需4.5109秒，因為一個事務只需分派一次鎖資本，削減了分派鎖和數據庫聯接的耗時。

固然，我們可以也應用SqlBulkCopy完成年夜量數據的寫入操作，詳細完成代碼以下：

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();
}
optimization4

圖5 數據寫入時光

下面，我們經由過程事務和SqlBulkCopy完成數據批量寫入數據庫中，但現實上，每次我們挪用cmd.ExecuteNonQuery()辦法都邑發生一個往復新聞，從客戶端運用法式到數據庫中，所以我們想能否存在一種辦法只發送一次新聞就完成寫入的操作呢？

應用表參數
假如，年夜家應用SQL Server 2008，它供給一個新的功效表變量（Table Parameters）可以將全部表數據聚集成一個參數傳遞給存儲進程或SQL語句。它的留意機能開支是將數據聚集成參數(O(數據量))。

如今，我們修正之前的代碼，在SQL Server中界說我們的表變量，詳細界說以下：

-- =============================================
-- 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)
)

下面，我們界說了一個表參數jk_users_bulk_insert，接著我們界說一個存儲進程接收表參數jk_users_bulk_insert，詳細界說以下：

-- =============================================
-- 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
AS

INSERT 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和表參數等方法下降數據庫的開支。