HELP!Database query performace issue! :(

[Former Member]

Hi Experts,

We are implementing a complicated employee search solution. And now there is a serious performance issue. For example, the following SQL statement takes about 300 seconds at the first query, and about 2.5 seconds after the first query using the same conditions.

SELECT PERNR INTO TABLE T_RESULT FROM PA0001

  WHERE WERKS IN P1  

    AND ZZ_POSLV = '08'.

There are about 10,000,000 records in the tabe PA0001, about 150,000 employee fit the first condition, and 200 employee fit both conditions.

The table PA0001 is SAP buffer disabled, and the ZZ_POSLV field is an customer field without database index.

What's the difference between the first and the second query? How can we improve the performance of the first query?

Best Regards,

Guo Guo Qing

[Former Member]

Hi Guo,

Please follow the below points:

1) Internal table must be declared using TYPE STANDARD TABLE OF and work area must be used instead of HEADER LINE.

2) Always specify as many primary keys as possible in WHERE clause to make the Select efficient. Select should not be performed based on the non-key fields.

3) Using buffered table improves the performance considerably. But in some cases a statement cannot be used with the buffered tables. These statements are:

o Select DISTINCT
o ORDER BY/GROUP BY/HAVING clause
o Anywhere clause that contains a sub query  or IS NULL expression.
o JOINs
o A SELECT….FOR UPDATE.

If the above statements are used on buffer table the buffer will be bypassed. If you want to explicitly bypass the buffer then use the addition BYPASSBUFFER in the SELECT statement.

If any clarification, let me know. Thanks.