[Solved] Improve performance of a view

EverSQL Database Performance Knowledge Base

Improve performance of a view

I would like to improve the performance of the view All_Risks, can anyone help me to do this?

The select statement that builds the view is as follows

SELECT     
   dbo.Risks_RiskDataNew.RiskID, dbo.Risks_RiskDataNew.HRASnapshotId, 
   dbo.Risks_RiskDataNew.RiskDate, dbo.Risks_RiskDataNew.RiskTypeDescription, 
   dbo.Risks_RiskDataNew.AgillicUserId, dbo.Risks_RiskDataNew.SequenceNo, 
   dbo.Risks_RiskDataNew.RiskValue, dbo.Risks_RiskDataNew.RiskLevelDescription, 
   dbo.BE_RISKTYPE.RiskName, dbo.BE_RISKTYPE.RiskTypeCatagory, 
   dbo.Risks_RiskDataNew.OrganisationID
FROM         
   dbo.Risks_RiskDataNew 
INNER JOIN
   dbo.BE_RISKTYPE ON dbo.Risks_RiskDataNew.RiskTypeDescription = dbo.BE_RISKTYPE.RiskTypeDescription AND EXISTS
                          (SELECT 1 AS Expr1
                           FROM dbo.BE_CompanyInfo
                           WHERE (dbo.Risks_RiskDataNew.OrganisationID = OrganisationID))

It has been suggested I bring the code from the view it references Risk_RiskDataNew into the view All_Risks

This is the select statement from the view dbo.Risks_RiskDataNew

SELECT     
   BE_Risks_Sequenced_1.RiskID, BE_Risks_Sequenced_1.HRASnapshotId, 
   BE_Risks_Sequenced_1.RiskDate, BE_Risks_Sequenced_1.RiskTypeDescription, 
   BE_Risks_Sequenced_1.AgillicUserId, BE_Risks_Sequenced_1.SequenceNo, 
   BE_Risks_Sequenced_1.RiskValue, BE_Risks_Sequenced_1.RiskLevelDescription, 
   BE_Risks_Sequenced_1.OrganisationID
FROM         
   dbo.BE_Risks_Sequenced 
INNER JOIN
   dbo.BE_Risks_Sequenced AS BE_Risks_Sequenced_1 ON dbo.BE_Risks_Sequenced.RiskTypeDescription = BE_Risks_Sequenced_1.RiskTypeDescription 
                                                  AND dbo.BE_Risks_Sequenced.AgillicUserId = BE_Risks_Sequenced_1.AgillicUserId 
                                                  AND dbo.BE_Risks_Sequenced.SequenceNo = BE_Risks_Sequenced_1.SequenceNo + 1
WHERE     
   (dbo.BE_Risks_Sequenced.RiskValue = 1) 
   AND (dbo.BE_Risks_Sequenced.RiskLevelDescription NOT LIKE BE_Risks_Sequenced_1.RiskLevelDescription)

UNION ALL

SELECT     
    RiskID, HRASnapshotId, RiskDate, RiskTypeDescription, AgillicUserId, 
    SequenceNo, RiskValue, RiskLevelDescription, OrganisationID
FROM
    dbo.BE_Risks_Sequenced
WHERE     
    (SequenceNo = 1) AND (RiskValue > 1)

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `BE_RISKTYPE` ADD INDEX `be_risktype_idx_risktypedescription` (`RiskTypeDescription`);
The optimized query:
SELECT
        dbo.Risks_RiskDataNew.RiskID,
        dbo.Risks_RiskDataNew.HRASnapshotId,
        dbo.Risks_RiskDataNew.RiskDate,
        dbo.Risks_RiskDataNew.RiskTypeDescription,
        dbo.Risks_RiskDataNew.AgillicUserId,
        dbo.Risks_RiskDataNew.SequenceNo,
        dbo.Risks_RiskDataNew.RiskValue,
        dbo.Risks_RiskDataNew.RiskLevelDescription,
        dbo.BE_RISKTYPE.RiskName,
        dbo.BE_RISKTYPE.RiskTypeCatagory,
        dbo.Risks_RiskDataNew.OrganisationID 
    FROM
        dbo.Risks_RiskDataNew 
    INNER JOIN
        dbo.BE_RISKTYPE 
            ON dbo.Risks_RiskDataNew.RiskTypeDescription = dbo.BE_RISKTYPE.RiskTypeDescription 
            AND EXISTS (
                SELECT
                    1 AS Expr1 
            FROM
                dbo.BE_CompanyInfo 
            WHERE
                (
                    dbo.Risks_RiskDataNew.OrganisationID = dbo.Risks_RiskDataNew.OrganisationID
                )
        )

Related Articles



* original question posted on StackOverflow here.