SQL Server parameterized query of big data

As a small programmer, it is inevitable to deal with where in and like in daily development, in most cases, the parameters we pass are not much simple, single quotation marks, sensitive characters escape, and then directly spelled into the SQL, execute the query, done. If one day you inevitably need to improve the SQL query performance, and you need to place hundreds, thousands, or even tens of thousands of pieces of data at a time, parameterized query will be an inevitable choice. However, how to implement the where in and like parameterized queries is a headache for many people.

Implementation of where in parametric Query

First of all, let's talk about the common method to implement SQL directly, which can meet the needs in general.

String userIds = "1, 2, 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 ();

}

An error is reported when SQL statements are executed as follows.

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, 4 "});

Comm. ExecuteNonQuery ();

}

Obviously, an error is reported: failed to convert the varchar values '1, 2, 3, and 4' to the Data Type int, because the parameter type is a string, in where in, @ UserID is treated as a string, which is equivalent to executing the following statement:

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

If the executed statement is of the string type, no error is reported during SQL Execution, and no results are displayed.

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

}

In this way, no errors will be carried and expected results cannot be found, because this @ UserName is treated as a string, which is equivalent to executing the following statement:

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

I believe that you know why simple where in passing parameters cannot get the correct results. Let's take a look at how to implement correct parameterized where in execution, many people have come up with a variety of alternative solutions to achieve parameter passing through where in:

Solution 1: Use the CHARINDEX or like method to implement parameterized query. There is no doubt that this method is successful and the query plan is reused successfully, however, it also permanently invalidates the query index (this topic is not discussed here), resulting in full table scanning. If the data volume in the table is large, it may be millions, tens of millions, or even more, this writing method will have disastrous consequences. If the data size is small and you only want to use parameterization to prevent SQL injection, it is understandable to write this method. You still have to look at the specific requirements. (Not recommended)

Using (SqlConnection conn = new SqlConnection (connectionString ))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

// Use CHARINDEX to implement parameterized query. You can reuse the query plan and invalidate the index.

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, 4 "});

Comm. ExecuteNonQuery ();

}

Using (SqlConnection conn = new SqlConnection (connectionString ))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

// Use like to implement parameterized query. 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, 4 "});

Comm. ExecuteNonQuery ();

}

Solution 2: Execute SQL dynamically using exec. This method is undoubtedly very successful, and the code is also elegant. It also plays a role in preventing SQL injection and looks perfect, however, there is no substantial difference between this writing method and direct SQL Execution. The query plan is not reused, and it does not help in performance improvement. It is quite a bit of a feeling of getting rid of your pants, but it is also a solution. (Not recommended)

Using (SqlConnection conn = new SqlConnection (connectionString ))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

// Execute the SQL statement dynamically using exec // The actual query plan is (@ UserID varchar (max) select * from Users (nolock) where UserID in) // unexpected (@ 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, 4 "});

Comm. ExecuteNonQuery ();

}

Solution 3: generate a parameter for each where in parameter, which is more difficult to write. The number of transmitted parameters is limited. A maximum of 2100 parameters can be used as needed. (Recommended)

Using (SqlConnection conn = new SqlConnection (connectionString ))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

// Add a parameter for 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 ();

}

Solution 4: The use of temporary tables is cumbersome in writing. You can write a common where in temporary table query method as needed. I personally highly recommend this method, the query plan can be reused and the index can be effectively used. However, creating a temporary table may lead to additional IO overhead. If the query frequency is high, solution 3 is recommended for a small amount of data each time. If the number of data entries to be queried is large, especially for thousands or even tens of thousands, this solution is strongly recommended, which can greatly improve the performance. (Strongly 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 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, 4 "});

Comm. ExecuteNonQuery ();

}

Like parameterized Query

Like query writes wildcards to parameter values based on your habits or concatenates SQL statements. The two methods have the same effect and are not described here.

Using (SqlConnection conn = new SqlConnection (connectionString ))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

// Write % to the parameter value

Comm. CommandText = "select * from Users (nolock) where UserName like @ UserName ";

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

Comm. ExecuteNonQuery ();

}

Using (SqlConnection conn = new SqlConnection (connectionString ))

{

Conn. Open ();

SqlCommand comm = new SqlCommand ();

Comm. Connection = conn;

// Concatenate % in SQL

Comm. CommandText = "select * from Users (nolock) where UserName like @ UserName + '% '";

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

Comm. ExecuteNonQuery ();

}

My enthusiasm for writing is inseparable from your support.