need help in performance

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

hi,

Thanks i try it and let u now.

Regards

[former_member194613]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

HI JERRY

how i now which data base i use ?

and how i use hint?

Regards

[Former Member]

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) 

[Former Member]

hi,

my system is MSSQL ,

how i use hints in my select?

Regards

[Former Member]

Please see note 133381

[Former Member]

hi jerry

how i do that?

i don't think i can.

regards

[Former Member]

This message was moderated.

[Former Member]

HI JERRY,

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

Regards

[Former Member]

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)'.

[Former Member]

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

[Former Member]

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.

[former_member194613]

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

[former_member194613]

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

[former_member194613]

@Jerry,

are you sure that MSSQL will work with index gaps? I don't know.

@Ricardo,

Did you think about archiving? I am a bit wondering, how it comes that there are so many records in the table and that you are allowed to write Selects on such a large table.

Siegfried