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

[marcin_cholewczuk]

Hi,

It's probably buffered on SQL server side. Check under ST05 (with Explain button) if the right access method and index is being used. It is also possible to check table characteristics there.

Best Regards

Marcin Cholewczuk

[Former Member]

Hello,

For large set of records its always advisable that proper index is utilised, you can check this with the help of ST05/SE30/STAT.

If still the same issue then you can extract the data in chunks say 10k records each time. So in your first select query extract pernr upto 10k rows appending table...

In this way you can improve performance on your first query.

regards,

Deepti

[Former Member]

Hi Deepti,

Thanks for your reply.

If I just extract the 10K records in the first query, and they are not what I need. How can I select the next 10K records?

Regards,

Guo Guo Qing

[marcin_cholewczuk]

Hi,

Try using PACKAGE SIZE addition to SELECT command (http://www.erpgreat.com/abap/package-size.htm), but I'm not sure if it will help you (it supports you when you're using too much AS memory, not when you're hitting problems with DB access).

BR

Marcin Cholewczuk

[Former Member]

Hello,

Sorry for delay in response.

Please check below link for extracting data in chunks.

http://wiki.sdn.sap.com/wiki/display/ABAP/How+to+Select+data+from+large+tables

[Former Member]

Hi guoqing,

I think yo should create an index in PA0001 on field ZZ_POSLV. We had the same performance issue while fetching the data from BSEG. We created an Index for the fields on which our selection was, and it improved the performance to a lot extent.

Hope this helps.

Regards,

Dipesh

[jayanthi_jayaraman]

Hi,

DO you want all the records in one shot? If not, then you can use LDB.

If you want records in one set, try using werks as select condition. ANd then since zz_poslv is any way hard code, write another delete statement in the internal table for zz_poslv <> '08', once the selection is done.