performance significantly reduced when inner join table and views

I have three views in my sql server:

CREATE VIEW [dbo].[v_CompanyLabelCreate]  
AS   
SELECT   
c.Id AS Id,  
c.[Name] AS CompanyName,   
CASE WHEN r.ApprovedPatentCreativeApplication > 2 OR r.ApprovedCopyRightBookApplication + r.ApprovedCopyRightSoftwareApplication > 4 OR NumberInProduction + NumberNotInProduction > 10 THEN N'创造型'  
ELSE NULL END AS Label  
FROM Company c INNER JOIN ResearchInfo r ON c.Id = r.CompanyID  
WHERE c.Submitted = 1



CREATE VIEW [dbo].[v_CompanyLabelHighPotential]  
AS   
SELECT   
c.Id AS Id,  
c.[Name] AS CompanyName,   
CASE WHEN si1.Income >= 1000000 AND si2.Income <> 0 AND ((si1.Income -si2.Income)/si2.Income) >= 1 THEN N'潜力型'  
     WHEN n1.TotalProfit >= 500000 AND n2.TotalProfit <> 0 AND ((n1.TotalProfit - n2.TotalProfit)/n2.TotalProfit) >= 1 THEN N'潜力型'  
     WHEN nfa1.AssetsTotal >= 5000000 AND nfa2.AssetsTotal <> 0 AND ((nfa1.AssetsTotal - nfa2.AssetsTotal)/nfa2.AssetsTotal) >= 1 THEN N'潜力型'  
ELSE NULL END AS Label  
FROM Company c INNER JOIN SaleIncome si1 ON c.Id = si1.CompanyID AND si1.YearQuarterID = 2  
           INNER JOIN SaleIncome si2 ON c.Id = si2.CompanyID AND si2.YearQuarterID = 1  
           INNER JOIN NetProfit n1 ON c.Id = n1.CompanyID AND n1.YearQuarterID = 2  
  INNER JOIN NetProfit n2 ON c.Id = n2.CompanyID AND n2.YearQuarterID = 1  
  INNER JOIN NonFloatingAsset nfa1 ON c.Id = nfa1.CompanyID AND nfa1.YearQuarterID = 2  
  INNER JOIN NonFloatingAsset nfa2 ON c.Id = nfa2.CompanyID AND nfa2.YearQuarterID = 1  
WHERE c.Submitted = 1




CREATE VIEW [dbo].[v_CompanyLabelHighTotalPolicies]  
AS  
SELECT   
c.Id AS Id,   
c.[Name] AS CompanyName,   
CASE WHEN p.NumberOfPolicies >= 4 AND cp.PolicyID IS NOT NULL THEN N'政策大户'  
ELSE NULL END AS Label  
FROM Company c LEFT JOIN (SELECT CompanyID, COUNT(*) AS NumberOfPolicies FROM CompanyPolicy GROUP BY CompanyID) p ON c.Id = p.CompanyID  
           LEFT JOIN (SELECT CompanyID, PolicyID FROM CompanyPolicy WHERE PolicyID = 7) cp ON c.Id = cp.CompanyID  
WHERE c.Submitted = 1

When I run a select query which inner joins these three views:

select * 
from  v_CompanyLabelCreate clc 
  INNER JOIN v_CompanyLabelHighPotential clhp ON clc.Id = clhp.Id  
  INNER JOIN v_CompanyLabelHighTotalPolicies chtp ON clc.Id = chtp.Id  

It is fast and smooth. However, when I try to run a select query which inner join these three views with some other tables:

select * 
FROM Company c 
  INNER JOIN BasicInfo b ON c.Id = b.CompanyID  
  INNER JOIN CompanyDimension cd ON c.Id = cd.CompanyID  
  INNER JOIN TotalProfit t ON c.Id = t.CompanyID AND t.YearQuarterID = 2  
  INNER JOIN SaleCost sc ON c.Id = sc.CompanyID AND sc.YearQuarterID = 2  
  INNER JOIN CompanyFinancialIndex cfi ON c.Id = cfi.CompanyID AND cfi.YearQuarterID = 6  
  INNER JOIN SaleIncome si1 ON c.Id = si1.CompanyID AND si1.YearQuarterID = 2  
  INNER JOIN SaleIncome si2 ON c.Id = si2.CompanyID AND si2.YearQuarterID = 1  
  INNER JOIN NetProfit n1 ON c.Id = n1.CompanyID AND n1.YearQuarterID = 2  
  INNER JOIN NetProfit n2 ON c.Id = n2.CompanyID AND n2.YearQuarterID = 1  
  INNER JOIN v_CompanyLabelCreate clc ON c.Id = clc.Id  
  INNER JOIN v_CompanyLabelHighPotential clhp ON clc.Id = clhp.Id  
  INNER JOIN v_CompanyLabelHighTotalPolicies chtp ON clc.Id = chtp.Id  
WHERE c.Submitted = 1

It looks like it will take forever to get the result (Download the execution plan here: the execution plan). Now, if I just run the query without inner joining the three views like this:

select * 
FROM Company c 
  INNER JOIN BasicInfo b ON c.Id = b.CompanyID  
  INNER JOIN CompanyDimension cd ON c.Id = cd.CompanyID  
  INNER JOIN TotalProfit t ON c.Id = t.CompanyID AND t.YearQuarterID = 2  
  INNER JOIN SaleCost sc ON c.Id = sc.CompanyID AND sc.YearQuarterID = 2  
  INNER JOIN CompanyFinancialIndex cfi ON c.Id = cfi.CompanyID AND cfi.YearQuarterID = 6  
  INNER JOIN SaleIncome si1 ON c.Id = si1.CompanyID AND si1.YearQuarterID = 2  
  INNER JOIN SaleIncome si2 ON c.Id = si2.CompanyID AND si2.YearQuarterID = 1  
  INNER JOIN NetProfit n1 ON c.Id = n1.CompanyID AND n1.YearQuarterID = 2  
  INNER JOIN NetProfit n2 ON c.Id = n2.CompanyID AND n2.YearQuarterID = 1  
WHERE c.Submitted = 1

It is fast and smooth again.

About the tables used:

Company : A table which stores company Ids and company names. Of course c.Id is the PK and it will be referred to as Company ID later.

ResearchInfo , BasicInfo , CompanyDimension , CompanyFinancialIndex : CompanyID is foreign key, every company only has at most one row in this table.

SaleIncome, SaleCost, NetProfit, NonFloatingAsset, TotalProfit : the combination of CompanyID and YearQuarterID work as Primary Key, although every table has an identity field which is defined as the PK.

---

You also can have a missed index issue, but your query is a bit complex. You can try to reduce the complexity using cte table like (You need the field names be unique in CTEs):

WITH cte_tb1 
     AS (SELECT * -- UNIQUE NAMES FIELDS IS REQUIRED 
         FROM   company c 
                INNER JOIN basicinfo b 
                        ON c.id = b.companyid 
                INNER JOIN companydimension cd 
                        ON c.id = cd.companyid 
                INNER JOIN totalprofit t 
                        ON c.id = t.companyid 
                           AND t.yearquarterid = 2 
                INNER JOIN salecost sc 
                        ON c.id = sc.companyid 
                           AND sc.yearquarterid = 2 
                INNER JOIN companyfinancialindex cfi 
                        ON c.id = cfi.companyid 
                           AND cfi.yearquarterid = 6 
                INNER JOIN saleincome si1 
                        ON c.id = si1.companyid 
                           AND si1.yearquarterid = 2 
                INNER JOIN saleincome si2 
                        ON c.id = si2.companyid 
                           AND si2.yearquarterid = 1 
                INNER JOIN netprofit n1 
                        ON c.id = n1.companyid 
                           AND n1.yearquarterid = 2 
                INNER JOIN netprofit n2 
                        ON c.id = n2.companyid 
                           AND n2.yearquarterid = 1 
         WHERE  c.submitted = 1), 
     cte_tb2 
     AS (SELECT * -- UNIQUE NAMES FIELDS IS REQUIRED 
         FROM   v_companylabelcreate clc 
                INNER JOIN v_companylabelhighpotential clhp 
                        ON clc.id = clhp.id 
                INNER JOIN v_companylabelhightotalpolicies chtp 
                        ON clc.id = chtp.id) 
SELECT * 
FROM   cte_tb1 a 
       INNER JOIN cte_tb2 b 
               ON a.id = b.id 

链接地址: http://www.djcxy.com/p/15136.html

上一篇: c ++编译问题

下一篇: 内部连接表和视图时性能显着降低