help in performance

[Former Member]

hi,

i have this join there is way that i can improve this performance

regards

SELECT catsdb_{rnplnr catsdb}catshours catsdb~workdate

catsdb_{status afvc}vornr afvc~ltxa1

FROM ( catsdb

LEFT OUTER JOIN afvc

ON afvc_{aufpl EQ catsdb}raufpl

AND afvc_{aplzl EQ catsdb}raplzl )

INTO CORRESPONDING FIELDS OF TABLE c_tab

WHERE workdate IN period

AND rnplnr = project.

[Former Member]

hi,

i have this join there is way that i can improve this performance

regards

SELECT catsdb_{rnplnr catsdb}catshours catsdb~workdate

catsdb_{status afvc}vornr afvc~ltxa1

FROM ( catsdb

LEFT OUTER JOIN afvc

ON afvc_{aufpl EQ catsdb}raufpl

AND afvc_{aplzl EQ catsdb}raplzl )

INTO CORRESPONDING FIELDS OF TABLE c_tab

WHERE workdate IN period

AND rnplnr = project.

[Former Member]

Hi

USE FOR ALL ENTRIES THAT WILL REDUCE THE LOAD ON DATA BASE

IF YOU USE JOINS THEN DATA BASE CONNECTIVITY WILL BE THERE UP TO THE PROGRAM EXECUTION RESULT IN WHICH LOAD ON DATA BASE

IF YOU USE FOR ALL ENTRIES THE DATA WILLBE RETRIVED VERY FIRST AND THERE WON'T BE ANY DATA BASE CONNECTIVITY

[ThomasZloch]

the join condition is OK, however you are not using a primary or secondary key when selecting from CATSDB.

Analyse the keys of table CATSDB and see if you can fill any of these keys top down as much as possible when selecting the data.

Greetings

Thomas

[Former Member]

hi ,

u can give me E.G . how to use it?

regards

[ThomasZloch]

well, check out CATSDB in SE11.

primary key is COUNTER. Not available as selection criteria? We have to look at the secondary indexes:

1 Index for Personnel Number and Date

2 Index for Document Number

3 Index for Personnel Number and Work Item

4 Index for Personnel Number, Status, Date

5 Index for External Document Number

6 Index for Objekt-Id (ARBID)

now I don't want to list all key fields here, you can check that yourself in SE11. The first one for example has fields PERNR and WORKDATE (in that order). In your select you are using WORKDATE and another field. If you could include a narrow selection on PERNR in your select, performance would improve significantly.

If all else fails, you can create your own secondary index, however this occupies additional disk space and increases DB load slighty on each insert/update operation.

Greetings

Thomas

[Former Member]

hi Thomas Zloch

i need to take from catsdb network , project

hours ,status .

what u say is to take all employee of project and to use maybe

for all entris with pernr key ?

Regards

[ThomasZloch]

if that gives you a SMALL selection of PERNRs, it's worth a try.

otherwise I'll leave you with Siegfried, you're in good hands.

Cheers

Thomas

[Former Member]

Thomas - creating a secondary index for one SELECT in one report is overkill. Eventtually, what quite often happens is that the report is run rarely anyway and finally becomes obsolete, but the index remains along with the associated overhead.

If the poster cannot find a workaround, it can just be run in the background when users are off the system.

Rob

[Former Member]

@Thomas,

you should not really recommend a beginner to create an index. The effect can be very counterproductive as other applications can be effected.

@Ricardo

please ignore the spam mail on the FOR ALL ENTRIES, it is incorrect.If there is no index support then also FOR ALL ENTRIES is slow.

With proper index support a join is faster.

How many entries are in the CATSDB in your system, check with SE11?

Siegfried

[Former Member]

hi Siegfried Boes

we have 10,758,930 entries in catsdb table

Regards

[Former Member]

the FOR ALL ENTRIES will change nothing, there you would have to start with this SELECT

SELECT catsdb_{rnplnr catsdb}catshours catsdb~workdate

catsdb~status

FROM catsdb

INTO CORRESPONDING FIELDS OF TABLE c_tab

WHERE workdate IN period

AND rnplnr = project.

which is again slow because it is not index supported and

your table has 10.000.000 records.

Please check with the SE11 have many records fulfill your condition for workdate and project. Also this will take a while.

In principle it would be ncessary to create an index with rnplnr and workdate. But your table has 10.000.000 records, so the index will also be large.

Is your applcation really important? The new index will slow down other applications (additional index update). Maybe it is not so important because it runs very rarely.

You should check whether CATSDB data can be archieved or deleted.

When the select is optimized then the join is just a little overhead, and should be much faster than a FOR ALL ENTRIES.

Siegfried

[Former Member]

> The first one for example has fields PERNR and

> WORKDATE (in that order).

If you can get the pernr for your project from another table

then it could work.

Try

SELECT catsdb_{rnplnr catsdb}catshours catsdb~workdate

catsdb~status

FROM catsdb

INTO CORRESPONDING FIELDS OF TABLE c_tab

WHERE

pernr = ( select pernr from ???? where project = project)

AND workdate IN period

AND rnplnr = project.

Siegfried