HELP!Database 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

[FredericGirod]

Hi,

Is-this the big SELECT statement of your report ? Did you have create Index with the 3 fields ? (MANDT WERKS ZZ_POSLV) ?  can you ?

If you can't and the field WERKS is in an Index, you could create a statement with only WERKS in criteria and after make a filter on the table results.

Second point, how did you declare your table : T_RESULT ?

Third point, did you try to analyse your report with the trans. SE30 ?

Fred

[Former Member]

Thanks for your reply!

I did the test in the SQL performance tool.

And there is a standard index for field WERKS, maybe I can create index for field ZZ_POSLV. But How? Should I create an index for both fields and what's the order? Or just create for the field ZZ_POSLV?

The table T_RESULT just contains one column PERNR with header line.

Best Regards,

Guo Guo Qing

[FredericGirod]

The table declaration is important. If you declare your table with 0 occurs, SAP will create for example the table with 1000 entries. And after will made dynamic allocation memory, and your performance will be poor.

So first declare your table with the size of what you will get.

for the index, ask your system administrator if you can (never create an index of big table without asking the admin)

You could made an SQL trace if you want to know if the index is used in your repport.

[Former Member]

Hi Frédéric,

The final result is less than 200 records, is the internal table is a fator of the issue?

I will try to create index after the approval of system admin. And the SQL statement use the index for WERKS field.

Thanks for your suggestion!

Best Regards,

Guo Guo Qing

[Former Member]

Hi Guo Guo Qing,

In ABAP HR, the basic rule is you have to provide a date range in the where condition of select query. Please try adding  BEGDA and ENDDA and see how the performance improves!

Also as per my understanding, there is just ONE query where is the SECOND query?

[Former Member]

Hi A N,

Thanks for your reply.

Add BEGDA and ENDDA consumed much more time.

Sorry for the misunderstanding, The same query excute for two times, the first time and the second.

Best Regards,

Guo Guo Qing

[vigneshyeram]

Dear GuoQing,

Is the 1st and 2nd query same?

As i understand same query takes less time when it is written same after the 1st query?

Kindly let me know.

Thanks & Regards,

Vignesh Yeram

[Former Member]

Dear Vignesh,

The 1st and 2nd query are totally the same. But the the second time using the same condition is much more fast. How can we improve the performance of the first time query?

Thanks,

Guo Guo Qing

[RaymondGiuseppi]

Check via transaction "ST05 SQL" trace. You should discover that your SQL statement is cached or buffered somewhere in memory even when SAP don't maintain a buffer, database can sometimes perform  it itself. (Check with transaction ST04.)

A classic solution is to schedule a small job that executes at start of system or just before peak hours, so cache or buffer are already available before user executes the transaction. Also check with database managers if the statistics or similar tool are up-to-date in the system.

Regards,

Raymond