SQL Server參數化查詢大數據下的實踐

　　身為一名小小的程序員在日常開發中不可以避免的要和where in和like打交道在大多數情況下我們傳的參數不多簡單做下單引號敏感字符轉義之後就直接拼進了SQL執行查詢搞定若有一天你不可避免的需要提高SQL的查詢性能需要一次性where in 幾百上千甚至上萬條數據時參數化查詢將是必然進行的選擇然而如何實現where in和like的參數化查詢是個讓不少人頭疼的問題

　　where in 的參數化查詢實現

　　首先說一下我們常用的辦法直接拼SQL實現一般情況下都能滿足需要

　　string userIds = "";

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　commCommandText = stringFormat("select * from Users(nolock) where UserID in({})" userIds);

　　commExecuteNonQuery();

　　}

　　需要參數化查詢時進行的嘗試很顯然如下這樣執行SQL會報錯錯誤

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　commCommandText = "select * from Users(nolock) where UserID in(@UserID)";

　　commParametersAdd(new SqlParameter("@UserID" SqlDbTypeVarChar ) { Value = "" });

　　commExecuteNonQuery();

　　}

　　很顯然這樣會報錯誤在將 varchar 值轉換成數據類型 int 時失敗因為參數類型為字符串where in時會把@UserID當做一個字符串來處理相當於實際執行了如下語句

　　select * from Users(nolock) where UserID in()

　　若執行的語句為字符串類型的SQL執行不會報錯當然也不會查詢出任何結果

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　commCommandText = "select * from Users(nolock) where UserName in(@UserName)";

　　commParametersAdd(new SqlParameter("@UserName" SqlDbTypeVarChar ) { Value = "johndudurabbit" });

　　commExecuteNonQuery();

　　}

　　這樣不會抱任何錯誤也查不出想要的結果因為這個@UserName被當做一個字符串來處理實際相當於執行如下語句

　　select * from Users(nolock) where UserName in(johndudurabbit)

　　由此相信大家對於為何簡單的where in 傳參無法得到正確的結果知道為什麼了吧下面我們來看一看如何實現正確的參數化執行where in為了真正實現參數化where in 傳參很多人才想到了各種替代方案

　　方案使用CHARINDEX或like 方法實現參數化查詢毫無疑問這種方法成功了而且成功的復用了查詢計劃但同時也徹底的讓查詢索引失效(在此不探討索引話題)造成的後果是全表掃描如果表裡數據量很大百萬級千萬級甚至更多這樣的寫法將造成災難性後果;如果數據量比較小只想借助參數化實現防止SQL注入的話這樣寫也無可厚非還是得看具體需求(不推薦)

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　//使用CHARINDEX實現參數化查詢可以復用查詢計劃同時會使索引失效

　　commCommandText = "select * from Users(nolock) where CHARINDEX(+ltrim(str(UserID))++@UserID+)>";

　　commParametersAdd(new SqlParameter("@UserID" SqlDbTypeVarChar ) { Value = "" });

　　commExecuteNonQuery();

　　}

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　//使用like實現參數化查詢可以復用查詢計劃同時會使索引失效

　　commCommandText = "select * from Users(nolock) where +@UserID+ like %+ltrim(str(UserID))+% ";

　　commParametersAdd(new SqlParameter("@UserID" SqlDbTypeVarChar ) { Value = "" });

　　commExecuteNonQuery();

　　}

　　方案使用exec動態執行SQL這樣的寫法毫無疑問是很成功的而且代碼也比較優雅也起到了防止SQL注入的作用看上去很完美不過這種寫法和直接拼SQL執行沒啥實質性的區別查詢計劃沒有得到復用對於性能提升沒任何幫助頗有種脫了褲子放屁的感覺但也不失為一種解決方案(不推薦)

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　//使用exec動態執行SQL　　//實際執行的查詢計劃為(@UserID varchar(max))select * from Users(nolock) where UserID in ()　　//不是預期的(@UserID varchar(max))exec(select * from Users(nolock) where UserID in (+@UserID+)) commCommandText = "exec(select * from Users(nolock) where UserID in (+@UserID+))";

　　commParametersAdd(new SqlParameter("@UserID" SqlDbTypeVarChar ) { Value = "" });

　　commExecuteNonQuery();

　　}

　　方案為where in的每一個參數生成一個參數寫法上比較麻煩些傳輸的參數個數有限制最多個可以根據需要使用此方案(推薦)

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　//為每一條數據添加一個參數

　　commCommandText = "select * from Users(nolock) where UserID in (@UserID@UserId@UserID@UserID)";

　　commParametersAddRange(

　　new SqlParameter[]

　　{

　　new SqlParameter("@UserID" SqlDbTypeInt) { Value = }

　　});

　　commExecuteNonQuery();

　　}

　　方案使用臨時表實現寫法實現上比較繁瑣些可以根據需要寫個通用的where in臨時表查詢的方法以供不時之需個人比較推崇這種寫法能夠使查詢計劃得到復用而且對索引也能有效的利用不過由於需要創建臨時表會帶來額外的IO開銷若查詢頻率很高每次的數據不多時還是建議使用方案若查詢數據條數較多尤其是上千條甚至上萬條時強烈建議使用此方案可以帶來巨大的性能提升(強烈推薦)

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　string sql = @"

　　declare @Temp_Variable varchar(max)

　　create table #Temp_Table(Item varchar(max))

　　while(LEN(@Temp_Array) > )

　　begin

　　if(CHARINDEX(@Temp_Array) = )

　　begin

　　set @Temp_Variable = @Temp_Array

　　set @Temp_Array =

　　end

　　else

　　begin

　　set @Temp_Variable = LEFT(@Temp_ArrayCHARINDEX(@Temp_Array))

　　set @Temp_Array = RIGHT(@Temp_ArrayLEN(@Temp_Array)LEN(@Temp_Variable))

　　end

　　insert into #Temp_Table(Item) values(@Temp_Variable)

　　end

　　select * from Users(nolock) where exists(select from #Temp_Table(nolock) where #Temp_TableItem=UsersUserID)

　　drop table #Temp_Table";

　　commCommandText = sql;

　　commParametersAdd(new SqlParameter("@Temp_Array" SqlDbTypeVarChar ) { Value = "" });

　　commExecuteNonQuery();

　　}

　　like參數化查詢

　　like查詢根據個人習慣將通配符寫到參數值中或在SQL拼接都可兩種方法執行效果一樣在此不在詳述

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　//將 % 寫到參數值中

　　commCommandText = "select * from Users(nolock) where UserName like @UserName";

　　commParametersAdd(new SqlParameter("@UserName" SqlDbTypeVarChar ) { Value = "rabbit%" });

　　commExecuteNonQuery();

　　}

　　using (SqlConnection conn = new SqlConnection(connectionString))

　　{

　　connOpen();

　　SqlCommand comm = new SqlCommand();

　　commConnection = conn;

　　//SQL中拼接 %

　　commCommandText = "select * from Users(nolock) where UserName like @UserName+%";

　　commParametersAdd(new SqlParameter("@UserName" SqlDbTypeVarChar ) { Value = "rabbit%" });

　　commExecuteNonQuery();

　　}

　　我的寫作熱情離不開您的肯定支持

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22280.html