1.1.1 摘要
在開發(fā)過程中,我們不時會遇到系統(tǒng)性能瓶頸問題,而引起這一問題原因可以很多,有可能是代碼不夠高效、有可能是硬件或網(wǎng)絡(luò)問題,也有可能是數(shù)據(jù)庫設(shè)計的問題。
本篇博文將針對一些常用的數(shù)據(jù)庫性能調(diào)休方法進(jìn)行介紹,而且,為了編寫高效的SQL代碼,我們需要掌握一些基本代碼優(yōu)化的技巧,所以,我們將從一些基本優(yōu)化技巧進(jìn)行介紹。
本文目錄
代碼中的問題
數(shù)據(jù)庫性能開銷
使用存儲過程
使用數(shù)據(jù)庫事務(wù)
使用SqlBulkCopy
使用表參數(shù)
1.1.2 正文
假設(shè),我們要設(shè)計一個博客系統(tǒng),其中包含一個用戶表(User),它用來存儲用戶的賬戶名、密碼、顯示名稱和注冊日期等信息。
由于時間的關(guān)系,我們已經(jīng)把User表設(shè)計好了,它包括賬戶名、密碼(注意:這里沒有考慮隱私信息的加密存儲)、顯示名稱和注冊日期等,具體設(shè)計如下:
復(fù)制代碼 代碼如下:
-- =============================================
-- 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
)
圖1 Users表設(shè)計
上面,我們定義了Users表,它包含賬戶名、密碼、顯示名稱和注冊日期等10個字段,其中,ID是一個自增的主鍵,user_resistered用來記錄用戶的注冊時間,它設(shè)置了默認(rèn)值GETDATE()。
接下來,我們將通過客戶端代碼實(shí)現(xiàn)數(shù)據(jù)存儲到Users表中,具體的代碼如下:
復(fù)制代碼 代碼如下:
//// 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方式實(shí)現(xiàn)數(shù)據(jù)寫入功能,但大家是否發(fā)現(xiàn)代碼存在問題或可以改進(jìn)的地方呢?
首先,我們在客戶端代碼中,創(chuàng)建一個數(shù)據(jù)庫連接,它需要占用一定的系統(tǒng)資源,當(dāng)操作完畢之后我們需要釋放占用的系統(tǒng)資源,當(dāng)然,我們可以手動釋放資源,具體實(shí)現(xiàn)如下:
復(fù)制代碼 代碼如下:
//// 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將得不到釋放。我們仔細(xì)想想當(dāng)發(fā)生異常時,可以通過try/catch捕獲異常,所以無論是否發(fā)生異常都可以使用finally檢查資源是否已經(jīng)釋放了,具體實(shí)現(xiàn)如下:
復(fù)制代碼 代碼如下:
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語句,那么是否有更簡潔的方法實(shí)現(xiàn)資源的安全釋放呢?
其實(shí),我們可以使用using語句實(shí)現(xiàn)資源的釋放,具體實(shí)現(xiàn)如下:
using語句:定義一個范圍,將在此范圍之外釋放一個或多個對象。
復(fù)制代碼 代碼如下:
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語句實(shí)現(xiàn)資源的釋放,那么是否所有對象都可以使用using語句實(shí)現(xiàn)釋放呢?
只有類型實(shí)現(xiàn)了IDisposable接口并且重寫Dispose()方法可以使用using語句實(shí)現(xiàn)資源釋放,由于SqlConnection和SqlCommand實(shí)現(xiàn)了IDisposable接口,那么我們可以使用using語句實(shí)現(xiàn)資源釋放和異常處理。
在客戶端代碼中,我們使用拼接SQL語句方式實(shí)現(xiàn)數(shù)據(jù)寫入,由于SQL語句是動態(tài)執(zhí)行的,所以惡意用戶可以通過拼接SQL的方式實(shí)施SQL注入攻擊。
對于SQL注入攻擊,我們可以通過以下方式防御:
•正則表達(dá)校驗(yàn)用戶輸入
•參數(shù)化存儲過程
•參數(shù)化SQL語句
•添加數(shù)據(jù)庫新架構(gòu)
•LINQ to SQL
接下來,我們將通過參數(shù)化SQL語句防御SQL注入攻擊,大家也可以使用其他的方法防御SQL注入攻擊,具體實(shí)現(xiàn)代碼如下:
復(fù)制代碼 代碼如下:
//// 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();
}
}
上面通過參數(shù)化SQL語句和using語句對代碼進(jìn)行改進(jìn),現(xiàn)在代碼的可讀性更強(qiáng)了,而且也避免了SQL注入攻擊和資源釋放等問題。
接下來,讓我們簡單的測試一下代碼執(zhí)行時間,首先我們在代碼中添加方法Stopwatch.StartNew()和Stopwatch.Stop()來計算寫入代碼的執(zhí)行時間,具體代碼如下:
復(fù)制代碼 代碼如下:
//// 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();
}
上面,我們往數(shù)據(jù)庫中寫入了10000條數(shù)據(jù),執(zhí)行時間為 7.136秒(我的機(jī)器很破了),這樣系統(tǒng)性能還是可以滿足許多公司的需求了。
假如,用戶請求量增大了,我們還能保證系統(tǒng)能滿足需求嗎?事實(shí)上,我們不應(yīng)該滿足于現(xiàn)有的系統(tǒng)性能,因?yàn)槲覀冎来a的執(zhí)行效率還有很大的提升空間。
接下來,將進(jìn)一步介紹代碼改善的方法。
圖2 數(shù)據(jù)寫入Users表
為了使數(shù)據(jù)庫獲得更快的寫入速度,我們必須了解數(shù)據(jù)庫在進(jìn)行寫入操作時的主要耗時。
數(shù)據(jù)庫性能開銷
連接時間
當(dāng)我們執(zhí)行conn.Open()時,首先,必須建立物理通道(例如套接字或命名管道),必須與服務(wù)器進(jìn)行初次握手,必須分析連接字符串信息,必須由服務(wù)器對連接進(jìn)行身份驗(yàn)證,必須運(yùn)行檢查以便在當(dāng)前事務(wù)中登記,等等
這一系列操作可能需要一兩秒鐘時間,如果我們每次執(zhí)行conn.Open()都有進(jìn)行這一系列操作是很耗費(fèi)時間的,為了使打開的連接成本最低,ADO.NET使用稱為連接池的優(yōu)化方法。
連接池:減少新連接需要打開的次數(shù),只要用戶在連接上調(diào)用 Open()方法,池進(jìn)程就會檢查池中是否有可用的連接,如果某個池連接可用,那么將該連接返回給調(diào)用者,而不是創(chuàng)建新連接;應(yīng)用程序在該連接上調(diào)用 Close()或Dispose() 時,池進(jìn)程會將連接返回到活動連接池集中,而不是真正關(guān)閉連接,連接返回到池中之后,即可在下一個 Open 調(diào)用中重復(fù)使用。
解析器的開銷
當(dāng)我們向SQL Server傳遞SQL語句INSERT INTO …時,它需要對SQL語句進(jìn)行解析,由于SQL Server解析器執(zhí)行速度很快,所以解析時間往往是可以忽略不計,但我們?nèi)匀豢梢酝ㄟ^使用存儲過程,而不是直SQL語句來減少解析器的開銷。
數(shù)據(jù)庫連接
為了提供ACID(事務(wù)的四個特性),SQL Server必須確保所有的數(shù)據(jù)庫更改是有序的。它是通過使用鎖來確保該數(shù)據(jù)庫插入、刪除或更新操作之間不會相互沖突(關(guān)于數(shù)據(jù)庫的鎖請參考這里)。
由于,大多數(shù)數(shù)據(jù)庫都是面向多用戶的環(huán)境,當(dāng)我們對User表進(jìn)行插入操作時,也許有成千上百的用戶也在對User表進(jìn)行操作,所以說,SQL Server必須確保這些操作是有序進(jìn)行的。
那么,當(dāng)SQL Server正在做所有這些事情時,它會產(chǎn)生鎖,以確保用戶獲得有意義的結(jié)果。SQL Server保證每條語句執(zhí)行時,數(shù)據(jù)庫是完全可預(yù)測的(例如:預(yù)測SQL執(zhí)行方式)和管理鎖都需要耗費(fèi)一定的時間。
約束處理
在插入數(shù)據(jù)時,每個約束(如:外鍵、默認(rèn)值、SQL CHECK等)需要額外的時間來檢測數(shù)據(jù)是否符合約束;由于SQL Server為了保證每個插入、更新或刪除的記錄都符合約束條件,所以,我們需要考慮是否應(yīng)該在數(shù)據(jù)量大的表中增加約束條件。
Varchar
VARCHAR是數(shù)據(jù)庫常用的類型,但它也可能導(dǎo)致意想不到的性能開銷;每次我們存儲可變長度的列,那么SQL Server必須做更多的內(nèi)存管理;字符串可以很容易地消耗數(shù)百字節(jié)的內(nèi)存的,如果我們在一個VARCHAR列中設(shè)置索引,那么SQL Server執(zhí)行B-樹搜索時,就需要進(jìn)行O(字符串長度)次比較,然而,整數(shù)字段比較次數(shù)只受限于內(nèi)存延遲和CPU頻率。
磁盤IO
SQL Server最終會將數(shù)據(jù)寫入到磁盤中,首先,SQL Server把數(shù)據(jù)寫入到事務(wù)日志中,當(dāng)執(zhí)行備份時,事務(wù)日志會合并到永久的數(shù)據(jù)庫文件中;這一系列操作由后臺完成,它不會影響到數(shù)據(jù)查詢的速度,但每個事物都必須擁有屬于自己的磁盤空間,所以我們可以通過給事務(wù)日志和主數(shù)據(jù)文件分配獨(dú)立的磁盤空間減少IO開銷,當(dāng)然,最好解決辦法是盡可能減少事務(wù)的數(shù)量。
正如大家所看到的,我們通過優(yōu)化聯(lián)接時間、 解析器的開銷、 數(shù)據(jù)庫聯(lián)接、約束處理,、Varchar和磁盤IO等方法來優(yōu)化數(shù)據(jù)庫,接下來,我們將對前面的例子進(jìn)行進(jìn)一步的優(yōu)化。
使用存儲過程
前面例子中,我們把SQL代碼直接Hardcode在客戶端代碼中,那么,數(shù)據(jù)庫就需要使用解析器解析客戶端中SQL語句,所以我們可以改用使用存儲過程,從而,減少解析器的時間開銷;更重要的一點(diǎn)是,由于SQL是動態(tài)執(zhí)行的,所以我們修改存儲過程中的SQL語句也無需重新編譯和發(fā)布程序。
User表中的字段user_registered設(shè)置了默認(rèn)值(GETDATE()),那么我們通過消除表默認(rèn)值約束來提高系統(tǒng)的性能,簡而言之,我們需要提供字段user_registered的值。
接下來,讓我們省去User表中的默認(rèn)值約束和增加存儲過程,具體代碼如下:
復(fù)制代碼 代碼如下:
-- =============================================
-- 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向表中插入數(shù)據(jù),當(dāng)我們重新執(zhí)行代碼時,發(fā)現(xiàn)數(shù)據(jù)插入的時間縮短為6.7401秒。
圖3數(shù)據(jù)寫入時間
使用數(shù)據(jù)庫事務(wù)
想想數(shù)據(jù)是否可以延長寫入到數(shù)據(jù)庫中,是否可以批量地寫入呢?如果允許延遲一段時間才寫入到數(shù)據(jù)庫中,那么我們可以使用Transaction來延遲數(shù)據(jù)寫入。
數(shù)據(jù)庫事務(wù)是數(shù)據(jù)庫管理系統(tǒng)執(zhí)行過程中的一個邏輯單位,由一個有限的數(shù)據(jù)庫操作序列構(gòu)成。 SQL Server確保事務(wù)執(zhí)行成功后,數(shù)據(jù)寫入到數(shù)據(jù)庫中,反之,事務(wù)將回滾。
如果我們對數(shù)據(jù)庫進(jìn)行十次獨(dú)立的操作,那么SQL Server就需要分配十次鎖開銷,但如果把這些操作都封裝在一個事務(wù)中,那么SQL Server只需要分配一次鎖開銷。
復(fù)制代碼 代碼如下:
//// 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();
}
圖4 數(shù)據(jù)寫入時間
使用SqlBulkCopy
通過使用事務(wù)封裝了寫入操作,當(dāng)我們重新運(yùn)行代碼,發(fā)現(xiàn)數(shù)據(jù)寫入的速度大大提高了,只需4.5109秒,由于一個事務(wù)只需分配一次鎖資源,減少了分配鎖和數(shù)據(jù)庫聯(lián)接的耗時。
當(dāng)然,我們可以也使用SqlBulkCopy實(shí)現(xiàn)大量數(shù)據(jù)的寫入操作,具體實(shí)現(xiàn)代碼如下:
復(fù)制代碼 代碼如下:
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 數(shù)據(jù)寫入時間
上面,我們通過事務(wù)和SqlBulkCopy實(shí)現(xiàn)數(shù)據(jù)批量寫入數(shù)據(jù)庫中,但事實(shí)上,每次我們調(diào)用cmd.ExecuteNonQuery()方法都會產(chǎn)生一個往返消息,從客戶端應(yīng)用程序到數(shù)據(jù)庫中,所以我們想是否存在一種方法只發(fā)送一次消息就完成寫入的操作呢?
使用表參數(shù)
如果,大家使用SQL Server 2008,它提供一個新的功能表變量(Table Parameters)可以將整個表數(shù)據(jù)匯集成一個參數(shù)傳遞給存儲過程或SQL語句。它的注意性能開銷是將數(shù)據(jù)匯集成參數(shù)(O(數(shù)據(jù)量))。
現(xiàn)在,我們修改之前的代碼,在SQL Server中定義我們的表變量,具體定義如下:
復(fù)制代碼 代碼如下:
-- =============================================
-- 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)
)
上面,我們定義了一個表參數(shù)jk_users_bulk_insert,接著我們定義一個存儲過程接受表參數(shù)jk_users_bulk_insert,具體定義如下:
復(fù)制代碼 代碼如下:
-- =============================================
-- 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
接下我們在客戶端代碼中,調(diào)用存儲過程并且將表作為參數(shù)方式傳遞給存儲過程。
復(fù)制代碼 代碼如下:
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();
現(xiàn)在,我們重新執(zhí)行寫入操作發(fā)現(xiàn)寫入效率與SqlBulkCopy相當(dāng)。
1.1.3總結(jié)
本文通過博客系統(tǒng)用戶表設(shè)計的例子,介紹我們在設(shè)計過程中容易犯的錯誤和代碼的缺陷,例如:SQL注入、數(shù)據(jù)庫資源釋放等問題;進(jìn)而使用一些常用的代碼優(yōu)化技巧對代碼進(jìn)行優(yōu)化,并且通過分析數(shù)據(jù)庫寫入的性能開銷(連接時間、解析器、數(shù)據(jù)庫連接、約束處理、VARCHAR和磁盤IO),我們使用存儲過程、數(shù)據(jù)庫事務(wù)、SqlBulkCopy和表參數(shù)等方式降低數(shù)據(jù)庫的開銷。
[1] http://beginner-sql-tutorial.com/sql-query-tuning.htm
[2] http://www.dzone.com/links/r/sql_optimization_tipsquestions.html
[3] http://blackrabbitcoder.net/archive/2010/11/11/c.net-little-wonders---a-presentation.aspx
[4] http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/