This website is not affiliated with, sponsored by, or approved by SAP AG.

0051 - Select 101

Moderators: Snowy, thx4allthefish, Rich, ilya

0051 - Select 101

Postby Rich » Sat Mar 28, 2009 8:54 am

Selects 101.

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 ENDSELECT.

Select statements generally look something like the following:

Code: Select all
Select *
  from Mara
where Matkl = '1234'.
...
... Some Processing
...
EndSelect.


This statement block will do what you want - selecting data from Table Mara (don't look too closely at the code - these are examples only....), processing the data and then going back for the next record.

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.


What has happened here is that the entire result set for the select has been returned all at once as an internal table. The program then loops around the internal table to process the data. The speed increases from this technique are two fold: First there is a greatly reduced number of database requests and secondly by using internal tables data can be accessed faster than retrieving it from a database.

Field Lists

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.


We've reduced the amount of time spent shovelling data around the network by removing the SELECT-ENDSELECT loop, and we've reduced the amount of data flying about by specifying the fields that we really do want rather than being lazy and asking for all fields. The 'INTO CORRESPONDING' clause of the SELECT statement puts the fields in the result set into the correct fields (ie fields of the same name) in the table t_Mara. All so well and good. If t_Mara changes then the select will still work without a problem but there is still the time it takes the SELECT statement to find the locations of the specified fields in the field list, which can again cause some performance loss.

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.


Notice that the 'INTO CORRESPONDING FIELDS' clause has dissappeared. This causes the SELECT statement to 'pour' the current row directly into the table, ignoring field definitions and the table structure. As it does not have to search for each field to place the data into, the time for the SELECT statement to complete is again reduced. However, to do this, your internal table structure must have the fields listed in the same order as those in the field list, and the field definitions must be identical to those used by SAP.

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:

Image

To see what fields an index uses, click on the relevant line of the report:

Image

'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:

  • Client
  • Company Code
  • Accounting Document Number
  • Fiscal Year
  • Number of Line Item Within Accounting Document

and no indexes.....

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

Another example of a table that can cause problems with Selects is GLPCA. The data that makes up the records in this table can be found in the CO* tables.

Joins.


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:

Image

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.


You can, if you wish leave out the table names on the fields if they are unique to that table (ie they do not occur in any other table in the join), however I always include them to make sure that there is no doubt where the data is coming from. There is an 'Alias' clause which a lot of people use in a join like that above. This is not necessary, the only time you need to use an alias is when you are doing a 'Self Join' - ie a join that joins a table to itself, or has two or more joins to the same table:

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.


The join above locates all the records that contain a particular 'Sequence' number, however, the sequence number is not known until the first row is found.

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.

Sub Queries.

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


In summary:
  • 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!
Regards

Rich

Image
Abap KC:http://www.richard-harper.me.uk/Kb
SFMDR:http://www.se37.com
Rich
 
Posts: 7112
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool

Return to ABAPers

Who is online

Users browsing this forum: No registered users and 1 guest





loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.