SqlCommand timeout, although the same query is executed quickly in SQL Studio

Question

I have what I thought was a simple request that I execute from my small log processing application. The purpose of this method is to simply get the highest date value from the log table:

private DateTime GetLastEntryDate(string serverName, string siteName) { DateTime dt = DateTime.MinValue; using (var con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LogParserDB"].ConnectionString)) { con.Open(); using (var cmd = new SqlCommand("SELECT MAX(date) FROM iislogs WHERE host=@Host AND site=@Site ", con)) { cmd.CommandTimeout = 120; cmd.Parameters.AddWithValue("Host", serverName); cmd.Parameters.AddWithValue("Site", siteName); var result = cmd.ExecuteScalar(); if (result != DBNull.Value) { dt = (DateTime)result; } } } return dt; } 

There are some indexes in the table, but I'm not sure if this is relevant, because the problem I get is that when I run this code, it gives a timeout after 2 minutes in the ExecuteScalar line.

If I copy and paste this request into SSMS with the same parameters, it ends at 00:00:04 or even 00:00:00 (if I just updated the statistics).

 SELECT MAX(date) FROM iislogs WHERE host='servername' AND site='W3SVC1' 

I checked the lock and I don’t see anything like it: only one application is available to this database, and it is not multi-threaded or something like that.

---

Update: Interestingly, when I run the exact request captured by Profiler, it also takes a lot of time in SSMS:

 exec sp_executesql N'SELECT MAX(date) FROM iislogs WHERE host=@Host AND site=@Site ',N'@Host nvarchar(13),@Site nvarchar(6)',@Host=N'servername',@Site=N'W3SVC1' 

Actual columns are varchar(50) and varchar(10) respectively.

Answer 1

Request made in SSMS

 SELECT MAX(date) FROM iislogs WHERE host = 'servername' AND site = 'W3SVC1' 

This is not the same as the application executed by your application.

 EXEC sp_executesql N'SELECT MAX(date) FROM iislogs WHERE host=@Host AND site=@Site ', N'@Host nvarchar(13),@Site nvarchar(6)', @Host=N'servername', @Site=N'W3SVC1' 

The first has varchar string literals. The second has nvarchar options. In fact, your varchar column data types.

nvarchar has higher datatype priority than varchar , so you force an implicit column listing. This will make unnecessary indexes in the column.

Change parameter data types in application to varchar