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: 

Performance of SELECT statement

Former Member
0 Kudos
179

Hi all,

I am facing performance issues (ran trace ST12) with the below portion of code. However, I am not clear as to how this particular part is loading the DB for a longer time and how we can frame it for a better performance. This is a very rudimentary question, but I am just starting out with ABAP so please bear with me. Please let me know if you need anymore information about this section.

"Selection of entries from glpct

     SELECT ryear  "Fiscal Year

            rtcur  "Currency Key

            drcrk  "Debit/Credit Indicator

            rpmax  "Period

            rbukrs "Company Code

            rprctr "Profit Center

            kokrs  "Controlling Center

            racct  "Account

            tslvt  "Starting Balance C.Code Curr

            tsl01

            tsl02

            tsl03

            tsl04

            tsl05

            tsl06

            tsl07

            tsl08

            tsl09

            tsl10

            tsl11

            tsl12

            tsl13

            tsl14

            tsl15

            tsl16

            hslvt  "Balance in C.Code Currency

            hsl01

            hsl02

            hsl03

            hsl04

            hsl05

            hsl06

            hsl07

            hsl08

            hsl09

            hsl10

            hsl11

            hsl12

            hsl13

            hsl14

            hsl15

            hsl16

            kslvt  "Starting Balance Prof. Cntr. Local Curr.

            ksl01

            ksl02

            ksl03

            ksl04

            ksl05

            ksl06

            ksl07

            ksl08

            ksl09

            ksl10

            ksl11

            ksl12

            ksl13

            ksl14

            ksl15

            ksl16

       FROM glpct

       INTO TABLE i_glpct

       FOR ALL ENTRIES IN li_cepc

       WHERE rldnr EQ lc_rldnr_8a

         AND rrcty EQ 0

         AND rvers EQ lc_rvers_0

         AND ryear EQ p_ryear

         AND rbukrs IN lr_rbukrs

         AND rprctr EQ li_cepc-prctr

         AND rhoart IN s_rhoart

         AND racct IN s_racct.

1 ACCEPTED SOLUTION

Former Member
0 Kudos
106

It should go much more quickly if you can add field KOKRS to the SELECT statement. That may allow you to use the profit center index.

Rob

5 REPLIES 5

raymond_giuseppi
Active Contributor
0 Kudos
106

Execute a SQL trace (ST05) to understand which index uses (and didn't use) the SQL optimizer .

(e.g. GLPCT~1 or primary index GLPCT~0)

Hint: Start with some document as The SQL Trace (ST05) – Quick and Easy.

Regards,

Raymond

jack_graus2
Active Contributor
0 Kudos
106

Hi

The selection criterion seems to be year (RYEAR) with profit center (RPRCTR).

There exists an index on profit center for this table but only together with controlling area.

Could you add controlling area (KOKRS) in your selection ?

Controlling area can be derived from company code and company code is already part of your selection. A function module to do so is RK_KOKRS_FIND.

Regards Jack

0 Kudos
106

Hi Jack,

Thanks for your response. But does the format of the select statement and how it is calling the different columns of the table seem right to you? Because I was thinking I needed to change the format in which I am framing the select statement.

The solution that you provided seems to make sense to me. But how can I incorporate controlling area in the statement. Sorry again about the obvious questions, but I am a beginner. Appreciate your help.

Former Member
0 Kudos
107

It should go much more quickly if you can add field KOKRS to the SELECT statement. That may allow you to use the profit center index.

Rob

0 Kudos
106

Hi Mitra,

Make sure that in the below cases your internal table li_CEPC check is there.

Step 1:As metntioned above please include the Controlling Area(KOKRS) in the selection screen (like how you added rvers,ryear...)

Step 2: After that include same filed(for example rkokrs) in the where clause of your select statement.

i thin it will improve the performance.

OR

Step1. After added the controlling area(KOKRS).

Step2. Creat a secondary index with the below fields(fields should be same sequence which are mentioned in Where clause of select Stmt.) ---->example you have created a index name as ZPC

rldnr

rrcty

rvers

rkokrs(newly added field)

ryear

rbukrs

rprctr

rhoart

racct


Step 3: use the same line at the end of the select sttatement.

                %_HINTS ORACLE 'INDEX("Table Name"  "Tablename~Indexname")'.


(Example is:Here i have considered JEST table.               %_HINTS ORACLE 'INDEX("JEST"  "JEST~Z02")'.)


Regards,

Srini.