SQL Query slow in .NET application but instantaneous in SQL Server Management Studio

by

In my experience the usual reason why a query runs fast in SSMS but slow from .NET is due to differences in the connection’s SET-tings. When a connection is opened by either SSMS or SqlConnection, a bunch of SET commands are automatically issued to set up the execution environment. Unfortunately SSMS and SqlConnection have different SET defaults.

One common difference is SET ARITHABORT. Try issuing SET ARITHABORT ON as the first command from your .NET code.

SQL Profiler can be used to monitor which SET commands are issued by both SSMS and .NET so you can find other differences.

The following code demonstrates how to issue a SET command but note that this code has not been tested.

using (SqlConnection conn = new SqlConnection("<CONNECTION_STRING>")) {
    conn.Open();

    using (SqlCommand comm = new SqlCommand("SET ARITHABORT ON", conn)) {
        comm.ExecuteNonQuery();
    }

    // Do your own stuff here but you must use the same connection object
    // The SET command applies to the connection. Any other connections will not
    // be affected, nor will any new connections opened. If you want this applied
    // to every connection, you must do it every time one is opened.
}

More Related Contents:

  1. Any way to SQLBulkCopy “insert or update if exists”?
  2. Pros and Cons of using SqlCommand Prepare in C#?
  3. Is it better to execute many sql commands with one connection, or reconnect every time?
  4. SQL Server: Dynamic where-clause
  5. How to pass an array into a SQL Server stored procedure
  6. Execute a large SQL script (with GO commands)
  7. Calling stored procedure with return value
  8. Cannot open database “test” requested by the login. The login failed. Login failed for user ‘xyz\ASPNET’
  9. Make first letter of a string upper case (with maximum performance)
  10. Using StringWriter for XML Serialization
  11. Generating permutations of a set (most efficiently)
  12. How to pass sqlparameter to IN()? [duplicate]
  13. What is the real overhead of try/catch in C#?
  14. Check if a string contains an element from a list (of strings)
  15. Why does the c# compiler emit Activator.CreateInstance when calling new in with a generic type with a new() constraint?
  16. Connection timeout for SQL server
  17. MultipleActiveResultSets=True or multiple connections?
  18. C# – Which is the best alternative to ‘switch on type’? [duplicate]
  19. Generate class from database table
  20. How can I programmatically limit my program’s CPU usage to below 70%?
  21. Why is Parallel.ForEach much faster then AsParallel().ForAll() even though MSDN suggests otherwise?
  22. Does String.GetHashCode consider the full string or only part of it?
  23. try catch performance
  24. Fastest way to check if string contains only digits in C#
  25. Upload CSV file to SQL server
  26. Is the conditional operator slow?
  27. Winforms: SuspendLayout/ResumeLayout is not enough?
  28. Heap versus Stack allocation implications (.NET)
  29. Watch for a table new records in sql database
  30. C# Dynamic Keyword — Run-time penalty?

Leave a Comment