topic Re: Performance for select from view in Application Development and Automation Discussions

Performance for select from view

Former Member — Tue, 21 Aug 2007 11:31:23 GMT

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

Re: Performance for select from view

Former Member — Wed, 22 Aug 2007 07:53:11 GMT

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

Re: Performance for select from view

Former Member — Fri, 24 Aug 2007 07:41:06 GMT

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

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

Re: Performance for select from view

Former Member — Fri, 24 Aug 2007 08:41:57 GMT

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

Re: Performance for select from view

Former Member — Fri, 24 Aug 2007 12:03:14 GMT

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.

Re: Performance for select from view

Former Member — Fri, 24 Aug 2007 12:18:33 GMT

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.

Re: Performance for select from view

Former Member — Sun, 26 Aug 2007 15:02:11 GMT

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