1 / 26

Need-for-Speed Performance Tuning

Need-for-Speed Performance Tuning. Duncan Klett VP Analytics Research. Outline. Performance guidelines 10.1 query improvements Impact of guidelines Custom data model design. Performance Guidelines (1). Avoid “set” expressions Use Lookup and Join Use the “right” source worksheet(s)

allene
Télécharger la présentation

Need-for-Speed Performance Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Need-for-SpeedPerformance Tuning Duncan Klett VP Analytics Research

  2. Outline • Performance guidelines • 10.1 query improvements • Impact of guidelines • Custom data model design

  3. Performance Guidelines (1) • Avoid “set” expressions • Use Lookup and Join • Use the “right” source worksheet(s) • Use common source component sheet if possible • Use single IN for all component sheet filters rather than individual filters • Use “expression-based” fields in data model rather than worksheet queries

  4. Performance Guidelines (2) • Use “summarization” functions for group totals, fractions, etc • Collapse levels in composites • Defer bucketing • Use Reference to tables rather than strings • Avoid string concatenation if possible • Use Variables to identify “focus” items • Customized Data Model: Use “references” rather than strings

  5. Choosing the table to report • Queries on Input data are fast (input tables, input fields) • Use Activity if “driver” and AvailableDate not needed • Use CTPActivity (or CTPPlannedOrder) if AvailableDate is needed but Driver is not needed • Full-level-peg tables are slower, but have driver information • FlatBillUp and FlatBillDown can be slow if multiple time-phased BOMs, substitute BOMs, and/or PartSources

  6. Avoiding FlatBill* tables • 10.1 has new analytic variable:RR_Analytics_FlatBill_MergeIdentical • Reduces time and memory requirements by collapsing identical BOM and PartSource records when calculaing • To get a list of Parts that might be within the BOM (and within the same site), try (see data model guide): • Part In Components.Component OF ($SelectedFilter and Site[Eval('Site \'' + $SelectedSite + '\'')] )

  7. Choosing the table to report • Use WhereConsumed rather than: • WhereConsumedForDemand or LateSupply unless: • reporting data from less than 10 driver parts and • filter is in context of independent demands • WhereConsumedForSupply • unless you need to peg to intermediate supply orders

  8. Example Worksheet (for performance) • Crosstab to show demand, supply and balance, pegged back to selected part(s) by driver • Filter so only components with negative balance shown • Include Description and component make/buy sourcing

  9. Worksheet Structure & Performance Composite Structure:

  10. Original Query SRCWhereConsumedDemand: WhereConsumed[DriverPart[PRIVATE s'IndDmd parts' AND ABCCode='A' AND Name='CRUISER'] AND DriverPart.Site[PUBLIC Site 'All Sites']] {DriverPart=DriverPart.Name :By , DriverSite=DriverPart.Site :By , DriverDescription=DriverPart.Description :By , Level=Part.MultiSiteLowLevelCode :By , Part=Part.Name :By , Site=Part.Site :By , Description=Part.Description :By , MakeBuy=IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') :By , DemandDate=NeedDate :Bucket(CMPBalance!!Buckets!) , Quantity=NeedQuantity }; SRCWhereConsumedSupply … , AvailableDate=SplitSupplyAvailable :Bucket(CMPBalance!!Buckets!) , Quantity=NeedQuantity }; CMPBalance (SRCWhereConsumedDemand! OUTERJOIN SRCWhereConsumedSupply!) {DriverPart=SRCWhereConsumedDemand!DriverPart :By , DriverSite=SRCWhereConsumedDemand!DriverSite :By , DriverDescription=SRCWhereConsumedDemand!DriverDescription :By , Level=SRCWhereConsumedDemand!Level :By , Part=SRCWhereConsumedDemand!Part :By , Site=SRCWhereConsumedDemand!Site :By , Description=SRCWhereConsumedDemand!Description :By , MakeBuy=SRCWhereConsumedDemand!MakeBuy :By , Date=SRCWhereConsumedDemand!DemandDate :Bucket(CMPBalance!!Buckets!) , Demand=SRCWhereConsumedDemand!Quantity , Supply=SRCWhereConsumedSupply!Quantity , Balance=SRCWhereConsumedSupply!Quantity - SRCWhereConsumedDemand!Quantity: Subtotal(Running, Site) }; CMPBalanceFilter (SRCWhereConsumedDemand! OUTERJOIN SRCWhereConsumedSupply!) {DriverPart=SRCWhereConsumedDemand!DriverPart :By , DriverSite=SRCWhereConsumedDemand!DriverSite :By , DriverDescription=SRCWhereConsumedDemand!DriverDescription :By , Level=SRCWhereConsumedDemand!Level :By , Part=SRCWhereConsumedDemand!Part :By , Site=SRCWhereConsumedDemand!Site :By , Description=SRCWhereConsumedDemand!Description :By , Date=SRCWhereConsumedDemand!DemandDate :Bucket(CMPBalance!!Buckets!) , Balance=SRCWhereConsumedSupply!Quantity - SRCWhereConsumedDemand!Quantity: Subtotal(Running, Site) }[Date < d'20120305']; INBalanceFilter (CMPBalanceFilter!)[CMPBalanceFilter!Balance < 0] {ComponentPart=CMPBalanceFilter!Part :By , Site=CMPBalanceFilter!Site :By }; ComponentPlanning (CMPBalance!)[{ CMPBalance!Part, CMPBalance!Site } IN InBalanceFilter!] {DriverPart=CMPBalance!DriverPart :By :m n , DriverSite=CMPBalance!DriverSite :By :m n , DriverDescription=CMPBalance!DriverDescription :By :m n , Level=CMPBalance!Level :By :m n , Part=CMPBalance!Part :By :m n , Site=CMPBalance!Site :By :m n , Description=CMPBalance!Description :By :m n , MakeBuy=CMPBalance!MakeBuy :By :m n , Date=CMPBalance!Date :Bucket(ComponentPlanning!!Buckets!) :m n , Demand=CMPBalance!Demand :m n , Supply=CMPBalance!Supply :m n , Balance=CMPBalance!Supply - CMPBalance!Demand: Subtotal(Running, Site) :m n };

  11. Worksheet Performance Tool Where did the time go? • 2 almost identical queries on WhereConsumed • 7,000+ records (after bucketing) eachIncluding a “set” expression for make/buy • Identical source sheets are RE-USED

  12. Improvements • Merge the WhereConsumed queries into one • Pass Part as a reference • Removed Site, Description, Level fields • These can be calculated in the “top” sheet from the References • Defer bucketing • Use separate IN sheet to identify all POSSIBLE components • Avoid “Set” expression to determine Make, Buy, etc.

  13. Identifying Possible Components • Component sheet based on Part • Use filter expression (rather than filter radio buttons) • Example Filter expression: In Components.Component OF ($SelectedFilter and Site[Eval('Site \'' + $SelectedSite + '\'')] ) • First column reports Self (as a Reference)

  14. Avoiding a “set” operation • (Original) WhereConsumed expression: IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') • Move per-record structure in SRC WhereConsumed to per-part in Part query • Add column to InSelectedComponents worksheet • (Revised) Part expression: IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') +IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') +IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') • Use Lookup in final worksheet: MakeBuy=Lookup( CMPBalanceNewFilter!Part, InSelectedComponents!, 'NONE', Exact)

  15. Setting a field as “Reference”

  16. SRC sheet column comparison • Revised • DriverPart=DriverPart :Ref :By , • Part=Part :Ref :By , • AvailableDate=SplitSupplyAvailable :By , • DemandDate=NeedDate :By , • Quantity=NeedQuantity Original (Demand, repeated for Supply) • DriverPart=DriverPart.Name :By , • DriverSite=DriverPart.Site :By , • DriverDescription=DriverPart.Description :By , • Level=Part.MultiSiteLowLevelCode :By , • Part=Part.Name :By , Site=Part.Site :By , • Description=Part.Description :By , • MakeBuy=IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') :By , • DemandDate=NeedDate :Bucket(CMPBalance!!Buckets!) , • Quantity=NeedQuantity

  17. (Improved) Worksheet Structure & Performance Composite Structure (10.1) & Query Times: 9.5.1 Query times

  18. Revised Query InSelectedComponents Part[((Components.Component OF $SelectedFilter and Site[Eval('Site \'' + $SelectedSite + '\'')] ))] {PartRef=Self :Ref :By , MakeBuy=IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') +IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') +IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') :Max }; SRCWhereConsumed WhereConsumed[DriverPart[PRIVATE s'IndDmd parts' AND ABCCode='A' AND Name='CRUISER'] and (Part IN INSelectedComponents!) AND DriverPart.Site[PUBLIC Site 'All Sites']] {DriverPart=DriverPart :Ref :By , Part=Part :Ref :By , AvailableDate=SplitSupplyAvailable :By , DemandDate=NeedDate :By , Quantity=NeedQuantity }; CMPSupply (SRCWhereConsumed!){DriverPart=SRCWhereConsumed!DriverPart :Ref :By , Part=SRCWhereConsumed!Part :Ref :By , AvailableDate=SRCWhereConsumed!AvailableDate :By , Quantity=SRCWhereConsumed!Quantity }; CMPDemand (SRCWhereConsumed!){DriverPart=SRCWhereConsumed!DriverPart :Ref :By , Part=SRCWhereConsumed!Part :Ref :By , DemandDate=SRCWhereConsumed!DemandDate :By , Quantity=SRCWhereConsumed!Quantity }; CMPBalanceNew (CMPSupply! OUTERJOIN CMPDemand!){DriverPart=CMPSupply!DriverPart :Ref :By , Part=CMPSupply!Part :Ref :By , Date=CMPSupply!AvailableDate :Bucket(CMPBalanceNewFilter!!Buckets!) , Supply=CMPSupply!Quantity , Demand=CMPDemand!Quantity , Balance=CMPSupply!Quantity - CMPDemand!Quantity: Subtotal(Running, Part) }; CMPBalanceNewFilter (CMPBalanceNew!){Part=CMPBalanceNew!Part :Ref :By , DriverPart=CMPBalanceNew!DriverPart :Ref :By , MinBalance=CMPBalanceNew!Balance :By: Subtotal(Min, Part) , Date=CMPBalanceNew!Date :Bucket(CMPBalanceNewFilter!!Buckets!) , Supply=CMPBalanceNew!Supply , Demand=CMPBalanceNew!Demand , Balance=CMPBalanceNew!Balance }; ComponentPlanningNew (CMPBalanceNewFilter!)[CMPBalanceNewFilter!MinBalance < 0] {DriverPart=CMPBalanceNewFilter!DriverPart.Name :By :m n , DriverSite=CMPBalanceNewFilter!DriverPart.Site :By :m n , DriverDescription=CMPBalanceNewFilter!DriverPart.Description :By :m n , Level=CMPBalanceNewFilter!Part.MultiSiteLowLevelCode :By :m n , Part=CMPBalanceNewFilter!Part.Name :By :m n , Site=CMPBalanceNewFilter!Part.Site :By :m n , Description=CMPBalanceNewFilter!Part.Description :By :m n , MakeBuy=Lookup( CMPBalanceNewFilter!Part, InSelectedComponents!, 'NONE', Exact) :By :m n , Date=CMPBalanceNewFilter!Date :Bucket(ComponentPlanningNew!!Buckets!) :m n , Demand=CMPBalanceNewFilter!Demand :m n , Supply=CMPBalanceNewFilter!Supply :m n , Balance=CMPBalanceNewFilter!Supply - CMPBalanceNewFilter!Demand: Subtotal(Running, Site) :m n }

  19. Setting/Displaying “Focus Part” • Previously, needed a column expression, such asSelectedPart = IF ( Part = $FocusPart, ‘Y’, ‘’) • Then, use conditional formatting:Part = SelectedPart • Now, simply use Column Part value = $FocusPartin Conditional Formatting • Formatting is applied, no re-running of query is required

  20. Define Workbook Variable

  21. Define XML for Set Focus from Part column

  22. Set Focus

  23. Focus Display

  24. Customized data model design • Avoid strings • Preferably only use for “key” fields and descriptions • Put the field on the “right” table • If the value is the same for a set of data, put the new field on a “header” record for the set • Original new fields on IndependentDemand: • (all fields were Strings) • U_CustomerLocation • U_CustomerCountry • U_CustomerRegion • Alternative 1 • IndependentDemand: • Order -> Customer • Customer: • U_Location • U_Location: • U_Country • U_Country • U_Region • Alternative 2 • IndependentDemand: • U_CustomerLocation • U_Location: • U_Country • U_Country • U_Region • Advantages of Alternatives: • Easier maintenance • Allows drop-lists for data entry • Ensures data consistency • Faster, simpler queries • Avoids long chains of nested IF statements

  25. Performance Guidelines • Avoid “set” expressions • Use Lookup and Join • Use the “right” source worksheet(s) • Use common source component sheet if possible • Use single IN for all component sheet filters rather than individual filters • Use “expression-based” fields in data model rather than worksheet queries • Use “summarization” functions for group totals, fractions, etc • Collapse levels in composites • Defer bucketing • Use Reference to tables rather than strings • Avoid string concatenation if possible • Use Variables to identify “focus” items • Customized Data Model: Use “references” rather than strings

  26. ThanksQuestions ?? Duncan Klett

More Related