Why is my SQL Query Fast in SSMS but Slow in my C# Application?

Code Runs Slow in Application but Fast in SSMS

Problem:

A query that returns data quickly in SQL Server Management Studio (SSMS) takes a significant amount of time when run from a C# application.

Code:

The C# code uses a SqlDataAdapter and its Fill method to execute the query.

using (var conn = new SqlConnection(...))
using (var ada = new SqlDataAdapter(...))
{
    ada.Fill(Logs);
}

The SSMS query is identical to the one executed in the application.

Cause:

The discrepancy in execution time is caused by a difference in the way parameters are handled in the application and SSMS. In the application, the AddWithValue method is used to add a parameter of type NVARCHAR, while in SSMS, the parameter is declared as VARCHAR.

Solution:

To resolve this issue, either:

1. Use the constructor that accepts a type when adding parameters in the application code:

ada.SelectCommand.Parameters.Add("@clientID", SqlDbType.Varchar, 200);

2. Cast the parameter in the SQL text:

where client_id = cast(@clientID as varchar(200))

The first solution is preferred as it also addresses a potential cache pollution issue.

Additional Considerations:

• Ensure that both the application and SSMS are using the same computer and login.
• The tables referenced in the query have appropriate indexes and are performing well in SSMS.
• Consider the effect of parameter caching on query performance.

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