CODEWITHSUNDEEP
sql-serverentity-frameworkt-sqlasp.net-core
nam
nam

Reputation: 23749

EF: Query quite slow on first execution

We are using EF Core 1.1, in an ASP.NET Core app, where the following LINQ query takes about 45 seconds to a minute on its first execution. After the first execution, the subsequent executions seem to work fine.

Question: How can we improve the performance of this query. User waiting for about 45 seconds or more gives him/her an impression that probably the ASP.NET page displaying the query is broken and user moves on to another page:

var lstProjects = _context.Projects.Where(p => p.ProjectYear == FY && p.User == User_Name).OrderBy(p => p.ProjectNumber).ToList();

Execution Plan in SQL Server Query Editor: The table has 24 columns one of which is of type varchar(255), four are of type varchar(75). Others are of types int, smalldatetime, bit etc. All of the columns are needed in the query. But the WHERE clause filters the data to return about 35 rows out of about 26,000.

enter image description here

More details on Execution Plan

enter image description here

Upvotes: 0

Views: 170

Answers (1)

Steve Py
Steve Py

Reputation: 34698

Updated comment to answer.

When using Code First there still needs to be a consideration for indexing based on the common queries run in high-traffic areas of the application. The index scan across the PK amounts to little more than a table scan so an index across the Project Year + UserName would give a boost in performance and should be considered if this is expected to be used a bit or is performance sensitive. Regardless of DB First or Code First, developers need to consider profiler results against the database in order to optimize indexing based on realistic usage. Normally the execution plan will return back suggestions for indexing. Index suggestions should appear just below the SQL statement in the execution plan. From the screen shot it isn't clear whether one was suggested or not, as there might have been a scrollbar to the right of the SQL under the pop-up stats.

In cases where a query is returning slow results but no suggestions, try re-running the query with altered parameters with the execution plan to exclude SQL from picking up pre-compiled queries.

Upvotes: 1

Related Questions