Within SAP, tables are queried and data extracted using the OPEN SQL version of the SELECT statement. A single bad example of this statement in a program can make it unusable due to the amount of time it takes for the SELECT statement to return the results.
This article outlines some of the ways that a Select statement can bring a program to it's knees, and how to speed things up. Note that a lot of programmers fall into the development data trap. Ie the select statement is reasonable on the DEV and QA systems but turns into a sloth on production. The reason ?? The amount of data stored in the different systems. Write your selects correctly the first time round so that you do not have to revisit them at a later date.
Select statements generally look something like the following:
Code: Select all
Select * from Mara where Matkl = '1234'. ... ... Some Processing ... EndSelect.
Ok, so whats wrong with that ? The major thing is that for each iteration round the loop two database requests are generated, causing unwarrented network traffic.
By changing the way the select is written, many database requests can be reduced to one, with the accompanying reduction in network traffic and database accesses (Assume t_Mara is like MARA):
Code: Select all
Select * from Mara into table t_Mara where Matkl = '1234'. * Loop at t_ Mara into w_Mara. ... ... Some Processing ... EndLoop.
Field Lists consist of a list of database fields that you wish to retrieve. The '*' in SELECT * is a shorthand way of specifying all the fields of a table. So what's wrong with that ?? By using *, if the table definition changes, the program won't break and maintenance is reduced. However, by using '*', you also flood the network with loads of data that you do not want nor care about.
As an example, in the version of SAP I am using at the moment, MARA has 154 fields. What is the point of retrieving every single field when you only want for example MATNR, MTART and MEINS ??
You do this by specifying a field list like so:
Code: Select all
Select MATNR MTART MEINS from Mara into corresponding fields of table t_Mara where Matkl = '1234'. * Loop at t_ Mara into w_Mara. ... ... Some Processing ... EndLoop.
So get rid of it....
Define a structure that is exactly the list of fields you wish to retrieve, in the order that you retrieve them. If there are additional fields required for further processing put them at the end of the table definition. Then you can code your SELECT statement like this:
Code: Select all
Select MATNR MTART MEINS from Mara into table t_Mara where Matkl = '1234'. * Loop at t_ Mara into w_Mara. ... ... Some Processing ... EndLoop.
Of Indexes and Table Scans.
Way back when.... there was no Oracle, no Adabas no nothing, just the ability to write information to files. You, the programmer was responsible for writing any indexes that your database required. Thankfully now you have it all done for you by the database system being used. So what's an index anyway ?? An index is a method by which data is organised into the order in which you are searching the file. The index itself does not contain the full information but rather it contains a subset of the information and a record pointer or key into the main file or table which points directly to the data you need.
For example a company sells it's products to it's customers. When the customer orders a product it must also buy other products that are required by the first product. So the customer phones up and places his order, the order clerk must then go and find out the other products that are required before entering the order into the system.
However, this was sped up by using the 'FAST ORDER TEMPLATE' this consisted of a random access file which allowed the program to skip backwards and forwards amongst the records. These records consist of a 'Fast Order Template' number and a single product number. There may be many (50 or 60) extra products.
Due to the fact that there are many extra products in the template the records do not start on a 'template boundary' - for template 172 for example, the product list may start at record 12134.
You could should you so wish, start at record one and read through the file until you reach the records for the template the cleark requires, and then transfer each record until the template number changes. This is known as a table scan.
However, a better approach would be to construct an index. All this index is, is a list of record numbers pointing to the start of the template list for a specified template. Record 1 is template 1, record 26543 is template 26543 (this is an actual scenario.....) The value held in record 26543 is the record number where template 26543 actually starts in the template file.
So, by taking this approach rather than reading thousands of records before you find the one you want, you read a single record from another file indexed by the template number and then directly start reading the template. That is an index.
As SAP has a backend database, all this indexing is done 'under the hood' so to speak and normally we as programmers do not need to worry about indexes, however, you do need to take care when specifying the 'WHERE' clause. There is an entity called the Query Optimiser which sits between you and the database. Sometimes it does not make a very good job of deciding which index to use for your query. One way to help this is to specify the fields in the 'WHERE' clause in exactly the same order they appear in the key or the index.
How to find Keys and Indexes.
To make sure that you are specifying key fields in your where clause, use transaction SE11. The 'Key' fields are those fields that are specified as 'KEY' in the table definition (you can ignore MANDT unless you use the 'CLIENT SPECIFIED' clause of the 'SELECT' statement). Indexes can be found by using SE11->GOTO->INDEXES:
To see what fields an index uses, click on the relevant line of the report:
'Oh dear' - or words to that effect. Your Where clause does not specify any field that is a key field nor is included in any index for the table that you are selecting against. What do you do ? Well the first thing you don't do is create a new index (The 'create' button on the indexes window). The reason for this is that any additional index on a table increases the load on SAP as it processes requests for that table because of a) the additional database accesses required and b) the additional space required.
What you do instead is to try and find tables that you do have key fields that can then provide you with the keys into the table you are really after (if not the actual information itself).
For example table BSEG has the following key fields:
- Company Code
- Accounting Document Number
- Fiscal Year
- Number of Line Item Within Accounting Document
So, unless you have the above data you will end up doing a table scan and that will cripple any program trying to report on data from BSEG.
Because of the table type you cannot create any database indexes, so what SAP have done is created a series of tables that act as indexes into BSEG. These tables are:
- BSAD - Accounting: Secondary Index for Customers
- BSAK - Accounting: Secondary Index for Vendors (C
- BSAS - Accounting: Secondary Index for G/L Accoun
- BSIA - Archive Index for FI Documents (Outdated!
- BSID - Accounting: Secondary Index for Customers
- BSIK - Accounting: Secondary Index for Vendors
- BSIM - Secondary Index, Documents for Material
- BSIP - Index for Vendor Validation of Double Docu
- BSIS - Accounting: Secondary Index for G/L Accoun
- BSIW - Index table for customer bills of exchange
- BSIX - Index table for customer bills of exchange
A join is a link between one or more tables. This link produces all the records from each table that satisfy the 'WHERE' clause and the join conditions.
Joins come in two guises. A Left inner join and a right outer join. The difference between the two can be described using a Venn diagram. The Inner Join results in the records that form the intersection, the outer join results in the records that form the union of the input tables:
In other words, an Inner Join only includes rows in the result set where there is a match on all the criteria that specifies the join. An outer join will include all the rows selected by the main select clause, regardless of whether there is a match on the join clause or not.
A typical join might look like:
Code: Select all
Select vbak~vbeln vbak~kunnr vbap~matnr vbap~arktx makt~maktx aufk~aufnr aufk~adrnra aufk~objnr afpo~posnr afpo~ltrmp lips~vbeln lips~posnr afpo~lgort lips~meins afpo~wemng into table t_Deliveries from vbak inner join vbap on vbap~vbeln = vbak~vbeln inner join makt on makt~matnr = vbap~matnr and spras = sy-langu inner join aufk on aufk~kdauf = vbap~vbeln and aufk~kdpos = vbap~posnr and aufk~astkz = False and aufk~phas2 = False and aufk~phas3 = False and aufk~loekz = False inner join afpo on afpo~aufnr = aufk~aufnr and afpo~kdauf = aufk~kdauf and afpo~kdpos = aufk~kdpos and afpo~wemng <> 0 inner join lips on lips~vbelv = afpo~kdauf and lips~posnv = afpo~kdpos inner join vbup on vbup~vbeln = lips~vbeln and vbup~posnr = lips~posnr and vbup~kosta <> c_not_relevant and vbup~kosta <> c_fully_picked Where vbak~vbeln in s_vbeln and vbak~auart = g_auart.
Code: Select all
Select y2~seq y1~charg yfiber~fid y1~printed lqua~lgpla into table tb_bins from yfiberf as y1 inner join yfiberf as y2 on y2~seq = y1~seq inner join yfiber on yfiber~charg = y1~charg and yfiber~werks = y1~werks inner join lqua on lqua~lgnum = y2~lgnum and lqua~matnr = y2~matnr and lqua~werks = y2~werks and lqua~charg = y2~charg where y1~werks = w_werks and y1~lgnum = w_lgnum and y1~charg = w_charg.
Why should you use joins ?? Well, there is less network traffic to start with, and the database is far more efficient at doing this type of thing than ABAP or yourself can be so my take on this is to pass off all I can to the database and let it get on with what it's good at.
What's a sub-query ?? It's a self contained Select within a Select. Again, the reason why these should be used is to lessen the network load and to pass the job off to the database:
Code: Select all
* * Select deliverys without a shipment * Select Likp~Lgbzo Vbuk~Pkstk Vbuk~Uvk02 Likp~Btgew Likp~Volum Likp~Lgnum Likp~Kodat Likp~Vbeln Likp~Kunnr Vbuk~Kostk Vbuk~Lvstk Vbuk~Wbstk Vbuk~Trsta Likp~Trspg Vbuk~Fkstk Likp~Trspg Likp~Route Lips~Vgbel Likp~Vstel Lips~Posnr Vbup~Kosta Vbup~Pksta Vbup~Lvsta Vbup~Wbsta Vbup~Fksta Lips~Matnr Lips~Arktx Lips~Lfimg Lips~Vrkme Lips~Brgew Lips~Mfrgr Mara~Stoff Lips~Gewei Likp~Lddat Likp~Lduhr Likp~Lgtor Into Corresponding Fields Of Table T_Dis2 From Likp Inner Join Vbuk On Likp~Vbeln = Vbuk~Vbeln Inner Join Lips On Lips~Vbeln = Likp~Vbeln Inner Join Vbup On Vbup~Vbeln = Likp~Vbeln And Vbup~Posnr = Lips~Posnr Inner Join Mara On Lips~Matnr = Mara~Matnr Where Likp~Lgnum Eq P_Lgnum And Likp~Lgbzo In S_Lgbzo And Likp~Vbeln In S_Vbeln And Likp~Kunnr In S_Kunnr And Likp~Wadat In S_Wadat And Likp~Wauhr In S_Wauhr And Likp~Lddat In S_Lddat And Likp~Lduhr In S_Lduhr And Likp~Tddat In S_Tddat And Likp~Tduhr In S_Tduhr And Likp~Trspg In S_Trspg And Likp~Lgtor In S_Lgtor " New And Likp~Route In S_Route " New And Vbuk~Kostk In S_Kostk And Vbuk~Pkstk In S_Pkstk And Vbuk~Uvk02 In S_Uvk02 And Vbuk~Trsta In S_Trsta And Not Exists ( Select * From Vttp Where Vbeln Eq Likp~Vbeln ).
- Avoid Select-Endselect constructs
- Select into an Itab
- Always specify Field Lists unless you really want all the fields
- Structure your itabs in the same order as your field lists
- Avoid using 'INTO CORRESPONDING FIELDS' if you can
- Use Joins and Sub Queries in preference to multiple Select statements
- Let the database do what it's there for!