CODEWITHSUNDEEP
sql-serverentity-frameworklinq
pabdulin
pabdulin

Reputation: 35235

Same SQL query is fast or slow depending on execution context

I've encountered a strange phenomena then investigatating a slow view of a typical ASP.NET MVC application. One of the queries is running ridiculously slow for no obvious reason. The LINQ query in question look like this (Db is DbContext):

var testResults = Db.CustomTestResults
    .Include(tr => tr.TestMachine.Platform)
    .Include(tr => tr.TestCase)
    .Include(tr => tr.CustomTestResultAnalysis.Select(tra => tra.AnalysisOutcomeData))
    .Where(tr => tr.CustomTestBuildId == testBuild.Id)
    .ToList()
    .AsReadOnly();

nothing special actually. Depending on filter query result set can vary in size, from 10 to 10000 records at max.

The SQL generated query (captured by LINQ debug log), executed from SSMS, runs fast, about 2 seconds for the largest sets and less than a second for smaller ones. However then run by IIS strange things happen. The queries began to run like ~1/100x slower speed. The smaller ones take ~10 seconds to execute, the larger are failing due to query execution timeout. I'm not sure if any other queries are affected, but this one is only that is dealing with large data sets, so it's most obvious to notice the problem.

As this was not confusing enough this same code was running perfectly as expected not so long ago. So the bug seems to be caused by some external factors. The database is SQL Server 2014 SP2, EF is at v6.2, IIS 7.5.

Would appreciate any ideas in what areas and how I could investigate this further.

Upvotes: 3

Views: 1430

Answers (2)

pabdulin
pabdulin

Reputation: 35235

As it turned out, the issue was in SQL Server optimizations, which start to work some time after multiple runs of the similar queries. This problem can be detected by any nonrelevant change to the original query, which fixes performance for some time.

This behaviour can be properly mitigated by controlling query command options. One of the solutions for EF is demonstrated here.

As a temporary "quick-and-dirty" solution I used this approach to randomize query each time, thus preventing optimizations by SQL Server engine:

private static IQueryable<CustomTestResult> RandomizeQuery(IQueryable<CustomTestResult> query)
{
    const int minConditions = 1;
    const int maxConditions = 5;
    const int minId = -100;
    const int maxId = -1;

    var random = new Random();
    var conditionsCount = random.Next(minConditions, maxConditions);
    for (int i = 0; i < conditionsCount; i++)
    {
        var randomId = random.Next(minId, maxId);
        query = query.Where(test => test.Id != randomId);
    }

    return query;
}

Upvotes: 1

JMabee
JMabee

Reputation: 2300

Since the SQL has not changed but it is having issues depending on what platform you run on I would start with your settings. A GREAT reference for the whys and hows is written by Erland Sommarskog this: http://www.sommarskog.se/query-plan-mysteries.html

It is long but I imagine you will find your answer in there.

Upvotes: 0

Related Questions