09-22-2009 9:39 PM
I have a table with 50 million records. The SELECT statement for this table takes far too long. How can I improve the performance?
Creating an index does not seem to be a feasible solution as the selection criteria is rather limited.
09-22-2009 9:47 PM
It is a custom table for profitability analysis. There are approx. 200 fields, here are some of them
MANDT MANDT CLNT 3 0 Client
PALEDGER LEDBO CHAR 2 0 Currency type for an operating concern
VRGAR RKE_VRGAR CHAR 1 0 Record Type
VERSI RKEVERSI CHAR 3 0 Plan version (CO-PA)
PERIO JAHRPER NUMC 7 0 Period/year
PAOBJNR RKEOBJNR NUMC 10 0 Profitability Segment Number (CO-PA)
PASUBNR RKESUBNR NUMC 4 0 Profitability Segment Changes (CO-PA)
BELNR RKE_BELNR CHAR 10 0 Document number of line item in Profitability Analysis
POSNR RKE_POSNR CHAR 6 0 Item no. of CO-PA line item
HZDAT ERDAT DATS 8 0 Date on Which Record Was Created
USNAM ERFASSER CHAR 12 0 Created by
GJAHR GJAHR NUMC 4 0 Fiscal Year
PERDE PERIODE NUMC 3 0 Period
WADAT WADAT DATS 8 0 Goods Issue Date
FADAT FADAT DATS 8 0 Invoice date (date created)
Upto the field POSNR, they are all key fields. The only filtering criteria I have is VRGAR. So as you can see the amount of data been extracted will be huge. Currently I am looking into the possibility of using more selection criteria using GE greater than.
09-22-2009 9:44 PM
Is this custom table or standard SAP table? Is archiving some of this data a viable option?
09-22-2009 9:47 PM
It is a custom table for profitability analysis. There are approx. 200 fields, here are some of them
MANDT MANDT CLNT 3 0 Client
PALEDGER LEDBO CHAR 2 0 Currency type for an operating concern
VRGAR RKE_VRGAR CHAR 1 0 Record Type
VERSI RKEVERSI CHAR 3 0 Plan version (CO-PA)
PERIO JAHRPER NUMC 7 0 Period/year
PAOBJNR RKEOBJNR NUMC 10 0 Profitability Segment Number (CO-PA)
PASUBNR RKESUBNR NUMC 4 0 Profitability Segment Changes (CO-PA)
BELNR RKE_BELNR CHAR 10 0 Document number of line item in Profitability Analysis
POSNR RKE_POSNR CHAR 6 0 Item no. of CO-PA line item
HZDAT ERDAT DATS 8 0 Date on Which Record Was Created
USNAM ERFASSER CHAR 12 0 Created by
GJAHR GJAHR NUMC 4 0 Fiscal Year
PERDE PERIODE NUMC 3 0 Period
WADAT WADAT DATS 8 0 Goods Issue Date
FADAT FADAT DATS 8 0 Invoice date (date created)
Upto the field POSNR, they are all key fields. The only filtering criteria I have is VRGAR. So as you can see the amount of data been extracted will be huge. Currently I am looking into the possibility of using more selection criteria using GE greater than.
09-22-2009 10:32 PM
You can try adding all possible values of LEDBO to the SELECT, but you will probably return so many records that an internal table may not be able to hold all the recods without dumping.
Rob
09-23-2009 12:58 AM
Hey Sajid, <li>Paste the SELECT query which is being used right now or you are using. So that we can have a look into that . Thanks Venkat.O
09-23-2009 4:29 AM
I would suggest you to
1. Use primary index in your logic. You can check the index being used by ST05.You need to check for this in quality system also.
2. Try to use as many where conditions as possible.
3. You should make some parameters as mandatory , so that records fetched are minimised.
I can view that dates fields are avaliable in your table and these can be good to filter the required data.
Also, you can also do some variations and test like useing PACKAGE SIZE option with select.
I would also suggest you to ask client about the frequency of execution of this report and how much data is required in each go.
Hope it helps you.
09-23-2009 4:44 AM
Hi,
Using the option GE (greater equal) in your coding can improve your performance considerably without the risk of table lines not being selected.
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.
Use all the primary keys in select query
Thanks,
Krishna