Whenever we create database tables,we define what kind of buffering it should possess.If buffering is activated for a table,we should choose from one of three buffering types.

Single record buffering is recommended when we access fewer records from large tables. Generic buffering is recommended for language-specific and client-specific tables. Full buffering is recommended for small tables which are read frequently and written rarely.

Now,what are the optimization techniques we can use when we have large tables and where records are accessed very frequently. What type of buffering is recommended and how can we optimize our database tables for such situations.

asked 11 Apr '12, 12:04

bobby's gravatar image

bobby
1464513
accept rate: 0%


Large tables with many accesses, like VBAP, MSEG, MARA, etc, are configured by SAP without buffering, so I suppose that no buffering is recommended for those cases. To optimize performance it is common to see in SAP application code the following:

1) Try to query the database for a group of records instead of doing multiple single record selects (it's much faster to select one time 1000 rows than to do 1000 selects of 1 row)

2) Cache the results of DB queries in some application global variables, and check that cache before doing another query to the database. This can reduce the number of queries done and make the code faster, but one needs to use internal table of the type hash tables, otherwise, if the internal tables becomes large, searching for a record in memory with normal tables can be much slower than going to the database.

permanent link

answered 12 Apr '12, 14:02

pedrolima's gravatar image

pedrolima ♦♦
1.1k232840
accept rate: 32%

I think caching results of db queries is same as single record buffering. Single-record buffering will load into the buffer only the records that are actually read.

hey can you tell me how to cache the results of DB queries into application global variables.

(13 Apr '12, 03:54) bobby

I posted an exemple below, hope it helps.

(22 Apr '12, 18:39) pedrolima ♦♦

One example of caching db queries (just one of many standard functions that can be found in the system). In this case a STATICS variable is used, an alternative is to create the variable in the top include of the function group. In both cases the variable is kept in memory after the function execution.

FUNCTION bank_db_workl_get_pack_area.
*"----------------------------------------------------------------------
*"*"Lokale Schnittstelle:
*"  IMPORTING
*"     REFERENCE(I_WORKLID) TYPE  BANK_WORKLID
*"     REFERENCE(I_SERVER) TYPE  BANK_DTE_JC_SERVERNAME
*"  EXPORTING
*"     REFERENCE(E_PACKAGENOFROM) TYPE  BANK_DTE_PP_INTERVNO
*"----------------------------------------------------------------------

  DATA:
    BEGIN OF l_wrk_buf,
      worklid TYPE bank_worklid,
      server  TYPE bank_dte_jc_servername,
      nofrom  TYPE BANK_DTE_PP_INTERVNO ,
    END   OF l_wrk_buf.

  STATICS:
    s_tas_buf LIKE SORTED TABLE OF l_wrk_buf
              WITH UNIQUE KEY worklid server.

  READ TABLE s_tas_buf INTO l_wrk_buf
       WITH KEY worklid = i_worklid
                server = i_server
       BINARY SEARCH.

  IF ( sy-subrc EQ 0 ).
    e_packagenofrom = l_wrk_buf-nofrom.

  ELSE.
    l_wrk_buf-worklid = i_worklid.
    l_wrk_buf-server = i_server.

    SELECT SINGLE packagenofrom
           FROM bank_wl_srvrel
           INTO l_wrk_buf-nofrom
           WHERE worklid = i_worklid
             AND server = i_server.

    IF ( sy-subrc EQ 0 ).
      INSERT l_wrk_buf INTO TABLE s_tas_buf.
      e_packagenofrom = l_wrk_buf-nofrom.
    ENDIF.

  ENDIF.
ENDFUNCTION.
permanent link

answered 22 Apr '12, 18:38

pedrolima's gravatar image

pedrolima ♦♦
1.1k232840
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28

question asked: 11 Apr '12, 12:04

question was seen: 17,596 times

last updated: 22 Apr '12, 18:39