Why is some sql query much slower when used with SqlCommand?

by

Another thing that can be important is the SET options that are enabled. Some of these options change the query plan sufficiently to change the profile. Some can have a huge impact if you are looking at (for example) a calculated + persisted (and possibly indexed) column: if the SET options aren’t compatible, it can be forced to re-calculate the values, rather than using the indexed value – which can change an index seek into a table scan + calculation.

Try using the profiler to see what SET options are “in play”, and see if using those options changes things.

Another impact is the connection string; for example, if you enable MARS that can change the behaviour in subtle ways.

Finally, transactions (implicit (TransactionScope) or explicit) can have a huge impact, depending on the isolation level.

More Related Contents:

  1. store image in database or in a system file? [closed]
  2. SQL Identity (autonumber) is Incremented Even with a Transaction Rollback
  3. SELECT * FROM X WHERE id IN (…) with Dapper ORM
  4. What’s the best method to pass parameters to SQLCommand?
  5. Bulk Insert to Oracle using .NET
  6. Parameterized Queries with LIKE and IN conditions
  7. What is the difference between SqlCommand.CommandTimeout and SqlConnection.ConnectionTimeout?
  8. Does SqlCommand.Dispose close the connection?
  9. Using “GO” within a transaction
  10. Does End Using close an open SQL Connection
  11. Does this code prevent SQL injection?
  12. Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)
  13. How do I write LINQ’s .Skip(1000).Take(100) in pure SQL?
  14. How can you name the Dataset’s Tables you return in a stored proc?
  15. How can I create database tables from XSD files?
  16. How can I avoid SQL injection attacks in my ASP.NET application?
  17. Is it possible to send a collection of ID’s as a ADO.NET SQL parameter?
  18. How can I configure Entity Framework to automatically trim values retrieved for specific columns mapped to char(N) fields?
  19. How do I convert a string into safe SQL String?
  20. When would SqlCommand.ExecuteReader() return null?
  21. Regular expression to find all table names in a query
  22. How to return a page of results from SQL?
  23. Using a variable for table name in ‘From’ clause in SQL Server 2008
  24. Html.Partial vs Html.RenderPartial & Html.Action vs Html.RenderAction
  25. Should we switch to use async I/O by default?
  26. How to connect to Oracle 11 database from . net
  27. Can I set Option Explicit and Option Strict on a Project/Solution level?
  28. JavaScriptSerializer UTC DateTime issues
  29. what is IEnumerable in .net
  30. Can I pass non-string to WCF RESTful service using UriTemplate?

Leave a Comment