Databases
StackOverflow2013
Postsdbo
POHow to correct poor performance in T-SQL view
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to correct poor performance in T-SQL view
    primarykey
    data
    text
    <p>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.</p> <p>If I do a <code>SELECT *</code> on the view in SQL Server 2008 it takes over 20 minutes to return all 187,101 of the rows.</p> <p>If I do a <code>SELECT TOP 1000000</code> on the view I get the same 187,101 rows returned in 2 seconds.</p> <p>So I included the <code>TOP</code> 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.</p> <p>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.</p> <p>Here is the <code>SELECT</code> statement I used to create my view:</p> <pre><code>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) </code></pre> <p>Here is an image of the view design:</p> <p><img src="https://i.stack.imgur.com/W8u3x.png" alt="view"></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USSplendor
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to correct poor performance in T-SQL view
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre there indexes on all the fields being joined upon? Do you REALLY need all the columns?
      singulars
      1. POHow to correct poor performance in T-SQL view
      1. USMark Canlas
    2. CO@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.
      singulars
      1. POHow to correct poor performance in T-SQL view
      1. USSplendor
    3. COFKs 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?
      singulars
      1. POHow to correct poor performance in T-SQL view
      1. USMark Canlas
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload