CODEWITHSUNDEEP
sqlsql-server-2008t-sqlview
Splendor
Splendor

Reputation: 1396

How to correct poor performance in T-SQL view

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

Upvotes: 1

Views: 2156

Answers (1)

Splendor
Splendor

Reputation: 1396

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.sstatic.net/mr3IJ.png

Upvotes: 1

Related Questions