Performance issue

[Former Member]

Hi gurus,

Select query shown below takes about 10 to 15 minutes to complete or execute. Here gt_matnr is having approximate 4000 data entries. Please suggest any alternative way to write this query. Should I need to split this query in two?

IF gt_matnr[] IS NOT INITIAL

SELECT

DISTINCT

a~mblnr

a~mjahr

a~bwart

a~matnr

a~werks

a~lgort

a~charg

a~lifnr

a~menge

a~meins

a~erfmg

a~erfme

a~aufnr

a~ebeln

a~umwrk

a~hsdat

a~SHKZG

b~bldat

b~cputm

INTO TABLE gt_mseg

FROM mseg AS a

INNER JOIN mkpf AS b ON

a_{mblnr = b}mblnr AND

a_{mjahr = b}mjahr

FOR ALL ENTRIES IN gt_matnr

WHERE a~matnr = gt_matnr-matnr AND

a~werks IN s_werks AND

a~mblnr IN s_mblnr AND

a~mjahr IN s_mjahr AND

a~bwart IN s_bwart AND

a~kzbew IN s_kzbew AND

a~kzzug IN s_kzzug AND

a~sobkz IN s_sobkz AND

b~xblnr IN s_xblnr AND

a~aufnr IN s_aufnr AND

a~ebeln IN s_ebeln AND

b~CPUDT GE p_datum and

b~cputm GT p_uzeit.

ENDIF.

Thanks in advanced.

[Former Member]

Hi...

Add this code before checking for initial condiditon..

sort gt_matnr by matnr.
delete adjacent duplicates from gt_matnr comparing matnr.

Cheers...

[Former Member]

I have sorted gt_matnr by matnr at just before select query. Thnaks for reply

[Former Member]

If using For all entries and delete adjacent duplicates doesnt help..

the best solution is to split the query.. no other way.. i guess......

You can even see..whether its possible to give any other selection criteria also.. but .. you have already given enough i think...

[former_member404244]

Hi,

Avoid DISTINCT statement in the select query and see.

Regards,

Nagaraj

[Former Member]

Hi,

How abt trying with for all entries.

Regards.

[Former Member]

Hi,

Split this in two queries.

Fiirst perform inner join , then do for all entries.. Performance should increase and also check if u can create index.

Regards

Vinod

[Former Member]

One more point apart from performance is

The below statement will miss some entries

b~CPUDT GE p_datum and

b~cputm GT p_uzeit.

(in the above case the time will be checked for all days and all the entries will be skipped if created time is less than the given time)

So that you have to use like

b_{CPUDT GE p_datum. (In the select statement remove b}cputm GT p_uzeit.)

Delete table gt_mseg where CPUDT = p_datum and CPutm > p_uzeit

Regards

Sasi

[Former Member]

Thanks for reply but select query is working correct only thing is that it is taking 15 mins to run.

[Former Member]

Snehal..

If the select query is still using more that 15 min..after using FOR ALL ENTRIES then... split the select query...i dont think there is any other alternative..

And.

Also.. have a look at Sasi's point... i think he is correct...but it all depends on the scenarios.....

Cheers..

[Former Member]

>

> Snehal..
> 
> If the select query is still using more that 15 min..after using FOR ALL ENTRIES then... split the select query...i dont think there is any other alternative..
> 
> And.
> 
> 
> Also.. have a look at Sasi's point... i think he is correct...but it all depends on the scenarios.....
> 
> Cheers..

I don't understand this, what do you mean by split the query?

Anyway, if from 4000 records of gt_matnr, none of them has duplicate material number, delete adjacent duplicates will not affect anything. Instead, delete adjacent duplicates in gt_matnr itself will add more cost to the running time of the program.

And yes snehal, sasi is correct. If your query give correct result now, it does not mean it will give correct result for all cases. If your date is 28th of December and yoru time is 10 AM, you will only get data that creation time is greater than 10 am. You will miss data that created in 29th of December from 00:00 to 10 AM and 30th of December from 00:00 to 10 AM.

Try do a sql trace also and check whether your select statement hit the index or not.

Regards,

Abraham

[Former Member]

Abraham..

You have a point buddy...... but..as per the given code .... i am not really sure whether GT_MATNR has MATNR has key field.. if thats the case..DELETE ADJACENT is not required..but otherwise..it is...!!....

So..it all depends on .. how GT_MATNR is populated...

...