Adding one more join kills performance

Question

We have a fairly complex query on SQL Server 2005 with these characteristics:

• 11 table joins
• 8 view joins
• 7 non-correlated query joins

All of the joins have a common indexed column.

It normally runs in under 30 seconds, but when adding one more table join (the joined column indexed), it runs seemingly forever.

I noticed that if I remove one particular existing view join, it runs fast again even with the new join.

Below is a modified version of the query with most joins removed but it still demonstrates the performance issue.

--This runs super slow
SELECT 
    L.LoanID    
FROM  dbo.Loan L 
    JOIN dbo.Company C ON c.CompanyKey = l.CompanyKey
    JOIN dbo.Status S  on L.LoanID = S.LoanID
    JOIN dbo.Participation P on L.LoanID = P.LoanID
    JOIN dbo.Delinquent D on  L.LoanID = D.LoanID
    JOIN dbo.Property Pr on Pr.Loanid = L.Loanid
    JOIN dbo.MailingAddress ma ON ma.LoanID = L.LoanID  
    LEFT JOIN dbo.BorrowerPhonePivot bp ON bp.loanid = l.loanid
WHERE s.primstat=1 AND  DATEADD(d,16,L.DueDate) <= C.TransPostDate


--This runs fast
 SELECT 
    L.LoanID    
 FROM  dbo.Loan L 
    JOIN dbo.Company C ON c.CompanyKey = l.CompanyKey
    JOIN dbo.Status S  on L.LoanID = S.LoanID
    JOIN dbo.Participation P on L.LoanID = P.LoanID
    JOIN dbo.Delinquent D on  L.LoanID = D.LoanID
    JOIN dbo.Property Pr on Pr.Loanid = L.Loanid
    --JOIN dbo.MailingAddress ma ON ma.LoanID = L.LoanID    
    LEFT JOIN dbo.BorrowerPhonePivot bp ON bp.loanid = l.loanid
 WHERE s.primstat=1 AND  DATEADD(d,16,L.DueDate) <= C.TransPostDate


 --This runs fast
 SELECT 
    L.LoanID    
 FROM  dbo.Loan L 
    JOIN dbo.Company C ON c.CompanyKey = l.CompanyKey
    JOIN dbo.Status S  on L.LoanID = S.LoanID
    JOIN dbo.Participation P on L.LoanID = P.LoanID
    JOIN dbo.Delinquent D on  L.LoanID = D.LoanID
    JOIN dbo.Property Pr on Pr.Loanid = L.Loanid
    JOIN dbo.MailingAddress ma ON ma.LoanID = L.LoanID  
    --LEFT JOIN dbo.BorrowerPhonePivot bp ON bp.loanid = l.loanid
 WHERE s.primstat=1 AND  DATEADD(d,16,L.DueDate) <= C.TransPostDate

This problem isn't specific to this query as I have encountered this bizarre behavior before in other queries and have never been able to fully solve it. My theory is that SQL Server is hitting an internal query plan limit and goes stupid.

I filtered the query to one state (returned one record but took 10 minutes) in order to generate recommendations via the DTA. I added some needed indexes without any difference.

I examined the execution plan results but didn't find anything out of the ordinary.

Any suggestions on what else to look for?

This is what I have tried:

• DTA - added one needed index out of 4 generated recommendations. (others are already covered)
• MAXDOP 1 to see if it is threading issue per @AaronBertrand
• UPDATE STATISTICS on tables per @AaronBertrand
• Ran sqlplan (pastebin) in SQL sentry plan explorer per @AaronBertrand. The strange this is that the Actual Rows column shows a number that is an order of magnitude larger than reality. Somehow the query plan optimizer is getting confused.

showplan of first query:

  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([L].[LoanID]))
   |--Nested Loops(Inner Join, WHERE:([Expr1056]<=[Service_Prod].[dbo].[Company].[TransPostDate] as [C].[TransPostDate]))
   |    |--Hash Match(Inner Join, HASH:([ma].[LoanID])=([D].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Delinquent].[LoanID] as [D].[LoanID]=[Service_Prod].[dbo].[MailingAddress].[LoanID] as [ma].[LoanID]))
   |    |    |--Hash Match(Inner Join, HASH:([ma].[LoanID])=([P].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Participation].[LoanID] as [P].[LoanID]=[Service_Prod].[dbo].[MailingAddress].[LoanID] as [ma].[LoanID]))
   |    |    |    |--Merge Join(Inner Join, MERGE:([Pr].[LoanID])=([ma].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID]=[Service_Prod].[dbo].[MailingAddress].[LoanID] as [ma].[LoanID]))
   |    |    |    |    |--Merge Join(Inner Join, MERGE:([Pr].[LoanID])=([L].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Loan].[LoanID] as [L].[LoanID]=[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID]))
   |    |    |    |    |    |--Merge Join(Inner Join, MERGE:([Pr].[LoanID])=([S].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Status].[LoanID] as [S].[LoanID]=[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID]))
   |    |    |    |    |    |    |--Index Scan(OBJECT:([Service_Prod].[dbo].[Property].[aaaaaProperty_PK] AS [Pr]), ORDERED FORWARD)
   |    |    |    |    |    |    |--Index Seek(OBJECT:([Service_Prod].[dbo].[Status].[_dta_index_Status_114_1122103038__K13_K1_K2_K3] AS [S]), SEEK:([S].[PrimStat]=(1)) ORDERED FORWARD)
   |    |    |    |    |    |--Compute Scalar(DEFINE:([Expr1056]=dateadd(day,(16),[Service_Prod].[dbo].[Loan].[DueDate] as [L].[DueDate])))
   |    |    |    |    |         |--Index Scan(OBJECT:([Service_Prod].[dbo].[Loan].[_dta_index_Loan_K1_K66_K10_K23] AS [L]), ORDERED FORWARD)
   |    |    |    |    |--Index Scan(OBJECT:([Service_Prod].[dbo].[MailingAddress].[StatusMailingAddress] AS [ma]), ORDERED FORWARD)
   |    |    |    |--Index Scan(OBJECT:([Service_Prod].[dbo].[Participation].[Reference17] AS [P]))
   |    |    |--Index Scan(OBJECT:([Service_Prod].[dbo].[Delinquent].[aaaaaDelinquent_PK] AS [D]))
   |    |--Table Scan(OBJECT:([Service_Prod].[dbo].[Company] AS [C]))
   |--Stream Aggregate(GROUP BY:([l].[LoanID]))
        |--Filter(WHERE:([Service_Prod].[dbo].[Loan].[LoanID] as [l].[LoanID]=[Service_Prod].[dbo].[Loan].[LoanID] as [L].[LoanID]))
             |--Hash Match(Inner Join, HASH:([b].[LoanID], [b].[AssmRecCounter], [b].[BorrowerID])=([bp].[LoanID], [bp].[AssmRecCounter], [bp].[BorrowerID]), RESIDUAL:([Service_Prod].[dbo].[BorrowerPhone].[LoanID] as [bp].[LoanID]=[Service_Prod].[dbo].[Borrower].[LoanID] as [b].[LoanID] AND [Service_Prod].[dbo].[Borrower].[AssmRecCounter] as [b].[AssmRecCounter]=[Service_Prod].[dbo].[BorrowerPhone].[AssmRecCounter] as [bp].[AssmRecCounter] AND [Service_Prod].[dbo].[Borrower].[BorrowerID] as [b].[BorrowerID]=[Service_Prod].[dbo].[BorrowerPhone].[BorrowerID] as [bp].[BorrowerID]))
                  |--Hash Match(Inner Join, HASH:([Service_Prod].[dbo].[Company].[CompanyKey])=([l].[CompanyKey]))
                  |    |--Index Scan(OBJECT:([Service_Prod].[dbo].[Company].[aaaaaCompany_PK]))
                  |    |--Merge Join(Inner Join, MERGE:([s].[LoanID])=([b].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Status].[LoanID] as [s].[LoanID]=[Service_Prod].[dbo].[Borrower].[LoanID] as [b].[LoanID]))
                  |         |--Index Seek(OBJECT:([Service_Prod].[dbo].[Status].[_dta_index_Status_114_1122103038__K13_K1_K2_K3] AS [s]), SEEK:([s].[PrimStat]=(1)) ORDERED FORWARD)
                  |         |--Merge Join(Inner Join, MERGE:([l].[LoanID])=([b].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Loan].[LoanID] as [l].[LoanID]=[Service_Prod].[dbo].[Borrower].[LoanID] as [b].[LoanID] AND [Service_Prod].[dbo].[Loan].[AssmRecCounter] as [l].[AssmRecCounter]=[Service_Prod].[dbo].[Borrower].[AssmRecCounter] as [b].[AssmRecCounter]))
                  |              |--Index Scan(OBJECT:([Service_Prod].[dbo].[Loan].[_dta_index_Loan_K1_K66_K10_K23] AS [l]), ORDERED FORWARD)
                  |              |--Index Scan(OBJECT:([Service_Prod].[dbo].[Borrower].[aaaaaBorrower_PK] AS [b]), ORDERED FORWARD)
                  |--Index Seek(OBJECT:([Service_Prod].[dbo].[BorrowerPhone].[_dta_index_BorrowerPhone_K12_K1_K2_K3_K5] AS [bp]), SEEK:([bp].[ForeignPhone]=(0)),  WHERE:([Service_Prod].[dbo].[BorrowerPhone].[PhoneType] as [bp].[PhoneType]=(0) OR [Service_Prod].[dbo].[BorrowerPhone].[PhoneType] as [bp].[PhoneType]=(1)) ORDERED FORWARD)

showplan of 2nd query:

  |--Parallelism(Gather Streams)
   |--Hash Match(Left Outer Join, HASH:([L].[LoanID])=([l].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Loan].[LoanID] as [l].[LoanID]=[Service_Prod].[dbo].[Loan].[LoanID] as [L].[LoanID]))
        |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([L].[LoanID]))
        |    |--Hash Match(Inner Join, HASH:([Pr].[LoanID])=([D].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Delinquent].[LoanID] as [D].[LoanID]=[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID]))
        |         |--Bitmap(HASH:([Pr].[LoanID]), DEFINE:([Bitmap1065]))
        |         |    |--Hash Match(Inner Join, HASH:([Pr].[LoanID])=([P].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Participation].[LoanID] as [P].[LoanID]=[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID]))
        |         |         |--Bitmap(HASH:([Pr].[LoanID]), DEFINE:([Bitmap1064]))
        |         |         |    |--Hash Match(Inner Join, HASH:([S].[LoanID])=([Pr].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Status].[LoanID] as [S].[LoanID]=[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID]))
        |         |         |         |--Bitmap(HASH:([S].[LoanID]), DEFINE:([Bitmap1063]))
        |         |         |         |    |--Hash Match(Inner Join, HASH:([S].[LoanID])=([L].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Loan].[LoanID] as [L].[LoanID]=[Service_Prod].[dbo].[Status].[LoanID] as [S].[LoanID]))
        |         |         |         |         |--Bitmap(HASH:([S].[LoanID]), DEFINE:([Bitmap1062]))
        |         |         |         |         |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([S].[LoanID]))
        |         |         |         |         |         |--Index Seek(OBJECT:([Service_Prod].[dbo].[Status].[IDX_Status_PrimStat_INCLoanID] AS [S]), SEEK:([S].[PrimStat]=(1)) ORDERED FORWARD)
        |         |         |         |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([L].[LoanID]), WHERE:(PROBE([Bitmap1062],[Service_Prod].[dbo].[Loan].[LoanID] as [L].[LoanID])))
        |         |         |         |              |--Nested Loops(Inner Join, WHERE:([Expr1053]<=[Service_Prod].[dbo].[Company].[TransPostDate] as [C].[TransPostDate]))
        |         |         |         |                   |--Parallelism(Distribute Streams, RoundRobin Partitioning)
        |         |         |         |                   |    |--Table Scan(OBJECT:([Service_Prod].[dbo].[Company] AS [C]))
        |         |         |         |                   |--Compute Scalar(DEFINE:([Expr1053]=dateadd(day,(16),[Service_Prod].[dbo].[Loan].[DueDate] as [L].[DueDate])))
        |         |         |         |                        |--Index Scan(OBJECT:([Service_Prod].[dbo].[Loan].[_dta_index_Loan_K1_K66_K10_K23] AS [L]))
        |         |         |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Pr].[LoanID]))
        |         |         |              |--Index Scan(OBJECT:([Service_Prod].[dbo].[Property].[aaaaaProperty_PK] AS [Pr]),  WHERE:(PROBE([Bitmap1063],[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID])))
        |         |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([P].[LoanID]))
        |         |              |--Index Scan(OBJECT:([Service_Prod].[dbo].[Participation].[Reference17] AS [P]),  WHERE:(PROBE([Bitmap1064],[Service_Prod].[dbo].[Participation].[LoanID] as [P].[LoanID])))
        |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([D].[LoanID]))
        |              |--Index Scan(OBJECT:([Service_Prod].[dbo].[Delinquent].[aaaaaDelinquent_PK] AS [D]),  WHERE:(PROBE([Bitmap1065],[Service_Prod].[dbo].[Delinquent].[LoanID] as [D].[LoanID])))
        |--Stream Aggregate(GROUP BY:([l].[LoanID]))
             |--Sort(ORDER BY:([l].[LoanID] ASC))
                  |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([l].[LoanID]))
                       |--Hash Match(Inner Join, HASH:([b].[LoanID], [b].[AssmRecCounter], [b].[BorrowerID])=([bp].[LoanID], [bp].[AssmRecCounter], [bp].[BorrowerID]), RESIDUAL:([Service_Prod].[dbo].[BorrowerPhone].[LoanID] as [bp].[LoanID]=[Service_Prod].[dbo].[Borrower].[LoanID] as [b].[LoanID] AND [Service_Prod].[dbo].[Borrower].[AssmRecCounter] as [b].[AssmRecCounter]=[Service_Prod].[dbo].[BorrowerPhone].[AssmRecCounter] as [bp].[AssmRecCounter] AND [Service_Prod].[dbo].[Borrower].[BorrowerID] as [b].[BorrowerID]=[Service_Prod].[dbo].[BorrowerPhone].[BorrowerID] as [bp].[BorrowerID]))
                            |--Bitmap(HASH:([b].[LoanID], [b].[AssmRecCounter], [b].[BorrowerID]), DEFINE:([Bitmap1068]))
                            |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([b].[LoanID], [b].[AssmRecCounter], [b].[BorrowerID]))
                            |         |--Hash Match(Inner Join, HASH:([s].[LoanID], [l].[AssmRecCounter])=([b].[LoanID], [b].[AssmRecCounter]), RESIDUAL:([Service_Prod].[dbo].[Status].[LoanID] as [s].[LoanID]=[Service_Prod].[dbo].[Borrower].[LoanID] as [b].[LoanID] AND [Service_Prod].[dbo].[Loan].[AssmRecCounter] as [l].[AssmRecCounter]=[Service_Prod].[dbo].[Borrower].[AssmRecCounter] as [b].[AssmRecCounter]))
                            |              |--Bitmap(HASH:([s].[LoanID], [l].[AssmRecCounter]), DEFINE:([Bitmap1067]))
                            |              |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([s].[LoanID], [l].[AssmRecCounter]))
                            |              |         |--Hash Match(Inner Join, HASH:([Service_Prod].[dbo].[Company].[CompanyKey])=([l].[CompanyKey]))
                            |              |              |--Parallelism(Distribute Streams, Broadcast Partitioning)
                            |              |              |    |--Index Scan(OBJECT:([Service_Prod].[dbo].[Company].[aaaaaCompany_PK]))
                            |              |              |--Hash Match(Inner Join, HASH:([s].[LoanID])=([l].[LoanID]), RESIDUAL:([Service_Prod].[dbo].[Loan].[LoanID] as [l].[LoanID]=[Service_Prod].[dbo].[Status].[LoanID] as [s].[LoanID]))
                            |              |                   |--Bitmap(HASH:([s].[LoanID]), DEFINE:([Bitmap1066]))
                            |              |                   |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([s].[LoanID]))
                            |              |                   |         |--Index Seek(OBJECT:([Service_Prod].[dbo].[Status].[IDX_Status_PrimStat_INCLoanID] AS [s]), SEEK:([s].[PrimStat]=(1)) ORDERED FORWARD)
                            |              |                   |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([l].[LoanID]))
                            |              |                        |--Index Scan(OBJECT:([Service_Prod].[dbo].[Loan].[_dta_index_Loan_K1_K66_K10_K23] AS [l]),  WHERE:(PROBE([Bitmap1066],[Service_Prod].[dbo].[Loan].[LoanID] as [l].[LoanID])))
                            |              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([b].[LoanID], [b].[AssmRecCounter]))
                            |                   |--Index Scan(OBJECT:([Service_Prod].[dbo].[Borrower].[aaaaaBorrower_PK] AS [b]),  WHERE:(PROBE([Bitmap1067],[Service_Prod].[dbo].[Borrower].[LoanID] as [b].[LoanID],[Service_Prod].[dbo].[Borrower].[AssmRecCounter] as [b].[AssmRecCounter])))
                            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([bp].[LoanID], [bp].[AssmRecCounter], [bp].[BorrowerID]))
                                 |--Index Seek(OBJECT:([Service_Prod].[dbo].[BorrowerPhone].[_dta_index_BorrowerPhone_K12_K1_K2_K3_K5] AS [bp]), SEEK:([bp].[ForeignPhone]=(0)),  WHERE:(([Service_Prod].[dbo].[BorrowerPhone].[PhoneType] as [bp].[PhoneType]=(0) OR [Service_Prod].[dbo].[BorrowerPhone].[PhoneType] as [bp].[PhoneType]=(1)) AND PROBE([Bitmap1068],[Service_Prod].[dbo].[BorrowerPhone].[LoanID] as [bp].[LoanID],[Service_Prod].[dbo].[BorrowerPhone].[AssmRecCounter] as [bp].[AssmRecCounter],[Service_Prod].[dbo].[BorrowerPhone].[BorrowerID] as [bp].[BorrowerID])) ORDERED FORWARD)

Answer 1

This is going to sound stupid, but try various combinations and adding all of the join criteria to give the optimizer the best chance at matching. I've seen this aid the optimizer in picking a better plan simply due to the order of the tables and the cardinality.

SELECT ac.AccountNum
    FROM dbo.Account AS ac
    INNER JOIN dbo.Address AS ad
      ON ad.AccountNum = ac.AccountNum
    INNER JOIN dbo.PhoneView AS p
      ON p.AccountNum = ac.AccountNum
      AND p.AccountNum = ad.AccountNum; -- extra help here

Also try in the opposite order:

SELECT ac.AccountNum
    FROM dbo.Account AS ac
    INNER JOIN dbo.PhoneView AS p
      ON p.AccountNum = ac.AccountNum
    INNER JOIN dbo.Address AS ad
      ON ad.AccountNum = p.AccountNum
      AND ad.AccountNum = ac.AccountNum; -- extra help here

I've added the dbo. prefix to each table (which you should get in the habit of doing), to see if specifying those explicitly improve the optimizer's decision (or at least prevent duplicate plans), and used shorter aliases for readability.

Also don't forget to inspect the execution plans - it may be that because of the size/cardinality of some of these tables, you are getting the wrong type of join.

EDIT now that we have query plans, we can see that the bad plan has parallelism all over it. You might try adding MAXDOP 1 to the query, but this is only temporary relief and shouldn't be the real fix. I suspect you are seeing bad cardinality estimates and an improper distribution of work across multiple threads (commonly blamed on CXPACKET waits but they're a symptom not the cause). I would check the statistics for the various tables in the query. If you load the actual execution plan in the free SQL Sentry Plan Explorer, on the Plan Tree tab you will immediately be able to see where the estimated and actual row counts are horribly off, and this should guide you to which table(s) need to have statistics updated. At least, this is probably the cause of horribly performing parallelism about 90% of the time.

A couple of suggestions. Since company only has one row, there is absolutely no need to include company in the query. Instead, pull the cutoff date into a variable, subtract 16 days, now you can use the index on Loan.DueDate (if one exists):

DECLARE @d SMALLDATETIME;

SELECT @d = DATEADD(DAY, -16, TransPostDate) FROM dbo.Company;

SELECT L.LoanID
FROM dbo.Loan AS L
  INNER JOIN dbo.[Status] AS S                 ON L.LoanID  = S.LoanID
  INNER JOIN dbo.Participation AS P            ON L.LoanID  = P.LoanID
  INNER JOIN dbo.Delinquent AS D               ON L.LoanID  = D.LoanID
  INNER JOIN dbo.Property AS Pr                ON Pr.LoanID = L.LoanID
  INNER JOIN dbo.MailingAddress AS ma          ON ma.LoanID = L.LoanID  
  LEFT OUTER JOIN dbo.BorrowerPhonePivot AS bp ON bp.loanid = L.loanid
WHERE 
  s.primstat = 1 
  AND L.DueDate <= @cutoff;

Since you are only returning LoanID, doesn't this do the same thing?

DECLARE @d SMALLDATETIME;

SELECT @d = DATEADD(DAY, -16, TransPostDate) FROM dbo.Company;

SELECT L.LoanID
FROM dbo.Loan AS L
  WHERE L.DueDate <= @cutoff
  AND EXISTS (SELECT 1 FROM dbo.Status         WHERE LoanID = L.LoanID AND primstat = 1)
  AND EXISTS (SELECT 1 FROM dbo.Participation  WHERE LoanID = L.LoanID)
  AND EXISTS (SELECT 1 FROM dbo.Delinquent     WHERE LoanID = L.LoanID)
  AND EXISTS (SELECT 1 FROM dbo.Property       WHERE LoanID = L.LoanID)
  AND EXISTS (SELECT 1 FROM dbo.MailingAddress WHERE LoanID = L.LoanID);

This is just a start, and is certainly not going to solve all of your problems. I have to believe that you don't really want all of the rows in some of those joined tables.

Answer 2

I suspect @AaronBertrand is correct about the statistics being out of date.

One thing I would do is remove the one implicit join. I have noticed that wonky things can happen when you mix implicit and explicit joins (although more in the line of bad results sets than performance, but still it could contribute to performance in a complicated query by making the optimizer chose something less than optimal).

I also want to warn you that views can cause slowness especially if they are views that call views. I have seen queries speeded up a lot by looking at the views and seeing if you can replace them with direct code in the query. This may be especially true if each view calls the same base tables and when views call views. So if updating the statistics doesn't help, look at replacing the views.

Answer 3

I have two suggestions:

The first is to use the SQL Server Database Engine Tuning Advisor to check for suggestions. It may detect indexes that need to be added that will increase the performance, and are not obvious.

The second option is one that will only work if you can deal with slightly stale data, or you can trigger the process I'm about to describe to happen when data is updated. It won't work for data that's updated on a regular basis (several times an hour or so). In our case, it's OK if the data is accurate within 24 hours, so we can run the process I'm about to describe once per day. if you don't have that luxury, ignore the rest.

We ran into a similar situation, and no matter what we did, we were unable to increase the performance. The joins were just too many, on too many large tables. It took five minutes or so to return 431 rows from the view.

So we took a leaf from our BI (Business Intelligence) team's approach, and built de-normalized tables based on our normalized data. We essentially created a mini-Data Warehouse.

Our view (which takes forever to run) is named "vw_StoreData", and we export it using the statements

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmpCompiledStoreData]') AND type in (N'U'))
DROP TABLE [dbo].[tmpCompiledStoreData]


Select * Into tmpCompiledStoreData FROM vw_StoreData

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompiledStoreData]') AND type in (N'U'))
DROP TABLE [dbo].[CompiledStoreData]


exec sp_rename 'tmpCompiledStoreData', 'CompiledStoreData'

GRANT SELECT ON [dbo].[CompiledStoreData] TO [SomeUser]
GRANT SELECT ON [dbo].[CompiledStoreData] TO [SomeOtheruser]
...etc

The resulting table, CompiledStoreData" is lightning fast to query on, compared to the view. Again, creating the de-normalized table from a view will only work if your data freshness needs aren't real-time.