how to optimize a SQL view to fetch data faster from a table that has millions of records?

Question

Hello,

 the following view takes around 4 minutes to run. it is pulling data from a table that has four million rows.

how to optimize this view to run faster?

create or replace
algorithm = UNDEFINED view `vw_fltadjprodcap` (`AdjId`,
`AdjStat`,
`NetOptDt`,
`AdjDesc`,
`UserFldr`,
`C64`,
`LegOrig`,
`LegDest`,
`StartDepDt`,
`EndDepDt`,
`StartDFD`,
`EndDFD`,
`StartDepTm`,
`EndDepTm`,
`DOWGrp`,
`ApplStartDt`,
`ApplEndDt`,
`FltNbr`,
`EquipCd`,
`DmndDfltInd`,
`PolicyNm`,
`MINMAXCAPDesc`,
`MINMAXCAPParm`,
`InhbtWtlstIndicator`,
`CloseWtlstIndicator`,
`CloseWtlstThld`,
`BookCls`,
`POS`,
`Bckt`,
`BcktBrkptNbr`,
`Cabin`,
`CondAdjParm`,
`ApplFltCnt`,
`HierNbr`,
`FailRsnCd`,
`FailRsnDesc`,
`UploadId`,
`CreateBy`,
`AdjCreateDt`,
`ModfBy`,
`ModfDt`,
`LastApplDt`,
`LastProcDt`,
`AdjTypeId`,
`ActnPlnID`,
`SubmImmedInd`,
`AdjIDRel`,
`AdjRel`,
`AdjIDShr`) as
select
    `fapc`.`FlightAdjustmentProductCapID` as `AdjId`,
    upper(`state`.`AdjustmentStateName`) as `AdjStat`,
    `fapc`.`LastProcessedDt` as `NetOptDt`,
    `fapc`.`Description` as `AdjDesc`,
    `fapc`.`UserFolder` as `UserFldr`,
    `c64`.`C64Code` as `C64`,
    `orgapt`.`AirportCode` as `LegOrig`,
    `destapt`.`AirportCode` as `LegDest`,
    `fapc`.`DeptDtFrom` as `StartDepDt`,
    `fapc`.`DeptDtTo` as `EndDepDt`,
    `fapc`.`DaysFromDeptRangeFrom` as `StartDFD`,
    `fapc`.`DaysFromDeptRangeTo` as `EndDFD`,
    `fapc`.`DeptTmFrom` as `StartDepTm`,
    `fapc`.`DeptTmTo` as `EndDepTm`,
    `d`.`DOWGrp` as `DOWGrp`,
    `fapc`.`ApplyDtFrom` as `ApplStartDt`,
    `fapc`.`ApplyDtTo` as `ApplEndDt`,
    `fapc`.`FlightNumber` as `FltNbr`,
    `e`.`EquipTypeCd` as `EquipCd`,
    `fapc`.`DemandDefaultIndicator` as `DmndDfltInd`,
    `amt`.`PolicyTypeName` as `PolicyNm`,
    `fapc`.`MinMaxCpctyCode` as `MINMAXCAPDesc`,
    `fapc`.`MinMaxCpctyValue` as `MINMAXCAPParm`,
    `fapc`.`InhbtWtlstIndicator` as `InhbtWtlstIndicator`,
    `fapc`.`CloseWtlstIndicator` as `CloseWtlstIndicator`,
    `fapc`.`CloseWtlstThld` as `CloseWtlstThld`,
    `bc`.`BookingClassCode` as `BookCls`,
    `pc`.`POSCountryCode` as `POS`,
    `fapc`.`BucketNumber` as `Bckt`,
    `fapc`.`BucketBreakpoint` as `BcktBrkptNbr`,
    `c`.`CabinCode` as `Cabin`,
    `fapc`.`ConditionalAdjustment` as `CondAdjParm`,
    `fapc`.`NumberOfFlights` as `ApplFltCnt`,
    `fapc`.`HierarchyNumber` as `HierNbr`,
    `fr`.`FailureCode` as `FailRsnCd`,
    `fr`.`FailureReason` as `FailRsnDesc`,
    `fapc`.`AdjustmentUploadID` as `UploadId`,
    concat(upper(`usr`.`FirstName`), ' ', upper(`usr`.`LastName`)) as `CreateBy`,
    `fapc`.`CreationDt` as `AdjCreateDt`,
    concat(upper(`usr2`.`FirstName`), ' ', upper(`usr2`.`LastName`)) as `ModfBy`,
    `fapc`.`ModifiedDt` as `ModfDt`,
    `fapc`.`LastApplyDt` as `LastApplDt`,
    `fapc`.`ActualLastProcessedDt` as `LastProcDt`,
    `fapc`.`AdjustmentTypeID` as `AdjTypeId`,
    `fapc`.`ActionPlanID` as `ActnPlnID`,
    `fapc`.`SubmitImmediatelyIndicator` as `SubmImmedInd`,
    (case
        when (`fapc`.`AlternateVersionID` is null) then null
        when ((`fapc`.`AlternateVersionID` is not null)
        and (`fapc`.`AdjustmentStateID` not in (2, 3, 8))) then null
        else `fapc`.`AlternateVersionID`
    end) as `AdjIDRel`,
    (case
        when (`fapc`.`AlternateVersionID` is null) then 'FALSE'
        when ((`fapc`.`AlternateVersionID` is not null)
        and (`fapc`.`AdjustmentStateID` not in (2, 3, 8))) then 'FALSE'
        else 'TRUE'
    end) as `AdjRel`,
    (case
        when (`fapc`.`AlternateVersionID` is null) then `fapc`.`FlightAdjustmentProductCapID`
        when ((`fapc`.`AlternateVersionID` is not null)
        and (`fapc`.`AdjustmentStateID` <> 2)) then `fapc`.`FlightAdjustmentProductCapID`
        when ((`fapc`.`AlternateVersionID` is not null)
        and (`fapc`.`AdjustmentStateID` = 2)) then `fapc`.`AlternateVersionID`
    end) as `AdjIDShr`
from
    (((((((((((((`flightadjustmentproductcap` `fapc`
left join `airport` `orgapt` on
    ((`fapc`.`LegOrigAirport` = `orgapt`.`AirportID`)))
left join `airport` `destapt` on
    ((`fapc`.`LegDestinationAirport` = `destapt`.`AirportID`)))
join `authuser` `usr` on
    ((`fapc`.`CreatedBy` = `usr`.`AuthUserID`)))
left join `authuser` `usr2` on
    ((`fapc`.`ModifiedBy` = `usr2`.`AuthUserID`)))
join `adjustmentstate` `state` on
    ((`fapc`.`AdjustmentStateID` = `state`.`AdjustmentStateID`)))
left join `c64definition` `c64` on
    ((`c64`.`C64DefinitionAdjustmentID` = `fapc`.`C64DefinitionAdjustmentID`)))
left join `dowmapping` `d` on
    ((`fapc`.`DOW` = `d`.`DOW`)))
left join `failurereason` `fr` on
    ((`fr`.`FailureReasonID` = `fapc`.`FailureReasonID`)))
left join `equip` `e` on
    ((`e`.`EquipID` = `fapc`.`EquipID`)))
left join `bookingclass` `bc` on
    ((`fapc`.`BookingClassID` = `bc`.`BookingClassID`)))
left join `poscountry` `pc` on
    ((`pc`.`POSCountryID` = `fapc`.`POSCountryID`)))
left join `cabin` `c` on
    ((`c`.`CabinID` = `fapc`.`CabinID`)))
left join `policytype` `amt` on
    ((`amt`.`PolicyTypeID` = `fapc`.`PolicyTypeID`)))
where
    ((`fapc`.`DeletedIndicator` = 0)
        and (`fapc`.`TemplateIndicator` = 0)
            and `fapc`.`FlightAdjustmentProductCapID` in (
            select
                `flightadjustmentproductcap`.`AlternateVersionID`
            from
                `flightadjustmentproductcap`
            where
                ((`flightadjustmentproductcap`.`AlternateVersionID` is not null)
                    and (`flightadjustmentproductcap`.`AdjustmentStateID` not in (2, 3))
                        and (`flightadjustmentproductcap`.`DeletedIndicator` = 0))) is false);

Answer 1

Hi @SteveSmirth2024

The main reason why the views you create take a lot of time is because you use a lot of table joins and too much data. The secondary reason is the use of Case... When...... End statement to determine the value.

You can select the view you created and use Ctrl+L to view the execution plan.

In order to optimize the view, you can try to create an indexed view without changing the creation statement in advance, but the creation rules are a bit cumbersome.

https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

How should we answer your question if we don't have anything to test it on our own... there are so many options...

To assist you with your issue/problem adequately, it would be constructive for us to reproduce your scenario.

Please provide us with at least the following things:

(1) DDL and sample data population, i.e., CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e., logic and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in #1 above.

(4) Your SQL Server version (SELECT @@version)

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 3

That's not valid Transact-SQL for MS SQL Server, the topic of this forum.

Which RDBMS are you using? You may better post to a more related forum.