08-21-2007 12:31 PM
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
08-22-2007 8: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
08-24-2007 8:41 AM
U use view or join, the performance will depend on the indexes accessed.
Ideally start with the table which will filter out minimum records.
08-24-2007 9: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
08-24-2007 1: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.
08-24-2007 1: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.
08-26-2007 4: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