Skip to Content
avatar image
Former Member

Performance for select from view

Hi,

There is a select from a view (for ekko, ekpo, ekbe tables) which taking long time, some times going to dump which trying to fetch 20,00,000 recirds from database view.

view has created because selects are taking long time.

Is there any way to fine tune.

Means which one either view takes long time of any other way to replace view select.

Kishore

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Aug 22, 2007 at 07:53 AM

    what do you want to know!

    A view is nothing differnt than a join, i.e. a not faster than a select.

    20.000 records from large tables will never be fast, you can only do it as fast as possible.

    Tell use the statement, the where clause and the indices of the table which you think are appropriate, then you can start a discussion.

    Siegfried

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      U use view or join, the performance will depend on the indexes accessed.

      Ideally start with the table which will filter out minimum records.

  • avatar image
    Former Member
    Aug 24, 2007 at 08:41 AM

    What are the fields you are searching by in the WHERE block?

    The performance depends on whether the fields are key or index fields and how you are specifying them.

    How many records are you expecting to be selected for processing? All 20,000,000 or only a much smaller subset?

    inefficient use of access to the tables can vary the runtime from a few minutes to many hours.

    Post more details to the forum for more detailed answers on suggestions.

    Andrew

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 24, 2007 at 12:03 PM

    hi

    reward if usefull

    Improve performance of SELECT

    This tip has been copied from SearchSap.com

    Tip submitted by: Ben Meijs

    As you all know, it is important to use as many key fields as possible in WHERE clauses of SELECT

    statements. Sometimes you are not sure about the value of some key

    fields. They seem to be empty (Initial value) but you are afraid to use this in your ABAP coding.

    Using the option GE (greater equal) in your coding can improve your performance considerably

    without the risk of table lines not being selected.

    Code

    REPORT ZZBM_SELECT_1 .

    TABLES: S001.

    CONSTANTS: SSOUR_INI LIKE S001-SSOUR VALUE IS INITIAL,

    VRSIO_INI LIKE S001-VRSIO VALUE IS INITIAL,

    SPMON_INI LIKE S001-SPMON VALUE IS INITIAL,

    SPTAG_INI LIKE S001-SPTAG VALUE IS INITIAL,

    SPWOC_INI LIKE S001-SPWOC VALUE IS INITIAL,

    SPBUP_INI LIKE S001-SPBUP VALUE IS INITIAL.

    DATA: TA_S001 TYPE STANDARD TABLE OF S001.

    DATA: WA_S001 TYPE S001.

    SELECT-OPTIONS:

    SO_SPTAG FOR S001-SPTAG OBLIGATORY,

    SO_KUNNR FOR S001-KUNNR OBLIGATORY,

    SO_VKORG FOR S001-VKORG OBLIGATORY,

    SO_VTWEG FOR S001-VTWEG OBLIGATORY,

    SO_SPART FOR S001-SPART OBLIGATORY,

    SO_MATNR FOR S001-MATNR OBLIGATORY.

    START-OF-SELECTION.

    SELECT * FROM S001

    INTO TABLE TA_S001

    WHERE SSOUR GE SSOUR_INI "Greater Equal initial value

    AND VRSIO GE VRSIO_INI " idem

    AND SPMON GE SPMON_INI " idem

    AND SPTAG IN SO_SPTAG

    AND SPWOC GE SPWOC_INI " idem

    AND SPBUP GE SPBUP_INI " idem

    AND KUNNR IN SO_KUNNR

    AND VKORG IN SO_VKORG

    AND VTWEG IN SO_VTWEG

    AND SPART IN SO_SPART

    AND MATNR IN SO_MATNR.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 24, 2007 at 12:18 PM

    Hi

    Package size in SELECT statements

    Package size can be used to retreive a spcific number of records at a time. This can be used if you

    for example only want tofinish processing a limited amount of data at a time due to lack of memory.

    The exampel below read 50 records at a time from VBAK into an internal table, and selects the

    corresponding entries from vbap into an internal table. Then the two internal tables can be

    processed, and the next 50 records from VBAk can be read. remeber to reinitialize tha tables before

    the next read.

    Note the usage of SELECT - ENDSELECT !

    REPORT z_test .

    TYPES:

    BEGIN OF t_vbak,

    vbeln LIKE vbak-vbeln,

    erdat LIKE vbak-erdat,

    END OF t_vbak,

    BEGIN OF t_vbap,

    posnr LIKE vbap-posnr,

    matnr LIKE vbap-matnr,

    meins LIKE vbap-meins,

    END OF t_vbap,

    BEGIN OF t_report,

    vbeln LIKE vbak-vbeln,

    erdat LIKE vbak-erdat,

    posnr LIKE vbap-posnr,

    matnr LIKE vbap-matnr,

    meins LIKE vbap-meins,

    END OF t_report.

    DATA:

    li_vbak TYPE t_vbak OCCURS 0,

    l_vbak TYPE t_vbak,

    li_vbap TYPE t_vbap OCCURS 0,

    l_vbap TYPE t_vbap,

    li_report TYPE t_report OCCURS 0,

    l_report TYPE t_report.

    START-OF-SELECTION.

    SELECT vbeln erdat

    FROM vbak

    INTO TABLE li_vbak PACKAGE SIZE 50.

    SELECT posnr matnr meins

    FROM vbap

    INTO TABLE li_vbap

    FOR ALL ENTRIES IN li_vbak

    WHERE vbeln = li_vbak-vbeln.

    IF sy-subrc = 0.

    • Now you have the two internal tables li_vbak and liÆ_vbap filled with data.

    • Do something with the data - remember to reinitialize internal tables

    ENDIF.

    ENDSELECT.

    All of the product names here are trademarks of their respective companies. The site

    www.allsaplinks.com no way affiliated with SAP AG. We have made every effort for the content

    integrity. Information used on this site is at your own risk.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 26, 2007 at 03:02 PM

    Hi Kishore,

    Previous replies are correct. To enhance your join query do not use view, since it won't reduce the run time.

    First of all, we need the exact fields for your query,

    and then find the index in tables that match your selection criteria at most,

    and then consider to break up your code into separate select statements,

    begin with querying the header table (ekko), then ekpo and ekbe.

    the program would be looks like :

    select ebeln ( your fields here ) from ekko

    where .... ( selection criteria here )

    into table it_ebeln .

    if it_ebeln[] is not initial.

    select ebeln ebelp ( your fields here ) from ekpo

    for all entries in it_ekko

    where ebeln eq it_ekko-ebeln and ( selection criteria here )

    into table it_ekpo .

    select ebeln ( your fields here ) from ekbe

    for all entries in it_ekko

    where ebeln eq it_ekko-ebeln and ( selection criteria here )

    into table it_ekbe .

    endif. " it_ebeln

    loop at it_ekko.

    loop at it_ekpo and it_ekbe to assign expected data into your output

    endloop. " it_ekko

    Add comment
    10|10000 characters needed characters exceeded