How to improve the performance of a LINQ query

Question

I have a query that calls from a view - TheSQLView - and joins on a table - TheSQLTable - to get a list of results from that view. If I were to get every single record from the view, there would be ~2000 results. That alone takes about 40 seconds to execute. Even if I use a where clause that would return 40 results, it still takes about 8 seconds.

Here is some code:

using (var db = new SQLEntities(_connStr))
{
   var query = from x in db.TheSQLView
               join y in db.TheSQLTable on x.ID equals y.ID
               select new BSTProject()
               {
                  ID = x.ID,
                  Name = x.Name,
                  ... snipped for brevity (about 12 more properties) ...
                  // The two joined properties
                  PctCompleted = y.PctCompleted == null ? 0 : y.PctCompleted,
                  IsCompleted = y.IsCompleted
               };

   return query.toList();
}

I'm assuming it might be something to do with joining the view on the table?

Edit 1: Ran the SQL Profiler and this query is what's being executed:

SELECT 
[Project1].[IsCompleted] AS [IsCompleted], 
[Project1].[ID] AS [ID], 
[Project1].[Name] AS [Name], 
[Project1].[org_code] AS [org_code], 
[Project1].[GroupName] AS [GroupName], 
[Project1].[clnt_name] AS [clnt_name], 
[Project1].[start_date] AS [start_date], 
[Project1].[PRIcode] AS [PRIcode], 
[Project1].[PRI] AS [PRI], 
[Project1].[PMcode] AS [PMcode], 
[Project1].[PM] AS [PM], 
[Project1].[DPMcode] AS [DPMcode], 
[Project1].[DPM] AS [DPM], 
[Project1].[ADMcode] AS [ADMcode], 
[Project1].[ADM] AS [ADM], 
[Project1].[PhaseBudgetsTotal] AS [PhaseBudgetsTotal], 
[Project1].[PhasesEffortTotal] AS [PhasesEffortTotal], 
[Project1].[PhasesBudgetRemaining] AS [PhasesBudgetRemaining], 
[Project1].[PhasesOver] AS [PhasesOver], 
[Project1].[C1] AS [C1]
FROM ( SELECT 
    [Extent1].[org_code] AS [org_code], 
    [Extent1].[GroupName] AS [GroupName], 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[clnt_name] AS [clnt_name], 
    [Extent1].[start_date] AS [start_date], 
    [Extent1].[PRIcode] AS [PRIcode], 
    [Extent1].[PRI] AS [PRI], 
    [Extent1].[PMcode] AS [PMcode], 
    [Extent1].[PM] AS [PM], 
    [Extent1].[DPMcode] AS [DPMcode], 
    [Extent1].[DPM] AS [DPM], 
    [Extent1].[ADMcode] AS [ADMcode], 
    [Extent1].[ADM] AS [ADM], 
    [Extent1].[PhaseBudgetsTotal] AS [PhaseBudgetsTotal], 
    [Extent1].[PhasesEffortTotal] AS [PhasesEffortTotal], 
    [Extent1].[PhasesBudgetRemaining] AS [PhasesBudgetRemaining], 
    [Extent1].[PhasesOver] AS [PhasesOver], 
    [Extent2].[IsCompleted] AS [IsCompleted], 
    CASE WHEN ([Extent2].[PctCompleted] IS NULL) THEN cast(0 as decimal(18)) ELSE [Extent2].[PctCompleted] END AS [C1]
    FROM  (SELECT 
[TheSQLView].[org_code] AS [org_code], 
[TheSQLView].[GroupName] AS [GroupName], 
[TheSQLView].[ID] AS [ID], 
[TheSQLView].[Name] AS [Name], 
[TheSQLView].[clnt_name] AS [clnt_name], 
[TheSQLView].[start_date] AS [start_date], 
[TheSQLView].[PRIcode] AS [PRIcode], 
[TheSQLView].[PRI] AS [PRI], 
[TheSQLView].[PMcode] AS [PMcode], 
[TheSQLView].[PM] AS [PM], 
[TheSQLView].[DPMcode] AS [DPMcode], 
[TheSQLView].[DPM] AS [DPM], 
[TheSQLView].[ADMcode] AS [ADMcode], 
[TheSQLView].[ADM] AS [ADM], 
[TheSQLView].[PhaseBudgetsTotal] AS [PhaseBudgetsTotal], 
[TheSQLView].[PhasesEffortTotal] AS [PhasesEffortTotal], 
[TheSQLView].[PhasesBudgetRemaining] AS [PhasesBudgetRemaining], 
[TheSQLView].[PhasesOver] AS [PhasesOver]
FROM [dbo].[TheSQLView] AS [TheSQLView]) AS [Extent1]
    INNER JOIN (SELECT 
[TheSQLTable].[ID] AS [ID], 
[TheSQLTable].[IsCompleted] AS [IsCompleted], 
[TheSQLTable].[PctCompleted] AS [PctCompleted]
FROM [dbo].[TheSQLTable] AS [TheSQLTable]) AS [Extent2] ON [Extent1].[ID] = [Extent2].[ID]
)  AS [Project1]
ORDER BY [Project1].[ID] ASC

Have a look with SQL Server Profiler to find out which query is really executed. — Ruban J, May 13 '16 at 13:04
Get a SQL profiler, see what exact SQL is executed. Try running it and see the execution plan. Reasons for slowness are infinite - I'm afraid we won't be able to help much. — trailmax, May 13 '16 at 13:04
The view seems to be the problem. So the LINQ part here is unrelated. Show the view's sql query and tell us more about your db schema. — Tim Schmelter, May 13 '16 at 13:05
An SQL server often gets fragmented and queries times becomes random. Try having admin compact the database and try again. — jdweng, May 13 '16 at 13:07
The query seems fine, main points of interest could be the view and the join, are the ID fields primary keys? Is there an index on them? — Lud, May 13 '16 at 13:07
if you really are using a View, change that, as they are notoriously slow. — user1666620, May 13 '16 at 13:28
The OP has been updated with the results from the SQL profiler — Daath, May 13 '16 at 13:35
OP=Original Poster. You mean "The _question_ has been updated..." — D Stanley, May 13 '16 at 13:38
No table schema, no view definition, no indexes, no answer. Do you have proper indexes on the join columns? — Panagiotis Kanavos, May 13 '16 at 13:39
You may need to have a good DBA look at the query plan to determine what the bottleneck is. It's possible that adding an index would help but we'd have to know the definition of `TheSQLView` to be sure. — D Stanley, May 13 '16 at 13:40
@jdweng there is no such thing as compacting a database. SQL Server is a *server*, not a file database like Access. In fact, "compacting", ie shrinking the database will harm performance — Panagiotis Kanavos, May 13 '16 at 13:41
Would executing a stored procedure instead improve performance? — Daath, May 13 '16 at 13:41
@AnthonyMascian no. SPs aren't a magic go-fast technique. In fact, they don't offer any benefits over any other parameterized query. Obviously there are missing indexes and a problem with the view query — Panagiotis Kanavos, May 13 '16 at 13:42
What does the view do? Is the query equally slow when you run it from SQL Server's Management studio? What is its execution plan ? (Hit Ctrl+L to see an estimated execution plan in SSMS) — Panagiotis Kanavos, May 13 '16 at 13:43
@PanagiotisKanavos The execution in SQL takes less than a second. And as for the execution plan, do you want an actual screenshot? There are a lot of merge joins and remote queries — Daath, May 13 '16 at 13:49
We compact the SQL Server bases all the time and it improves performance significantly. SQL Servers are know to get badly fragmented. When we have issues the access times go from a few seconds to 30 minutes. — jdweng, May 13 '16 at 13:56
@jdweng *compacting*, ie *shrinkiing* the database only forces it to reallocate space when it grows. And *that* can lead to fragmentation, if the growth factor is small. It doesn't defragment. High fragmentation could also mean there are design problems. GUID primary keys perhaps? Too many full scans instead of index seeks? SQL Server is *not* known to get "badly fragmented" as explained in Brent Ozar's [Stop worrying about SQL Server fragmentation](https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/) — Panagiotis Kanavos, May 13 '16 at 15:37
@jdweng to summarize Brent Ozar's article, you are temporarily fixing the *symptoms* for your servers bad performance by defragmentation but never solve the real problems — Panagiotis Kanavos, May 13 '16 at 15:41
Don't agree. Where every 6 months we get poor performance where report take 1/2 hour instead of seconds doesn't sound like we have an issue with database. SQL Server will create multiple views when database gets fragmented and only way of eliminating the issue is defrag. — jdweng, May 13 '16 at 16:22

score 0 · Answer 1 · edited May 23 '17 at 11:44

There is precious little info in your question. The generated SQL is nice, but it would be even nicer had you also posted the cpu + logical read counters from SQL Profiler.

But, I will take a wild stab at an answer: Look into indexed views (a clustered index no less). I strongly suspect your view doesn't have one. Look here for some links to get you going: https://stackoverflow.com/a/3986540/1736944

One caveat: If you are not using the Enterprise edition of SQL Server, then your query must contain the "WITH (NOEXPAND)" hint. Simply create a second view that selects all rows from the first view and refer to this second view in your EF query. Confirm with SQL Profiler + query plan that you get a reasonable query plan.

How to improve the performance of a LINQ query

1 Answers1