Why is My SQL Server Query Fast in SSMS but Slow in C# Code?

Query Performance Discrepancy in Code vs. SSMS

A query that performs smoothly in SQL Server Management Studio (SSMS) may exhibit excessive slowness when executed within code. This discrepancy, encountered during a recent code development, prompted an investigation into the underlying cause.

Code Implementation

The C# code used to run the query is as follows:

using (var conn = new SqlConnection("Data Source=backend.example.com;..."))
{
    using (var ada = new SqlDataAdapter(sqlCommand, conn))
    {
        ada.SelectCommand.Parameters.AddWithValue("@clientID", ClientID);
        ada.SelectCommand.Parameters.AddWithValue("@dt", dtpFilter.Value);
        conn.Open();
        Logs.Clear();
        ada.Fill(Logs); // Time out exception for 30 sec limit.
    }
}

SSMS Query

The identical query, extracted from the C# code, is run directly in SSMS:

SELECT [PK_JOB],[CLIENT_ID],[STATUS],[LOG_NAME],dt 
FROM [ES_HISTORY] 
inner join [es_history_dt] on [PK_JOB] = [es_historyid] 
Where client_id = @clientID and dt > @dt and (job_type > 4 or job_type = 0 or job_type = 1 or job_type = 4 ) 
Order by dt desc

Analysis

Upon examination, a subtle difference exists between the two queries. In the SSMS version, the @clientID parameter is declared as VARCHAR, while in the C# code, it is added using the AddWithValue method, which typically assigns the data type based on the .NET variable's type. In this case, the ClientID variable is of type string, which is mapped to NVARCHAR in SQL Server.

Impact

This difference has a significant impact on query performance. The NVARCHAR parameter type does not allow for Search Argument (SARG) filtering, a crucial optimization technique that SQL Server uses to quickly locate rows based on indexed columns. As a result, the query in the C# code is forced to perform a table scan, which is far less efficient than an index seek.

Solution

To resolve the discrepancy, it is necessary to explicitly specify the VARCHAR data type for the @clientID parameter in the C# code. This can be achieved using the following syntax:

ada.SelectCommand.Parameters.Add("@clientID", SqlDbType.VarChar, 200).Value = ClientID;

By ensuring that the data types of the parameters match those declared in the SSMS query, the application can avoid the performance penalty and execute the query in a time-efficient manner.

The above is the detailed content of Why is My SQL Server Query Fast in SSMS but Slow in C# Code?. For more information, please follow other related articles on the PHP Chinese website!