topic Re: Problem with SELECT statement in Application Development and Automation Discussions

Problem with SELECT statement

Former Member — Mon, 25 Sep 2006 07:12:40 GMT

Hi Guys,

I am selecting from table AUFM with below query

SELECT mblnr mjahr zeile matnr werks lgort meins

ebeln ebelp aufnr sakto

INTO TABLE iaufm_tmp

FROM aufm

FOR ALL ENTRIES IN x_viaufkst_tmp

WHERE budat GE zp_date

AND aufnr = x_viaufkst_tmp-aufnr .

Now this query is giving short dump with following description

The program "ZNEME362_PRF " has exceeded the maximum permitted runtime and has therefore been terminated.

The internal table x_viaufkst contains more than 3000 records thats why I guess the query is taking much time.

Question 1 :

Whether this query is utilizing the secondary index defined on table AUFM. Table AUFM has a secondary index defined with fields MANDT and AUFNR.

Question 2 :

If answers is No for Question 1, then would following code will solve the problem?

SELECT mblnr mjahr zeile matnr werks lgort meins

ebeln ebelp aufnr sakto

INTO TABLE iaufm_tmp

FROM aufm

FOR ALL ENTRIES IN x_viaufkst_tmp

WHERE aufnr = x_viaufkst_tmp-aufnr .

DELETE iaufm_tmp WHERE budat LT zp_date .

Question 3 :

What is maximum number of records that can be fetched from database to internal table (application server). Is this setting stored somewhere in ABAP?

Re: Problem with SELECT statement

Former Member — Mon, 25 Sep 2006 07:16:55 GMT

Hi pawan,

1. Whether this query is utilizing the secondary index defined on table AUFM. Table AUFM has a secondary index defined with fields MANDT and AUFNR

It must be bcos AUFNR is used in query.

2. then would following code will solve the problem

Yes, it may definitely help in improving the performance,

bcos BUDAT will not be used in where,

and it is not there is any index also.

3. What is maximum number of records that can be fetched from database to internal table (application server). Is this setting stored somewhere in ABAP?

The limit is usually set by physical constraints.

regards,

amit m.

Re: Problem with SELECT statement

Former Member — Mon, 25 Sep 2006 07:18:50 GMT

if there are any duplicate entries in ur internal table x_viaufkst_tmp remove them before the select statemen

delete adjacent duplicates from x_viaufkst_tmp comparing AUFNR.

Re: Problem with SELECT statement

Former Member — Mon, 25 Sep 2006 07:23:44 GMT

I am already doing DELETE ADJACENT on x_viaufkst_tmp internal table.

Re: Problem with SELECT statement

Former Member — Mon, 25 Sep 2006 07:24:11 GMT

create the iaufm_tmp with occurs 0..

and give more conditions in the select statement.

the itab fields should be of the same order as in the select sttement and the fields in select statement should be of the order in the table.

Re: Problem with SELECT statement

Former Member — Mon, 25 Sep 2006 07:29:34 GMT

Hi Amit,

Thanks for your input.

What would you suggest to avoid short dump.

I was in impression that my original select query with BUDAT is not utilizing the index and performace can be improved by removing the BUDAT from where clause.

Since DEV system doesn't have that many data, I can't even simulate the suituation.

Re: Problem with SELECT statement

Former Member — Mon, 25 Sep 2006 07:37:57 GMT

Hi,

in where condn it is not a primary key or sec key

so system giving dump

wat does ur itab1 consists or the table u r fetching contains any common data fld with the second table

so that may give u a soln

also try for gjahr as u alredy have budat

so from this budat take gjahr = budat+4(4)

as gjahr is primary key