CODEWITHSUNDEEP
sqlsql-serverperformancejoinsql-view
igorjrr
igorjrr

Reputation: 892

SQL joins with views

I have a view that is now joining two other views with some extra tables. It's very slow.

My experience tell me it's because views are not indexed by default. I tried to create an index on each of them, but it's not possible since they have self joins or inner queries.

My question is: It appears to me that in general the join of views is not recommended. So, in short, there is no way to reuse a code from one view into another? Example: the view A calculates the percentage and the view B calculates something else that uses the percentage from view A plus other information from other tables/views. What would be the best approach? Do you really have to replicate the code from view A to view B so it uses the original table's indexes?

Views (simplified view, to show the issue):

View A (calculates the percentage):

SELECT     dbo.tblPopAgeGrp.RevID, dbo.tblPopAgeGrp.VarID, dbo.tblPopAgeGrp.LocID, 
                      dbo.tblPopAgeGrp.TimeID, dbo.tblPopAgeGrp.AgeID, tPAGT.AgeID AS AgeTotal, 
                      100 * dbo.tblPopAgeGrp.PopMale / tPAGT.PopMale AS PopMalePerc, 
                      100 * dbo.tblPopAgeGrp.PopFemale / tPAGT.PopFemale AS PopFemalePerc, 
                      100 * dbo.tblPopAgeGrp.PopTotal / tPAGT.PopTotal AS PopTotalPerc
FROM         dbo.tblPopAgeGrp
INNER JOIN   dbo.tblPopAgeGrp tPAGT
        ON dbo.tblPopAgeGrp.GroupID = tPAGT.GroupID
        AND dbo.tblPopAgeGrp.AgeID = 700

View A by itself, since there so many records, takes a long time to execute. However, in view B the records are filtered according to the VersionID.

View B (gets the percentage from view A with additional info from another view):

SELECT     vPAGP.VersionID, 
           vPAGP.LocationID AS LocID, 
           vPAGP.PopTotalPerc  AS pPopTot,
           vPAGP.PopMalePerc   AS pMale,
           vPAGP.PopFemalePerc AS pFemale,
           vPAGPSR.PopMaleSexRatio    AS SexRatio,
           vPAGPSR.PopFemaleSexRatio  AS FemRatio
FROM       dbo.vwA AS vPAGP
INNER JOIN dbo.vwOther AS vPAGPSR
           ON vPAGPSR.GroupID = vPAGP.GroupID
WHERE vPAGP.VersionID=10

Executing View A without filters, takes like 10 minutes. Executing it for VersionID=10 only, it executes in 10 seconds. The view vwOther executes very quickly.

Thanks!

Upvotes: 0

Views: 2879

Answers (1)

Larry Lustig
Larry Lustig

Reputation: 50970

You are not correct when you state "It appears to me that in general the join of views is not recommended."

Views can be combined with other views and will perform well provided that all JOINs are optimizable and have the appropriate index created and any filtering done within the view is optimizable and has appropriate indexes created.

A view based on other views should perform as well as the same query written to factor out the views. If you want further help, please post the definition of all views involved in your problem.

Upvotes: 2

Related Questions