萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> SQL Server參數化查詢大數據下的實踐

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

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

  where in 的參數化查詢實現

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

  string userIds = "1,2,3,4";

  using (SqlConnection conn = new SqlConnection(connectionString))

  {

  conn.Open();

  SqlCommand comm = new SqlCommand();

  comm.Connection = conn;

  comm.CommandText = string.Format("select * from Users(nolock) where UserID in({0})", userIds);

  comm.ExecuteNonQuery();

  }

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

  using (SqlConnection conn = new SqlConnection(connectionString))

  {

  conn.Open();

  SqlCommand comm = new SqlCommand();

  comm.Connection = conn;

  comm.CommandText = "select * from Users(nolock) where UserID in(@UserID)";

  comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });

  comm.ExecuteNonQuery();

  }

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

  select * from Users(nolock) where UserID in('1,2,3,4')

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

  using (SqlConnection conn = new SqlConnection(connectionString))

  {

  conn.Open();

  SqlCommand comm = new SqlCommand();

  comm.Connection = conn;

  comm.CommandText = "select * from Users(nolock) where UserName in(@UserName)";

  comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, -1) { Value = "'john','dudu','rabbit'" });

  comm.ExecuteNonQuery();

  }

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

  select * from Users(nolock) where UserName in('''john'',''dudu'',''rabbit''')

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

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

  using (SqlConnection conn = new SqlConnection(connectionString))

  {

  conn.Open();

  SqlCommand comm = new SqlCommand();

  comm.Connection = conn;

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

  comm.CommandText = "select * from Users(nolock) where CHARINDEX(','+ltrim(str(UserID))+',',','+@UserID+',')>0";

  comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });

  comm.ExecuteNonQuery();

  }

  using (SqlConnection conn = new SqlConnection(connectionString))

  {

  conn.Open();

  SqlCommand comm = new SqlCommand();

  comm.Connection = conn;

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

  comm.CommandText = "select * from Users(nolock) where ','+@UserID+',' like '%,'+ltrim(str(UserID))+',%' ";

  comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });

  comm.ExecuteNonQuery();

  }

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

  using (SqlConnection conn = new SqlConnection(connectionString))

  {

  conn.Open();

  SqlCommand comm = new SqlCommand();

  comm.Connection = conn;

  //使用exec動態執行SQL  //實際執行的查詢計劃為(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4)  //不是預期的(@UserID varchar(max))exec('select * from Users(nolock) where UserID in ('+@UserID+')') comm.CommandText = "exec('select * from Users(nolock) where UserID in ('+@UserID+')')";

  comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });

  comm.ExecuteNonQuery();

  }

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

  using (SqlConnection conn = new SqlConnection(connectionString))

  {

  conn.Open();

  SqlCommand comm = new SqlCommand();

  comm.Connection = conn;

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

  comm.CommandText = "select * from Users(nolock) where UserID in (@UserID1,@UserId2,@UserID3,@UserID4)";

  comm.Parameters.AddRange(

  new SqlParameter[]

  {

  new SqlParameter("@UserID1", SqlDbType.Int) { Value = 1},

  new SqlParameter("@UserID2", SqlDbType.Int) { Value = 2},

  new SqlParameter("@UserID3", SqlDbType.Int) { Value = 3},

  new SqlParameter("@UserID4", SqlDbType.Int) { Value = 4}

  });

  comm.ExecuteNonQuery();

  }

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

  using (SqlConnection conn = new SqlConnection(connectionString))

  {

  conn.Open();

  SqlCommand comm = new SqlCommand();

  comm.Connection = conn;

  string sql = @"

  declare @Temp_Variable varchar(max)

  create table #Temp_Table(Item varchar(max))

  while(LEN(@Temp_Array) > 0)

  begin

  if(CHARINDEX(',',@Temp_Array) = 0)

  begin

  set @Temp_Variable = @Temp_Array

  set @Temp_Array = ''

  end

  else

  begin

  set @Temp_Variable = LEFT(@Temp_Array,CHARINDEX(',',@Temp_Array)-1)

  set @Temp_Array = RIGHT(@Temp_Array,LEN(@Temp_Array)-LEN(@Temp_Variable)-1)

  end

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

  end

  select * from Users(nolock) where exists(select 1 from #Temp_Table(nolock) where #Temp_Table.Item=Users.UserID)

  drop table #Temp_Table";

  comm.CommandText = sql;

  comm.Parameters.Add(new SqlParameter("@Temp_Array", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });

  comm.ExecuteNonQuery();

  }

  like參數化查詢

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

  using (SqlConnection conn = new SqlConnection(connectionString))<

copyright © 萬盛學電腦網 all rights reserved