topic Re: Lack Performance when select from GLPCA in Application Development and Automation Discussions

Lack Performance when select from GLPCA

Former Member — Fri, 02 Apr 2010 10:15:11 GMT

Hi Experts,

I have the following select statement from table GLPCA.

SELECT RLDNR RRCTY RVERS RYEAR RTCUR DRCRK

RBUKRS RPRCTR KOKRS RACCT TSL HSL

INTO TABLE GT_GLPCA

FROM GLPCA CLIENT SPECIFIED

WHERE RCLNT EQ SY-MANDT

AND RLDNR EQ CC_C8A

AND RRCTY IN S_RRCTY

AND RVERS IN S_RVERS

AND RYEAR EQ JAHR

AND POPER EQ MONAT

AND RBUKRS EQ SKB1-BUKRS

AND KOKRS EQ CC_CUOBM

AND RACCT EQ SKB1-SAKNR

AND HSL NE '0'

AND TSL NE '0'

AND BUDAT IN S_BUDAT1.

However, this select query took really long time to retrieving the data for only 1 GL account.

I don't have key GL_SIRID at this point.

May I know is there any way I can enhance the performance?

===================================

Please assume data as below:

CC_C8A is constant '8A'.

S_RRCTY is '0'.

S_RVERS is '0'

JAHR is '2009'

MONAT is '12'

SKB1-BUKRS and SKB1-SAKNR are taking from logical database GET SKB1 (only 1 GL).

CC_CUOBM is constant 'UOBM'

S_BUDAT1 is from 01.12.2009 to 20.12.2009

===================================

Hope someone may help! Thank you very much!!

Regards,

Darren

Re: Lack Performance when select from GLPCA

Former Member — Fri, 02 Apr 2010 10:32:37 GMT

If you are having the value for field "RPRCTR", then use the sequence mentioned in the index "GLPCA~1"(KOKRS,RYEAR,RPRCTR,RVERS,RACCT) in your where clause.

Filter out the unwanted records from your internal table after the selection.

Regards

Vinod

Re: Lack Performance when select from GLPCA

Former Member — Fri, 02 Apr 2010 11:34:22 GMT

Use of NOT operators (NE) assure a FULL TABLE SCAN....don't use NE in where clauses. Client-specified is meaningless if you're selecting with sy-mandt....remove that...

Re: Lack Performance when select from GLPCA

Former Member — Fri, 02 Apr 2010 17:15:51 GMT


> Use of NOT operators (NE) assure a FULL TABLE SCAN....don't use NE in where clauses...

That's incorrect.

Rob

Re: Lack Performance when select from GLPCA

Clemenss — Fri, 02 Apr 2010 18:21:36 GMT

Hi Chuan Ng,

you have lots of documents in this table. Try to get possible GL_SIRIDs first so that you can make use of the primary index of the table. In the where-used list of the referenced data element GU_RECID shows a lot of tables, try to get faster access by selecting firrst from there and selecting from GLPCA with FOR ALL ENTRIES IN <itab with GL_SIRIDs>.

Possible table to select from first

COFIP                            Single plan items for reconciliation ledger
COFIS                            Actual line items for reconciliation ledger
ECMCA                            SAP Cons.: Journal Entry Table (Actual)
FBICRC001A                       ICRC: Open Items GL Accounts: Documents

... or check the where-used-list what else may help.

Regards,

Clemens

Re: Lack Performance when select from GLPCA

Former Member — Sun, 04 Apr 2010 15:46:34 GMT

Thanks for all replied!!

Dear Vinod,

I don't have RPRCTR at this point as well.

Dear Short Dump,

May I know why client specified by sy-mandy is meaningless here?

Dear Rob,

Can you explain?

Dear Clemens,

Is there no other way to get fast access without GL_SIRID?

Thank you for the suggestions.

Regards,

Darren

Re: Lack Performance when select from GLPCA

Clemenss — Sun, 04 Apr 2010 18:11:24 GMT

Hi Chuan Ng,

although you did not ask me for that:

CLIENT SPECIFIED WHERE RCLNT EQ SY-MANDT .. CLIENT SPECIFIED is always meaningless because without the addition CLIENT SPECIFIED the select will always done from the current SY-MANDT. Some SAP centuries ago, some developers believed they could do a favor to the database by explicitly specifying what the database interface would do anyway. Others used this to confuse the spies that stole the software

The second, more important question, the way to get fast access: If you don't get the primary key GL_SIRID value, you may be able to use one of the secondary keys (find in SE11/12 utilities -> database object -> display, scroll down)

Index GLPCA~1 
KOKRS 
RYEAR 
RPRCTR 
RVERS 
RACCT 

Unique index GLPCA~2 
DOCNR 
RYEAR 
DOCCT 
RBUKRS 
RLDNR 
RCLNT 
DOCLN 

Index GLPCA~3 
REFDOCNR 
REFRYEAR 
REFDOCCT 
RBUKRS 
RLDNR 
REFDOCLN 
RCLNT 

Index GLPCA~7 
RHOART 
KOKRS 
RYEAR 
ACTIV 
POPER

Note: You must specify at least a condition for the first field of any index so that the databse can make use of it. The more fields of the index you specify, the better the access, but it must always be complete starting from first field.

In your SELECT, you use all fields of secondary index #1, only RPRCTR is missing. OK, you must specify distinct values in the SELECT-OPTIONS. If you leave index fields blank, the index can not be used.

I'm convinced if you specify values for all fields KOKRS RYEAR RPRCTR RVERS RACCT it will be faster than light.

Regards,

Clemens

Re: Lack Performance when select from GLPCA

Former Member — Sun, 04 Apr 2010 18:47:24 GMT

The use of 'NE' in a WHERE clause does not "guarantee" a full table scan. In many cases, like the one here, you do what you have to do. You will probably end up with a full table scan anyway, but that's because you aren't using any indexes not the use of 'NE'.

Your best solution here is probably to simply run it in the background and move on.

Rob

Re: Lack Performance when select from GLPCA

Former Member — Thu, 22 Apr 2010 16:49:17 GMT

Its better to create a secondary index for all the fields you are using. But if still problem persists then scheduling in background option is also good if possible.

Re: Lack Performance when select from GLPCA

Former Member — Thu, 22 Apr 2010 17:07:40 GMT

Creating a secondary index on a standard SAP table to speed up a single SELECT is not a good solution.

Rob