SqlCommand timeout even though in SQL Studio same query is fast

Question

I have what I thought was a simple query that I execute from my little log-processing application. The aim of this method is to simply get the highest date value out of 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 on the table, but I'm not sure if that's relevant, because the problem I'm getting is that when I run this code, it throws a timeout after 2 minutes on the ExecuteScalar line.

If I copy and paste that query into SSMS with the same parameters, it completes in 00:00:04, or even 00:00:00 (if I've just updated stats).

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

I have checked for blocking, and I can't see anything like that - that database is only being accessed by this one app, and it's not multi-threaded or anything like that.

Update: Interestingly, when I run the exact query as captured by Profiler, it also takes a long 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'

The actual columns are varchar(50) and varchar(10) respectively.

Probably parameter sniffing or the query you execute in SSMS is not actually exactly the same in every respect (including datatypes nvarchar vs varchar). See Slow in the Application, Fast in SSMS? Understanding Performance Mysteries. You might consider an index on host,site,date as well if you don't have one already. — Martin Smith
– Martin Smith, Commented Jun 19, 2013 at 9:27
Also how many rows does the table have? 2 minutes is quite a long time. If you don't have millions of rows maybe the cause lies somewhere else. — Martin Smith
– Martin Smith, Commented Jun 19, 2013 at 9:32
@MartinSmith Interesting on both counts - I think it might be parameter sniffing. If I run the query as captured by Profiler (sp_executesql with parameters) it runs equally slow. I'll have a look. The table is currently 2 million rows or so, but that's just a couple of days' data. Thanks for the pointer. — Cylindric
– Cylindric, Commented Jun 19, 2013 at 9:39

Martin Smith · Accepted Answer · 2013-06-19 10:18:01Z

5

The query you execute in SSMS

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

Is not the same as the one 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 one has varchar string literals. The second one has nvarchar parameters. Your column datatypes are in fact varchar.

nvarchar has higher datatype precedence than varchar so you are forcing an implicit cast of the column. This will render any indexes on the column useless.

Change the parameter datatypes in the application to varchar

answered Jun 19, 2013 at 10:18

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cylindric Over a year ago

Heh, I just spotted the same! I'm moving the data over now and will report back, but this looks like the answer!

Martin Smith Over a year ago

@Cylindric - You can try adding the N prefix to the string literals. i.e. SELECT MAX(date) FROM iislogs WHERE host = N'servername' AND site = N'W3SVC1' to use nvarchar string literals and check if the problem now occurs.

Collectives™ on Stack Overflow

SqlCommand timeout even though in SQL Studio same query is fast

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related