Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Improve performance of query

Former Member
0 Kudos

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.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

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.

6 REPLIES 6

Former Member
0 Kudos

Is this custom table or standard SAP table? Is archiving some of this data a viable option?

Former Member
0 Kudos

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.

0 Kudos

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

venkat_o
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

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