topic Re: issue with select query in Application Development and Automation Discussions

issue with select query

Former Member — Thu, 01 Oct 2009 02:58:19 GMT

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

Re: issue with select query

Former Member — Thu, 01 Oct 2009 04:07:28 GMT

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

Re: issue with select query

faisalatsap — Thu, 01 Oct 2009 04:43:40 GMT


> 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

Re: issue with select query

Former Member — Thu, 01 Oct 2009 05:18:22 GMT

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

Re: issue with select query

kesavadas_thekkillath — Thu, 01 Oct 2009 05:26:41 GMT

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 *

Re: issue with select query

Former Member — Thu, 01 Oct 2009 05:38:23 GMT

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к

Re: issue with select query

Former Member — Thu, 01 Oct 2009 05:46:21 GMT

Hello -

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

Re: issue with select query

kesavadas_thekkillath — Thu, 01 Oct 2009 06:39:55 GMT

Kartik thanks for the information.