SQL Server parameterized query of big data

As a small programmer, it is inevitable to deal with wherein 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, you need to have hundreds or thousands

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, you need to place hundreds or thousands of SQL queries at a time.

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

}