topic need help in performance in Application Development and Automation Discussions

need help in performance

Former Member — Thu, 13 Mar 2008 10:02:21 GMT

hi,

i do join for this table and its work o.k.

but the time that the bapi run its to much time ,

i need suggestion how to increase performance .

table catsdb is with 10.000.000 records.

Regards

SELECT  catsdb~rnplnr catsdb~catshours  catsdb~raufpl 
              catsdb~status afvc~vornr afvc~ltxa1 
  FROM   (  catsdb
  INNER JOIN   afvc
  ON  afvc~aufpl EQ catsdb~raufpl
  AND afvc~aplzl EQ catsdb~raplzl )
  INTO CORRESPONDING FIELDS OF TABLE c_tab
  WHERE workdate IN per_tab
  AND  status BETWEEN 10 AND  50
  AND rnplnr = proj.

i always ask about 1 project.

Regards

Re: need help in performance

Former Member — Thu, 13 Mar 2008 10:07:17 GMT

Here is my suggestion.

Instead of using corresponding fields of table,, use the internal table with exactly same fields as in your select query.

Join tables on primary keys

Use key fields in where clause

Make secondary indexes if the where clause is not using key fields.

Regards,

Mayank

Re: need help in performance

Former Member — Thu, 13 Mar 2008 10:07:57 GMT

u use into table in place of into corresponding fields of table

Re: need help in performance

Former Member — Thu, 13 Mar 2008 10:17:30 GMT

Hi,

SELECT rnplnr catshours raufpl

status raplzl

FROM catsdb

INTO TABLE i_catsdb

WHERE workdate IN per_tab

AND status BETWEEN 10 AND 50

AND rnplnr = proj.

IF NOT i_catsdb[] IS INITIAL.

SELECT aufpl aplzl vornr ltxa1

FROM afvc

INTO TABLE i_afvc

FOR ALL ENTRIES IN i_catsdb

WHERE aufpl = i_catsdb-raufpl and

aplzl EQ i_catsdb-raplzl.

ENDIF.

SORT : i_catsdb BY raufpl raplzl.

i_afvc BY aufpl aplzl.

LOOP AT i_catsdb.

READ TABLE i_afvc WITH KEY aufpl = i_catsdb-raufpl

aplzl = i_catsdb-raplzl.

IF SY-SUBRC EQ 0.

MOVE require fields to i_final from both internal tables.

APPEND i_final....

ENDIF.

ENDLOOP.

Re: need help in performance

Former Member — Thu, 13 Mar 2008 11:31:46 GMT

hi,

Thanks i try it and let u now.

Regards

Re: need help in performance

Former Member — Thu, 13 Mar 2008 12:15:26 GMT

I can not see any performance improvement here:


SELECT rnplnr catshours raufpl 
   status raplzl 
   FROM catsdb
  INTO TABLE i_catsdb
  WHERE workdate IN per_tab
AND status BETWEEN 10 AND 50
AND rnplnr = proj.

IF NOT i_catsdb[] IS INITIAL.

SELECT aufpl aplzl vornr ltxa1 
FROM afvc
INTO TABLE i_afvc
FOR ALL ENTRIES IN i_catsdb
WHERE aufpl = i_catsdb-raufpl and
aplzl EQ i_catsdb-raplzl. 

ENDIF.

SORT : i_catsdb BY raufpl raplzl.
i_afvc BY aufpl aplzl.

LOOP AT i_catsdb.

READ TABLE i_afvc WITH KEY aufpl = i_catsdb-raufpl
aplzl = i_catsdb-raplzl.

IF SY-SUBRC EQ 0.
MOVE require fields to i_final from both internal tables.
APPEND i_final....
ENDIF.
ENDLOOP.

just the opposite it will become worse.

The Select and the SELECT FOR ALL ENTRIES can not be better than the join.

And the SORT of i_catsdb is unnecessary and the read is missing an binary search, which produces nice quadratic coding,

which can lead to an additional performance problem.

The actual performance problem is related to indexes, indexes, indexes, as always in SQL problems

The table catsdb has 10.000.000 records, which index do you want to use for this part of the Select???

SELECT rnplnr catshours raufpl

status raplzl

FROM catsdb

INTO TABLE i_catsdb

WHERE workdate IN per_tab

AND status BETWEEN 10 AND 50

AND rnplnr = proj.

If this part is solved, then the join will also work and it will be faster than the FOR ALL ENTRIES!

Siegfried

Re: need help in performance

Former Member — Thu, 13 Mar 2008 13:21:21 GMT

hi Siegfried Boes

thanks

i don't need now about indexes i never do select from table with more 10 million records ,

i need to find all the status of hours & hours for project in date that i choose

like for project (rplnr) e0-5024 for 01012007 - 31012007

just it for table catsdb.

Regards

Re: need help in performance

Former Member — Thu, 13 Mar 2008 14:43:39 GMT

Everybody who write selects needs to know what an index is and how it works, starting from very small tables.

Even joins on very small tables will run into performance problems otherwise.

There is no index which your select can use => there is no solution for your problem, I have doubts that you can

create an index in such a large table.

Siegfried

Re: need help in performance

Former Member — Thu, 13 Mar 2008 15:21:30 GMT

What database are you using? When I "explain" this on Oracle, it does a skip_scan of the ~1 index. You may be able to enable the use of that index by adding a line in your where clause, as below.

Skip scans are not as good as a direct read of the index, but it may be better than a full table scan.

where pernr NE ' '

You can also use a hint.

Re: need help in performance

Former Member — Thu, 13 Mar 2008 16:00:59 GMT

Jerry comment might be an option depending on the dtabase platform.

Re: need help in performance

Former Member — Thu, 13 Mar 2008 16:07:43 GMT

HI JERRY

how i now which data base i use ?

and how i use hint?

Regards

Re: need help in performance

Former Member — Thu, 13 Mar 2008 16:37:47 GMT

If you go to "System->Status", it will tell you.

A hint would look like this in your code. There is quite a bit to it. You'd be well served reading the documentation.

%_HINTS ORACLE (USE_INDEX nnn)

Re: need help in performance

Former Member — Thu, 13 Mar 2008 16:44:03 GMT

hi,

my system is MSSQL ,

how i use hints in my select?

Regards

Re: need help in performance

Former Member — Thu, 13 Mar 2008 16:47:05 GMT

Please see note 133381

Re: need help in performance

Former Member — Thu, 13 Mar 2008 16:49:42 GMT

hi jerry

how i do that?

i don't think i can.

regards

Re: need help in performance

Former Member — Thu, 13 Mar 2008 16:51:59 GMT

This message was moderated.

Re: need help in performance

Former Member — Thu, 13 Mar 2008 17:19:28 GMT

HI JERRY,

maybe u can give me example how to use it in my program?

Regards

Re: need help in performance

Former Member — Thu, 13 Mar 2008 17:25:27 GMT

I don't have a MSSQL system to test this. but I assume it would look like this:

SELECT  catsdb~rnplnr catsdb~catshours  catsdb~raufpl 
              catsdb~status afvc~vornr afvc~ltxa1 
  FROM   (  catsdb
  INNER JOIN   afvc
  ON  afvc~aufpl EQ catsdb~raufpl
  AND afvc~aplzl EQ catsdb~raplzl )
  INTO CORRESPONDING FIELDS OF TABLE c_tab
  WHERE workdate IN per_tab
  AND  status BETWEEN 10 AND  50
  AND rnplnr = proj
%_HINTS MSSQLNT 'TABLE catsdb ABINDEX(~1)'.

Re: need help in performance

Former Member — Thu, 13 Mar 2008 19:04:30 GMT

HI JERRY

sorry to bother u but maybe u can explain why u

write

%_HINTS MSSQLNT 'TABLE catsdb ABINDEX(~1)'.

and not like in example

%_HINTS MSSQLNT 'TABLEcatsdb ABINDEX(001), FASTFIRSTROW'.

what it mean this statement?

Regards

Re: need help in performance

Former Member — Thu, 13 Mar 2008 19:09:34 GMT

Hi Ricardo. I was providing a general guideline, rather than concrete example that I had tested. I basically just scanned the note, and picked out code that seemed to fit your case.

If what you uncovered works, then use it.

The best way to tell if the SQL is using the index is to use txn ST05 and do a SQL trace on your ABAP. From there, you'll be able to see the Explain Plan for the statement.

Sometimes, though, a full table scan is the best path. An index read can perform worse.