Select query executing very slow the first time.

[Former Member]

Hi All,

I am using a query in my custom function module. This query takes too long to execute the first time in a day. From second time onwards it takes just seconds to execute.

SELECT  p~lgnum p~tanum p~tapos p~matnr  t~maktx AS matkx
             p~charg p~werks p~umrez AS umrez_sku_r p~umren AS umren_sku_l
             p~meins AS meins_r p~altme AS altme_l p~nistm AS nistm
             p~letyp p~vltyp p~vlber  p~vlpla p~nltyp p~nlber p~nlpla
       INTO TABLE zmseg_del
       FROM ltap AS p INNER JOIN makt AS t
       ON p~matnr EQ t~matnr
       FOR ALL ENTRIES IN  goodsmvt_items
       WHERE charg = goodsmvt_items-batch
             AND t~spras = 'E'
             AND vltyp IN ('901', '902').

Please let me know what could be the problem and possible solutions for this.

[adam_krawczyk1]

Hi Ramesh,

In general FOR ALL ENTRIES IN with JOIN may result in low performance.

First of all you need to make sure that table goodsmvt_items is not initial and values are distinct by BATCH field. If not then copy only unique goodsmvt_items-BATCH values to new temp table and use it for query.

There are many factors that decides about query performance, like size of ltap and makt table, data distribution and size of goodsmvt_items in your case. You can also try to measure different approaches and see what gives best results in your case. You can try:

- Use third table in JOIN instead of FOR ALL ENTRIES IN goodsmvt_items.

- If you know that WHERE condition will often not find rows, you can build two separate select queries - select first only from LTAP table for ALL ENTRIES IN goodsmvt_items and then for result set do JOIN on MAKT table (or other way - select from MAKT, then join with LTAP).

To compare execution time do measurements with ST05 or SAT (SE30) for same input/output data. Of course, execution time of subsequent run will be much faster than running query on next day (because of buffering, caching etc.) but you can anyhow compare different queries efficiency to each other and find best one.

Regards,

Adam

[Former Member]

HI

Do you have material number or plant in the internal table goodsmvt_items?? if so provide it in the where condition.is batch number is an index (secondary) field? If no its better to avoid direct selection from mseg.may be you can get the material doc from lips (LFGJA,LFBNR,LFPOS) - or from vbfa with orginal number ,line item as delivery- if you already have delivery number

cheers

[Former Member]

Hi Adam / Mukundan,

I have Material Number and Plant and added that to the where condition. Batch number is not a secondary index field and I need to fetch the data from LTAP table only.

I am not able to do proper runtime analysis with SAT. Here is what i did in SAT

1. I executed the FM with the select query without any changes(as shown above). It showed 1,104,691 microsecs for the DB Fetch.
2. I added Material # and Plant to the where condition and executed with SAT. Results showed 405,069 microsecs.
3. I rolled back to the original query and tried with SAT again. Results showed 4,163 microsecs.

I am not able to get definite results because of buffering/caching

[adam_krawczyk1]

Hi Ramesh,

That is normal that second and next repetition of query gives much faster results than first run. That is why you need to compare different solutions after few query runs, to have comparable results (so called "warmed up system"). In this way you may find most efficient algorithm for your needs.

With SAT / SE30 you can find exactly the place which takes most time. If you see it is a database query, try to measure it with ST05, as you probably did. Then open menu option "Trace List -> Summarized by SQL statement" and check:

- Redundancy column (ideally should have value 0 which means that there were no duplicated queries).

- Number of executions to see how many times query was executed (it is better to have one query with bigger result than separated queries with partial results).

- BfTp - Type of buffer to see if any of tables you use are buffered.

- Explain statement, to see if indexes are used.

By the way, 1 second for database query is not long time in case of large tables and query with JOIN and FOR ALL ENTRIES. Anyhow if database queries are slowest part of your program, review and match indexes. Perfectly matched index for your query would be on BATCH, SPRAS and VLTYP, but having BATCH only should help as well. Creating good indexes is the key factor to have quick query execution also for the first time.

Regards,

Adam

[Former Member]

also,how this goodsmvt_items table gets filled,is it filled from mseg??

if so..mseg has these fields lgnum & TANUM .select them into goodsmvt_items.and have those along with the other where condition- batch number ,material.so that you can fetch data from ltap using the key fields.i could not see the transfer order line item(tapos) in mseg (still batch number ,materila & plant in the where condition will help to pick the unique record)

cheers

Mukundan.R