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

SteveSmirth2024 0 Reputation points
2023-01-18T00:13:45.26+00:00

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);

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,238 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-01-18T03:00:34.2966667+00:00

    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.

    0 comments No comments

  2. Bjoern Peters 8,766 Reputation points
    2023-01-18T03:12:21.61+00:00

    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!

    0 comments No comments

  3. Olaf Helper 38,786 Reputation points
    2023-01-18T07:06:01.3233333+00:00

    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.