issue with select query

[Former Member]

Hi all,

I have written a join on MSEG and MKPF table in a program and execute the program in two different systems (say A and B) which has same amount of data and also have the same indexes in the tables.

System A is taking 32000 seconds and System B is taking 600 seconds for the same input and output also same because of similar data.

I tried many options like splitting the join, usage of index, using view WB2_V_MKPF_MSEG2 in place of join etc.. to increase the performance in System A but performance not improved. And one more thing what i observed is if i provide the same inputs for mseg in se11 , it is giving all the required entries in just 100 seconds.

I would like to know the reason why the query in program is taking time in system A. Is it due to any memory issue? Please provide solution for this.

This is my select query

SELECT mseg~mblnr

mseg~bwart

mseg~matnr

mseg~werks

mseg~lgort

mseg~charg

mseg~zeile

mkpf~budat

INTO TABLE P_T_LASTDT

FROM mseg INNER JOIN mkpf

ON mseg_{mandt = mkpf}mandt

AND mseg_{mblnr = mkpf}mblnr

AND mseg_{mjahr = mkpf}mjahr

FOR ALL ENTRIES IN P_T_MCHB

WHERE mseg~matnr = p_t_mchb-matnr

AND mseg~werks = p_t_mchb-werks

AND mseg~lgort = p_t_mchb-lgort

AND mseg~charg = p_t_mchb-charg.

Thanks in advance..

Regards

Shoban

[Former Member]

I would like to suggest one thing, when using tabke in joins use like this

mseg as a l mkpf as b then use a and b instead of entire table names

[faisalatsap]

>

> I would like to suggest one thing, when using tabke in joins use like this
> mseg as a l mkpf as b then  use a and b instead of entire table names

Hi, Kartik

I think this way it will take some more Seconds to Execute, because first it will get meaning of a than b if we will not directly provide table names ?

Please Correct me if wrong

@ Babu: Please try to USE More Meaningful Subject Line

Faisal

[Former Member]

Using alias definately improves performance.

 Reducing Parse Time Using Aliasing A statement which does not have a cached execution plan should be parsed before execution; this parsing phase is a highly time and resource consuming, so parsing time for any sql query must include an alias name in it for the following reason. 1. Providing the alias name will enable the query engine to resolve the tables to which the specified fields belong to. 2. Providing a short alias name, (a single character alias name) is more efficient that providing a big alias name. 

The above text is quoted from the answer given by Seshu Maramreddy in this thread

кu03B1ятu03B9к

[kesavadas_thekkillath]

Kartik thanks for the information.

[Former Member]

Hi,

Try to use

INTO CORRESPONDING FIELDS OF TABLE .

Here is the query:

SELECT a~mblnr

a~bwart

a~matnr

a~werks

a~lgort

a~charg

a~zeile

b~budat

INTO CORRESPONDING FIELDS OF TABLE P_T_LASTDT

FROM mseg as a INNER JOIN mkpf as b

ON a_{mandt = b}mandt

AND a_{mblnr = b}mblnr

AND a_{mjahr = b}mjahr

FOR ALL ENTRIES IN P_T_MCHB

WHERE a~matnr = p_t_mchb-matnr

AND a~werks = p_t_mchb-werks

AND a~lgort = p_t_mchb-lgort

AND a~charg = p_t_mchb-charg.

hope it will hep.

Regards,

Archana

[kesavadas_thekkillath]

Try to pass budat.

If you dont have budat.

try to create a start date and end date from the MJAHR if you have.

try to avoid joins.

• Split the query *

[Former Member]

Hello -

Suggest you to use CURSORS with a packing size. Do not use join; will be very expensive.