Post reply

Best way to optimize view that contains millions of rows

  • August 26, 2015 at 3:20 pm

    #302282

    Hello,

    I have one view which is based on couple of tables. Here is the definition of view. Can anyone please let me know which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.

    CREATE VIEW [dbo].[V_Reqs]

    WITH SCHEMABINDING

    AS

    SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime,

    purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne,

    purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,

    purchase.Req.FaxNumber, purchase.Req.HealthCardNumber, purchase.Req.HealthCardVersion, purchase.Req.DOB,

    purchase.Req.Sex, purchase.Req.Age, purchase.Req.ChartIdentifier, purchase.Req.CommentsOne,

    purchase.Req.CommentsTwo, purchase.Req.Investigator, purchase.Req.Collector, purchase.Req.CertifyingScientist,

    purchase.Req.NumberOfCopies, purchase.Req.LanUserGroup, purchase.Req.CumulativeReportingFlag,

    purchase.Req.ControlNumber, purchase.Req.CollectionSiteCode, purchase.Req.BatchFileName,

    purchase.Req.InsuranceAmount, purchase.Req.InsuranceType, purchase.Req.ExaminingCompany,

    purchase.Req.InsuranceBarCode, purchase.Req.LastFoodTaken, purchase.Req.ReferencePolicyNumber,

    purchase.Req.SensitiveInfo, purchase.Req.Source, purchase.Req.InsuranceClientAgentCode, purchase.Req.ExaminerCode,

    purchase.Req.InsuranceAgent, purchase.Req.ExaminingProvinceCode, purchase.Req.WasClosedBefore,

    purchase.Req.AgentProvinceCode, purchase.Req.TotalTestCount, purchase.Req.AbnormalTestCount,

    purchase.Req.RemainingTestCount, purchase.Req.RequestDateTime, purchase.Req.ReportingLabCode,

    purchase.Req.ReportingLabName, purchase.Req.ReportingAddress1, purchase.Req.ReportingAddress2,

    purchase.Req.ReportingAddress3, purchase.Req.ReportingProvince, purchase.Req.ReportingPostalCode,

    Admin.RequisitionLifeCycle.ID, Admin.RequisitionLifeCycle.UserName, Admin.RequisitionLifeCycle.RequisitionID AS Expr1,

    Admin.RequisitionLifeCycle.StatusCode

    FROM purchase.Req INNER JOIN

    Admin.RequisitionLifeCycle ON Admin.RequisitionLifeCycle.RequisitionID = purchase.Req.RequisitionID

    Indexes on view attached in excel sheet.

  • August 26, 2015 at 5:59 pm

    #1822640

    It's not the View that needs to be optimized. It's the queries that call it that need to be optimized.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 27, 2015 at 3:54 am

    #1822691

    Any foreign key between Admin.RequisitionLifeCycle.RequisitionID and purchase.Req.RequisitionID?

    Do they have own index or primary key?

  • August 27, 2015 at 4:07 am

    #1822693

    Since it's an indexed view, giving us the definition of the view is useless from the point of tuning. You need to identify the queries using the view that are slow and post those, along with their execution plans.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply