SQL Server parameterized query practice under large data

As a small programmer, in the day-to-day development can not be avoided with where in and like to deal with, in most cases we pass the parameters of simple quotes, sensitive words escape directly after the SQL, execute the query, fix. If one day you inevitably need to improve SQL query performance, need a one-time where in hundreds of, thousands, or even tens of thousands of data, parameterized query will be the inevitable choice. However, how to implement the parameterized query of where in and like is a problem that many people have a headache.

Parameterized query implementation of where in

First of all, we often use the method, directly spell SQL implementation, generally can meet the needs.

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

}

When you need a parameterized query, it is clear that executing SQL will cause an error.

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

}

It's obvious that this will report an error: the conversion of the varchar value ' 1,2,3,4 ' to a data type int failed because the parameter type is a string, where in will treat the @userid as a string, which is equivalent to actually executing the following statement:

SELECT * from Users (NOLOCK) where UserID in (' 1,2,3,4 ')

If the executed statement is of type string, SQL execution does not give an error and, of course, no results are queried.

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

}

This does not hold any error, nor does it find the desired result, as the @username is treated as a string, which is actually equivalent to executing the following statement:

SELECT * from Users (NOLOCK) where UserName in (' John ', ' Dudu ', ' rabbit ')

It is believed that everyone can not get the correct result for why the simple where in is the argument. Now let's take a look at how to implement the correct parameterized execution where in, many people think of alternatives in order to really implement parameterized where in parameters:

Scenario 1: Using Charindex or like methods to implement parameterized queries, there is no doubt that this method is successful, and successful reuse of the query plan, but also thoroughly let the query index invalidation (this does not discuss the index topic), the result is a full table scan, if the data volume is large, millions, Tens even more, such a writing would have disastrous consequences; If the data volume is small, just want to rely on parameterized implementation to prevent SQL injection this is understandable, or depends on the specific needs. (Not recommended)

using (SqlConnection conn = new SqlConnection (connectionString))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

Using CHARINDEX to implement parameterized queries, you can reuse query plans and invalidate indexes

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;

Using like to implement parameterized queries, you can reuse the query plan and invalidate the index

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

}

Scenario 2: Using exec to execute SQL dynamically, this writing is no doubt very successful, and the code is also more elegant, also plays a role in preventing SQL injection, looks perfect, but this type of writing and direct SQL execution No substantive differences, the query plan has not been reused, It's not a good way to improve performance, but it's kind of a solution to the feeling of pants farting. (Not recommended)

using (SqlConnection conn = new SqlConnection (connectionString))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

A query plan using EXEC dynamic Execute SQL//actual execution is (@UserID varchar (max)) SELECT * from Users (NOLOCK) where UserID in (1,2,3,4)//not expected (@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 ();

}

Scenario 3: A parameter is generated for each parameter in the where in, and the number of parameters transmitted is limited, up to 2,100, and can be used as needed. Recommended

using (SqlConnection conn = new SqlConnection (connectionString))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

Add a parameter to each piece of data

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

}

Scenario 4: Using temporary table implementations, writing implementation is more cumbersome, you can write a generic where in the temporary table Query method, for a rainy, personal more respected this style, can make the query plan to be reused and the index can be effectively used, but because of the need to create temporary tables, will bring additional IO overhead, if the query frequency is very high, each time the data is recommended to use Scheme 3, if the number of query data, especially thousands or even tens of thousands of times, it is strongly recommended to use this scheme, can bring huge performance improvements. (Highly recommended)

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 the 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 parameterized query

Like query according to personal custom to write wildcard characters in parameter values or in the SQL concatenation can be, the two methods perform the same effect, not in detail here.

using (SqlConnection conn = new SqlConnection (connectionString))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

Write% to parameter values

Comm.commandtext = "SELECT * from the Users (NOLOCK) where UserName like @UserName";

Comm. Parameters.Add (New SqlParameter ("@UserName", SqlDbType.VarChar) {Value = "rabbit%"});

Comm. ExecuteNonQuery ();

}

using (SqlConnection conn = new SqlConnection (connectionString))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

Stitching% in SQL

Comm.commandtext = "SELECT * from Users (NOLOCK) where UserName like @UserName + '% '";

Comm. Parameters.Add (New SqlParameter ("@UserName", SqlDbType.VarChar) {Value = "rabbit%"});

Comm. ExecuteNonQuery ();

}

My passion for writing is inseparable from your affirmative support.