Query extremely slow in code but fast in SSMS

by

Your code in SSMS is not the same code you run in your application. This line in your application adds a NVARCHAR parameter:

 ada.SelectCommand.Parameters.AddWithValue("@clientID", ClientID);

while in the SSMS script you declare it as VARCHAR:

declare @clientID varchar(200)

Due to the rules of Data Type Precedence the Where client_id = @clientID expression in your query is not SARG-able where @clientID is of type NVARCHAR (I’m making a leap of faith and assume that client_id column is of type VARCHAR). The application thus forces a table scan where the SSMS query can do a quick key seek. This is a well know and understood issue with using Parameters.AddWithValue and has been discussed in many articles before, eg. see How Data Access Code Affects Database Performance. Once the problem is understood, the solutions are trivial:

The first solution is superior because it solves the cache pollution problem in addition to the SARG-ability problem.

I would also recommend you read Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

More Related Contents:

  1. How can I round a value in SQL
  2. How do I update a value in C# and SQL Server? [closed]
  3. SQL Server Char/VarChar DateTime Error [closed]
  4. How to get last inserted id?
  5. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  6. Execute Insert command and return inserted Id in Sql
  7. Adding multiple parameterized variables to a database in c#
  8. Getting return value from stored procedure in C#
  9. SQL error: Incorrect syntax near the keyword ‘User’
  10. SQL WHERE clause matching values with trailing spaces
  11. SqlBulkCopy from a List
  12. Code to validate SQL Scripts
  13. SQLException : String or binary data would be truncated
  14. SqlConnection.Close() inside using statement
  15. Generate class from database table
  16. ALTER TABLE DROP COLUMN failed because one or more objects access this column
  17. When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  18. Can I use ADFS 2.0 to authenticate certain users against SQL Server?
  19. What is passing parameters to SQL and why do I need it?
  20. The variable name ‘@’ has already been declared. Variable names must be unique within a query batch or stored procedure
  21. How to pass string array in SQL parameter to IN clause in SQL
  22. How to pass a null variable to a SQL Stored Procedure from C#.net code
  23. How to insert data into SQL Server
  24. Query validation using C#
  25. Use SQL Server time datatype in C#.NET application?
  26. Database Error: There is no row at position 0
  27. Generating sql code programmatically
  28. How to give ADO.NET Parameters
  29. Capturing count from an SQL query
  30. Is SqlConnection.Close() need inside a Using statement?

Leave a Comment