HELP! Database query performance issue!

[Former Member]

Hi Experts,

We are implementing a complicated employee search solution in the biggest human resource system on earth. And now there is a serious database query performance issue. It takes more than 300 seconds to excute the following SQL statement 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 table PA0001, 150,0000 records fit the first condition, and 200 records fit both conditions.

Table PA0001 is SAP buffer disabled, and field ZZ_POSLV is customer field without database index.

What's the difference between the first query and the second? How can we improve the performace of the first time query? The performance of the second is acceptable.

Best Regards,

Guo Guo Qing

[former_member197561 · Accepted solution]

Dear Guo Guo Qing,

The difference is in the state of the database cache.

Please,  define index on the table PA0001 with just those 2 columns "WERKS" and "ZZ_POSLV" included and retry the statement.

Best Regards,

Sylvia

[Former Member · Accepted solution]

Dear Sylvia,

Thanks for your reply!

There is an index for 'WERKS' field.

But we can't define index include the two field at the same time, because there are many other conditions like this. For example, BUKRS and ZZ_POSLV, PERSG and ZZ_POSLV and so on....

Best Regards,

Guo Guo Qing

[former_member197561 · Accepted solution]

Hi Guo Guo Qing,

why not?

There could be as many indexes defined on database table as appropriate.

A "select" operations is fast only if there is index which matches the fields of the "where clause" in that select statement.

How about specifying an index for field "ZZ_POSLV" and then use it as first where-clause member in your select?  e.g.

SELECT PERNR INTO TABLE T_RESULT FROM PA0001

  WHERE ZZ_POSLV = '08' AND  WERKS IN P1.

The "=" is more selective then the "IN" and possibly you will gain speed.

Best regards,

Sylvia

[Former Member · Accepted solution]

Thanks! I will try the second solution. But why do the the second query is much more fast than the first time?

Best Regards,

Guo Guo Qing

[former_member197561 · Accepted solution]

Hi Guo Guo Qing,

This is a proposal to check. You said that "150,0000 records fit the first condition" but you did not say how many fit the ZZ_POSLV  condition only... If they are less (and would be less  in principle), then the selectivity of the statement would be improved ...

Best regards,

Sylvia

[Former Member · Accepted solution]

Hi Sylvia,

There records fit the ZZ_POSLV condition are much less than the WERKS condition.

Should I put the ZZ_POSLV first?  Or just using one condition in where clause and filt the result using another condition in the internal table?

Best Regards,

Guo Guo Qing

[former_member197561 · Accepted solution]

Hi Guo Guo Qing,

yes, if "records fit the ZZ_POSLV condition are much less than the WERKS condition" , please define in the table a new index only on column ZZ_POSLV and arrange the statement like this

SELECT PERNR INTO TABLE T_RESULT FROM PA0001

  WHERE ZZ_POSLV = '08' AND  WERKS IN P1.

Then measure it and if you are ok with the execution time, then leave it like this.

If you like to improve it further then you need to change the index to contain both ZZ_POSLV and WERKS, having them in the index ordered in same order as in the sql statement.

Best Regards,

Sylvia