C #: how to set the execution of a stored procedure using parallelism or improve runtime?

Question

I apologize if my question is too stupid, or if it is a duplicate, but I did not find a solution for my problem. Thanks for any tips or answers!

In my ASP.NET MVC 5 project, I get data from a SQL Server table with more than 1.5 million records.

To prevent SQL injection, I always use only parameterized queries and generates a stored procedure.

For example, one of the requests that I send to the server:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand sqlCommand = new SqlCommand("SELECT TOP 5 NAME
                                            FROM TABLE_NAME 
                                            WHERE COLUMN_NAME LIKE @Param", connection);
    sqlCommand.Parameters.AddWithValue("@Param", "someValue");

    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    sqlDataAdapter.SelectCommand = sqlCommand;

    sqlDataAdapter.Fill(dataSet);
}

Result in SQL Server Profiler:

exec sp_executesql N'SELECT TOP 5 NAME FROM TABLE_NAME WHERE COLUMN_NAME LIKE @Param',N'@Param nvarchar(11)',@Param=N'%someValue%'

If there are only a few entries in the table that are suitable for searching, he must check all 1.5 million orders before returning the result.

, 1000 , SQL Server orders , , .

, , parallelism , ?

: , , .

Answer 1

:

create index idx_test on MyTable (Column_Name) include(Name)

:

SELECT TOP 5 NAME FROM TABLE_NAME with(index(idx_test)) WHERE COLUMN_NAME LIKE @Param

.