How to correct poor performance in T-SQL view

Question

I have an ASP.NET MVC application that is using a T-SQL view I created as part of its data model. I'm able to pull in the view data but I'm getting very poor performance.

If I do a SELECT * on the view in SQL Server 2008 it takes over 20 minutes to return all 187,101 of the rows.

If I do a SELECT TOP 1000000 on the view I get the same 187,101 rows returned in 2 seconds.

So I included the TOP statement in my view design to try to get better performance in the ASP.NET MVC application but it still takes ~6 seconds to load the results in a WebGrid.

There's obviously something in my view design that is killing performance but I'm not sure how to fix it. I read about clustered index views but I can't schema bind the view because the tables need to be able to be modified.

Here is the SELECT statement I used to create my view:

SELECT TRD.Description AS Pbm,
       MV.ExtractHistoryMemberValueId,
       MV.FieldDescription,
       MV.FieldValue,
       HM.ExtractHistoryMemberId,
       HM.CaseNum,
       HM.CertNum,
       HM.ClmtNum,
       HM.PlanNum,
       EH.ExtractHistoryId,
       EH.ExtractDate
FROM OutgoingAccumulators.ExtractHistoryMemberValues AS MV
    INNER JOIN OutgoingAccumulators.ExtractHistoryMembers AS HM
        ON (MV.ExtractHistoryMember = HM.ExtractHistoryMemberId)
    INNER JOIN OutgoingAccumulators.ExtractHistories AS EH
        ON (HM.ExtractHistory = EH.ExtractHistoryId)
    INNER JOIN OutgoingAccumulators.Extracts AS EX
        ON (EH.Extract = EX.ExtractId)
    INNER JOIN Accumulators.Interfaces AS INF
        ON (EX.Interface = INF.InterfaceId)
    INNER JOIN Accumulators.TradingPartners AS TRD
        ON (INF.TradingPartner = TRD.TradingPartnerId)

Here is an image of the view design:

view

Are there indexes on all the fields being joined upon? Do you REALLY need all the columns? — Mark Canlas, May 24 '13 at 17:20
@MarkCanlas Unfortunately, I really do have to grab all of these columns. I'm not 100% sure about the index portion of your question; there are primary/foreign key relationships on each field being joined. — Splendor, May 24 '13 at 17:25
FKs imply indexes, so the answer is yes. I would try do start your queries with just the base table and then tack on one join at a time to see where your query begins to suffer. Is there any lock contention going on, simultaneous reads and writes to the same tables? Are you chaining together multiple one-to-many queries? — Mark Canlas, May 24 '13 at 17:28
Side question: Why do you need 187000 rows in a grid view? That's a lot of data to push from the server... — Sparky, May 24 '13 at 17:33
What about linking up the view with a grid control like Telerik RadGrid? It's got pretty smart handling of paging for large datasets, it might help the performance some. — Darth Continent, May 24 '13 at 17:52
It is going to take a few seconds to load 187,101 into a WebGrid. — paparazzo, May 24 '13 at 17:55
The WebGrid is performing efficient paging via ajax. It's currently only retrieving 25 rows at a time. But the web performance is still effected by the raw view performance. — Splendor, May 24 '13 at 17:59
OK, need to get the query ajax is sending and view the query plan. Top X is not really the way to fix your view. Most likely the top x is causing a different type of join. See what that is and put the join type (hash, loop, merge) in the view. — paparazzo, May 24 '13 at 18:38
@MarkCanlas - FK imply indexes, but they aren't automatically put on by default. See of the other SO posts on this [here](http://stackoverflow.com/a/3650713/685760) and [here](http://stackoverflow.com/a/2979826/685760) — Mr Moose, May 27 '13 at 03:25
Please post an "Actual Execution Plan" for this query. There's a very limited amount that we can do without this information. — RBarryYoung, May 27 '13 at 03:55
@RBarryYoung Here's the execution plan: http://i.imgur.com/Jvc0pMn.png — Splendor, May 28 '13 at 21:22
@Splendor I am looking at it now, do you have one the the TOP version of your query also? — RBarryYoung, May 29 '13 at 02:35

score 1 · Accepted Answer · answered May 29 '13 at 14:20

I found a solution. I added a non-clustered index to the ExtractHistoryMemberValues table like this:

USE [ProcessingDB]
GO
CREATE NONCLUSTERED INDEX [IX_ExtractHistoryMember]
ON [OutgoingAccumulators].[ExtractHistoryMemberValues] ([ExtractHistoryMember])
INCLUDE ([ExtractHistoryMemberValueId],[FieldDescription],[FieldValue])
GO

The view now returns all of the rows in under 2 seconds.

Here is the new execution plan: https://i.stack.imgur.com/mr3IJ.png

How to correct poor performance in T-SQL view

1 Answers1