topic Re: Table GLPCA in Application Development and Automation Discussions

Table GLPCA

Former Member — Wed, 01 Nov 2006 03:37:24 GMT

Hi,

I am using the following select to get data from table GLPCA... this table has 3 million records just in QA environment... & the program is getting timed out ...

select BLART BUDAT CPUDT DRCRK HSL POPER RACCT RBUKRS REFDOCLN REFDOCNR

RPRCTR RTCUR RYEAR SGTXT USNAM

into corresponding fields of GLPCA

from GLPCA

where BLART in SP$00010

and BUDAT in SP$00004

and POPER in SP$00002

and RACCT in SP$00008

and RBUKRS in SP$00006

and REFDOCNR in SP$00001

and RPRCTR in SP$00007

and RTCUR in SP$00009

and RYEAR in SP$00003

and USNAM in SP$00005.

How can I avoid the program from dumping due "Time_out"

Re: Table GLPCA

Former Member — Wed, 01 Nov 2006 03:51:42 GMT

The only WHERE that can use an index effectively in your select is for REFDOCNR. So before you do the select, make sure SP$00001 is not empty.

Rob

Re: Table GLPCA

Former Member — Wed, 01 Nov 2006 04:15:42 GMT

Hi Rob,

This program is basically used to get all the documents, their line items for all the accounts in a company for a given quarter.

So in the selection screen the user will be just giving the company code, the fisical year and period say '007' to '009'....

-SB

Re: Table GLPCA

Former Member — Wed, 01 Nov 2006 06:46:14 GMT

as you are saying user will enter company code, fiscal year and period, i think the index which suits your case is "Index with origin object / activity", if atall this is existing in your system. over here it contains the following fields:

RHOART Type of origin object (EC-PCA)

KOKRS Controlling Area

RYEAR Fiscal Year

ACTIV FI-SL business activity

POPER Posting period

so i will suggest you if you can find out the

KOKRS Controlling Area

field before retrieving data, then it may help you in getting your data in a faster way. one more thing, if you can avoid writing INTO CORRESPONDING FIELDS OF TABLE GLPCA. if you can design the internal table, in the order you are selecting data, it will be better.

hope it may help you out.

shane

Re: Table GLPCA

Former Member — Wed, 01 Nov 2006 15:46:25 GMT

The more data you retrieve, the less important using an index becomes. So if you need to select a lot of data using non-key fields, there's not much you can do.

Rob

Re: Table GLPCA

Former Member — Fri, 03 Nov 2006 03:15:15 GMT

Hi Shane,

How do I create or use Index in my probelm ?? Please help

Thank You,

SB.

Re: Table GLPCA

Former Member — Fri, 03 Nov 2006 05:50:30 GMT

you check out for the index i suggested in SE11. go to the respective table in SE11. there is one button with caption called "indexes". click on that. you will get list of indexes existing in your system. check out for the index i suggested you in that list. if it is not existing, then you have to create that. create the index in the order of the field which you are using in your select query quite often.

**Note: creating additional index is typically based on need basis and risk factor involved in it is the number of indexes already existing in your system. if number of records in your table is huge and already you are seeing 5 - 6 indexes in your table, then it is not adivisable to create one more index.**

if you are not left with any other choice, then select data in ranges instead of selecting a huge chunk of data in one shot.

shane

Re: Table GLPCA

Former Member — Fri, 03 Nov 2006 16:10:09 GMT

Hi Shane

these are the indexes present...

1 Profit-center-based index Active

2 Index with document number Active

3 Index Through Reference Document Number Active

7 Index with origin object / activity Active

This has the one you mentioned earlier ... 7 Index with origin object / activity Active ... so with this how do I write my select statement ??....

Also please guide me how to select a range of data ...

Thanks for all your help !!

Re: Table GLPCA

Former Member — Fri, 03 Nov 2006 16:25:19 GMT

If there is a one to one correspondence in your system between controlling area (KOKRS) and company code (RBUKRS), you can replace the select on RBUKRS with one on KOKRS. Since you already have RYEAR in the select, this should cause the SELECT to use index 1.

Rob

Re: Table GLPCA

Former Member — Fri, 03 Nov 2006 18:20:39 GMT

I see only one KOKRS values coming up for all the company codes ... that means that the relationship is one Controlling area to many comapny codes ... so does it mean that I cannot use the index here ??

Re: Table GLPCA

Former Member — Fri, 03 Nov 2006 19:22:06 GMT

OK try this:

SELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp[code]SELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.
[/code]3
    AND blart    IN spSELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.
0
    AND budat    IN sp[code]SELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.
[/code]4
    AND poper    IN sp[code]SELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.
[/code]2
    AND racct    IN sp[code]SELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.
[/code]8
    AND rbukrs   IN sp[code]SELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.
[/code]6
    AND refdocnr IN spSELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.

    AND rprctr   IN sp[code]SELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.
[/code]7
    AND rtcur    IN sp[code]SELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.
[/code]9
    AND usnam    IN sp[code]SELECT blart budat cpudt drcrk hsl poper racct rbukrs refdocln refdocnr
       rprctr rtcur ryear sgtxt usnam
  INTO CORRESPONDING FIELDS OF glpca
  FROM glpca
  WHERE kokrs     = 'XXXX'        "Substitute your contolling area
    AND ryear    IN sp$00003
    AND blart    IN sp$00010
    AND budat    IN sp$00004
    AND poper    IN sp$00002
    AND racct    IN sp$00008
    AND rbukrs   IN sp$00006
    AND refdocnr IN sp$00001
    AND rprctr   IN sp$00007
    AND rtcur    IN sp$00009
    AND usnam    IN sp$00005.
[/code]5.

This should give a small increase in performance.

Rob