SAP abap数据库查询效率优化指南

EfficientDatabaseProgrammingwithABAP©2004SAPAG,ABAP2102LearningObjectivesAsaresultofthisworkshop,youwillbeableto:ExplainthecommunicationbetweendatabaseandapplicationserverAnalyzebottlenecksindatabaseprogrammingUnderstandhowtablebuffersandindicesworkUseOpenSQLforefficientdatabaseaccess©2004SAPAG,ABAP2103AgendaWASandDatabaseArchitectureABAPOpenSQLOverviewHowtoIdentifyExpensiveSQLRulesforBetterSQLProgrammingTheoryandHands-OnSummary©2004SAPAG,ABAP2104PerformanceofBusinessTransactionsGeneralrule:TheperformanceofabusinesstransactionisprimarilydeterminedbyitsDBaccesses.DBApplicationGUI©2004SAPAG,ABAP2105WASandDatabaseArchitectureApplicationServer(s)UsercommunicationDatatransferbetweenDBserverandapplicationserver(s)Frontend(s)/PresentationServer(s)DBServerDBcacheDBWPlocaldataWP©2004SAPAG,ABAP2106WASandDatabaseArchitectureR/3workprocessR/3workprocessDBworkprocessDBworkprocessDatabasecacheDatabaseServiceprocessesOperatingsystemDatabasefilesR/3workprocessDBworkprocessLANcommunicationDBCPUconsumptionDBmemoryconsumptionPhysicalI/OTableBufferAppServerCPUconsumptionAppServermemoryconsumption©2004SAPAG,ABAP2107TableBuffering:TypesFullbuffering(nokeyfields)8D0036D0032D0035C0034B0032B0036A0033A0032A0035D0021C0020C0023B0022B0021B0028A0026A0023A0021A0025B0013B0011B0014A0012A001datakey3key2key1Genericbuffering(twokeyfields)8D0036D0032D0035C0034B0032B0036A0033A0032A0035D0021C0020C0023B0022B0021B0028A0026A0023A0021A0025B0013B0011B0014A0012A001datakey3key2key1Single-recordbuffering(allkeyfields)8D0036D0032D0035C0034B0032B0036A0033A0032A0035D0021C0020C0023B0022B0021B0028A0026A0023A0021A0025B0013B0011B0014A0012A001datakey3key2key1Genericbuffering(onekeyfield)8D0036D0032D0035C0034B0032B0036A0033A0032A0035D0021C0020C0023B0022B0021B0028A0026A0023A0021A0025B0013B0011B0014A0012A001datakey3key2key1©2004SAPAG,ABAP2108TableBuffering:InvalidationPolicyLocalBufferFullBuffering:IncaseofaWorkarea-Update,thecorrespondingrowisupdatedinthebuffer.Otherwise,theentirebufferoftabletisinvalidated.GenericBuffering:IncaseofaWorkarea-Update,thecorrespondingrowisupdatedinthebuffer.Ifthereisachangeinonlythegenericarea,onlythisareaisinvalidated.Otherwise,theentirebufferoftabletisinvalidated.Single-RecordBuffering:Ifthereisachangeinonlyasinglerow,onlythisrowisinvalidated.Otherwise,theentirebufferoftabletisinvalidated.LocalBufferFullBuffering:IncaseofaWorkarea-Update,thecorrespondingrowisupdatedinthebuffer.Otherwise,theentirebufferoftabletisinvalidated.GenericBuffering:IncaseofaWorkarea-Update,thecorrespondingrowisupdatedinthebuffer.Ifthereisachangeinonlythegenericarea,onlythisareaisinvalidated.Otherwise,theentirebufferoftabletisinvalidated.Single-RecordBuffering:Ifthereisachangeinonlyasinglerow,onlythisrowisinvalidated.Otherwise,theentirebufferoftabletisinvalidated.©2004SAPAG,ABAP2109RemoteBuffersFullBuffering:Anychangeinvalidatestheentirebufferoftablet.GenericBuffering:Ifthereisachangeinonlythegenericarea,onlythisareaisinvalidated.Otherwise,theentirebufferoftabletisinvalidated.Single-RecordBuffering:Ifthereisachangeinonlyasinglerow,onlythisrowisinvalidated.Otherwise,theentirebufferoftabletisinvalidated.RemoteBuffersFullBuffering:Anychangeinvalidatestheentirebufferoftablet.GenericBuffering:Ifthereisachangeinonlythegenericarea,onlythisareaisinvalidated.Otherwise,theentirebufferoftabletisinvalidated.Single-RecordBuffering:Ifthereisachangeinonlyasinglerow,onlythisrowisinvalidated.Otherwise,theentirebufferoftabletisinvalidated.TableBuffering:InvalidationPolicy©2004SAPAG,ABAP21010BufferSynchronisation–Example(1)Server1Server2TableBufferDatabaseTableBufferTTTDDLOG©2004SAPAG,ABAP21011BufferSynchronisation–Example(2)Server1Server2TableBufferDatabaseTableBufferTTTDDLOGUpdateonT©2004SAPAG,ABAP21012BufferSynchronisation–Example(3)Server1Server2TableBufferDatabaseTableBufferTTTDDLOGUpdateonTINSERT©2004SAPAG,ABAP21013BufferSynchronisation–Example(4)Server1Server2TableBufferDatabaseTableBufferTTTDDLOGUpdateonTINSERTREAD©2004SAPAG,ABAP21014StatementOptimizerExecutionPlanSELECT*FROMsflightINTOxflightWHEREcityfrom=‘OSLO’ANDfldate=‘20020904’ORDERBYcarrid.rule-basedcost-based...decideshowtoexecutetheSQLstatement©2004SAPAG,ABAP21015TablesandIndicesAdams,TimBurton,RobertDaum,PaulMaier,BerndMaier,KurtStewart,RonZarcone,PiaAdams,TimBurton,RobertDaum,PaulMaier,BerndMaier,KurtStewart,RonZarcone,PiaMaier,BerndBerlinBurton,RobertNewOrleansZarcone,PiaRomeDaum,PaulBremenAdams,TimMiamiMaier,KurtHamburgStewart,RonEdinboroughMaier,BerndBerlinBurton,RobertNewOrleansZarcone,PiaRomeDaum,PaulBremenAdams,TimMiamiMaier,KurtHamburgStewart,RonEdinboroughBerlinBremenEdinboroughHamburgMiamiNewOrleansRomeBerlinBremenEdinboroughHamburgMiamiNewOrleansRomeTableIndex(Name)Index(City)©2004SAPAG,ABAP21016AccessbyFullTableScan16:3113:30SFOJFK0017AA08:1507:10FRATXL2407LH14:0012:00UNLGKS0815AA15:0513:30JFKFRA0402LH18:2510:00JFKSFO1984DL17:5514:45SFOJFK0007UA10:3015:00FRASFO0455LH12:3010:10SFOFRA0454LHarrtimedeptimeairptoairpfromconnidcarridSPFLISELECT*FROMspfliWHEREdeptime='150000'.©2004SAPAG,ABAP21017...Table