topic Re: help in performance in Application Development and Automation Discussions

help in performance

Former Member — Thu, 21 Feb 2008 10:18:49 GMT

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.

Re: help in performance

Former Member — Thu, 21 Feb 2008 10:22:06 GMT

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

Re: help in performance

ThomasZloch — Thu, 21 Feb 2008 10:29:17 GMT

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

Re: help in performance

Former Member — Thu, 21 Feb 2008 10:56:29 GMT

hi ,

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

regards

Re: help in performance

ThomasZloch — Thu, 21 Feb 2008 11:41:08 GMT

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

Re: help in performance

Former Member — Thu, 21 Feb 2008 12:33:39 GMT

@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

Re: help in performance

Former Member — Thu, 21 Feb 2008 12:57:06 GMT

hi Siegfried Boes

we have 10,758,930 entries in catsdb table

Regards

Re: help in performance

Former Member — Thu, 21 Feb 2008 13:03:16 GMT

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

Re: help in performance

Former Member — Thu, 21 Feb 2008 13:36:05 GMT

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

Re: help in performance

ThomasZloch — Thu, 21 Feb 2008 15:35:47 GMT

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

Re: help in performance

Former Member — Thu, 21 Feb 2008 16:15:35 GMT

> 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

Re: help in performance

Former Member — Thu, 21 Feb 2008 18:34:56 GMT

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