Select Query

[Former Member]

I have to tune a select stmnt which was working fine in prod uptil a week age. Now this query is giving core dump. May be because of the increasing number of data in the prod.

the query is like..

SELECT a~QMNUM

a~objnr

a~bstnk

a~qmart

a~KUNUM

a~zztrjobnum

a~QMTXT

a~ADRNR

a~zzoutcm

a~ZZADDLWORK

a~tplnr "change kk080

a~btpln "addition RC232 E502860820

a~erdat

b~erdat

b~erzeit

a~strmn

a~equnr

a~bequi

a~zzdisp_date

a~zzdisp_time

• a~zzmedsub

a~zzdlvstat

a~ernam

b~pster

b~pstur

b~mncod

b~manum

• appending corresponding fields of table ITAB

INTO (ITAB-qmnum, itab-objnr, itab-bstnk, itab-qmart,

itab-kunum, itab-zztrjobnum, itab-qmtxt, itab-adrnr, itab-zzoutcm,

itab-zzaddlwork, itab-tplnr, itab-btpln, itab-erdat, itab-erdat1,

itab-erzeit1, itab-strmn, itab-equnr, itab-bequi, itab-dispdate,

itab-disptime, itab-zzdlvstat, itab-ernam, itab-pster, itab-pstur,

itab-mncod, itab-manum)

FROM VIQMEL as a inner join viqmma as b on a_{qmnum = b}qmnum

where b~mncod in ('9','9P','AD','BA','C001','C002','C003','C004',

'C005', 'CC', 'CM', 'CS', 'CT', 'FD', 'FM', 'FW',

'MR','N', 'NA','NR', 'SY', 'TI', 'TM', 'TP', 'WD',

'WS', 'FC', 'JC','PC','SC' )

and ( b_{mngrp eq 'STATUS' OR b}mngrp eq 'I&CCOMP' or b~mngrp eq

'I&CPPM' )

and b~kzloesch ne 'X'

• and b~pster IN qmdab

and b~pstur in petur

and a~zzoutcm in zzoutcm

and a~ZZADDLWORK in ZZADDLWO

AND a~qmart in qmart

and a~qmtxt in qmtxt

and a~qmnum in qmnum

and a~tplnr in S_MPRN " Change kk080

and a~btpln in s_Funloc " addition RC232 E502860820

and a~erdat in erdat

and b~erdat in QMAERDAT

and b~erzeit in QMAERZEI

and a~strmn in strmn

and a~equnr in equnr

and a~bequi in bequi

and a~zzdisp_date in dispdate

• and a~zzdisp_time in disptime

and a~zztrjobnum in twmsjbno.

append itab.

endselect.

I tried the following things but failed.

1. broke the join into two distinct select query like :

*select qmnum

• manum

• mncod

• erdat

• pster

• pstur

• erzeit

• into table itemp2

• from viqmma

• where qmnum in QMNUM and

• mngrp in r_mngrp and

• mncod in r_mncod and

• erdat in QMAERDAT and

• pstur in petur

• and pster eq qmdab

• and erzeit in QMAERZEI

• and kzloesch NE 'X'.

*if not itemp2[] is initial.

*SELECT qmnum

• objnr

• bstnk

• qmart

• kunum

• zztrjobnum

• qmtxt

• adrnr

• zzoutcm

• zzaddlwork

• tplnr

• btpln

• erdat

• strmn

• equnr

• bequi

• zzdisp_date

• zzdisp_time

• zzmedsub

• zzdlvstat

• ernam

• into table itemp1

• from viqmel

• for all entries in itemp2

• where qmnum EQ itemp2-qmnum

• where qmnum EQ qmnum

• and zzoutcm in zzoutcm

• and zzaddlwork in zzaddlwo

• and qmart in qmart

• and qmtxt in qmtxt

• and qmnum in qmnum

• and tplnr in s_mprn

• and btpln in s_funloc

• and erdat in erdat

• and strmn in strmn

• and equnr in equnr

• and bequi in bequi

• and zzdisp_date in dispdate

• and zzdisp_time in zzdisp_time

• and zztrjobnum in twmsjbno.

*

*endif.

clear itab.

Still it is taking a lot of time getting executed as i have not included the key fields in the where clause condition. The reason being in the selection screen only the date and notification type is entered. So it is taking a lot of time.

2. I tried putting package size for the select wid the join condition. But that is not allowed as the data picked is not pulled into all the fields of the internal table. Some fields of the int table are being populated by other stmnts.

So can anyone suggest a better way of running this query widout changing the input criteria given in it previuosly.

Regards,

Jayadeep.

[Former Member]

I am not looking into details.

But a FAE will not improve performance if you are actually missing indexes. If you have full table scans then you scan survive in test systems but not in productive systems. Without indexes nothing will work.

With proper indexes, a join is faster than the FAE (for all entries).

Siegfried

[Former Member]

What is the reason for the core dump? Check in ST22.

If it is time out change your code

SELECT ...
       FROM ... JOIN
       WHERE ...
       .
  APPEND ITAB.
ENDSELECT.

To:

SELECT ...
       INTO corresponding fields of TABLE ITAB
       FROM ... JOIN
       WHERE ....

MattG.

[Former Member]

Hi,

avoid NE in your select (b~kzloesch ne 'X' ). It tends to neglect the index during data fetch.

and avoid OR condition too. Instead use IN condition for the same.

( b~mngrp eq 'STATUS' OR b~mngrp eq 'I&CCOMP' or b~mngrp eq
'I&CPPM' )

regards,

madhu

[Former Member]

Hi,

I see this is very big an complex query and you can try following on it.

Remove corresponding fields and just define internal table in the same sequence of data selection fields in query

Check where clause field sequence if it is hitting proper index on table. (if there's is no index on table, evaluate the possibility of creating one for this query)

Dont use append ITAB, instead you can select all the entries at one go in internal table.

Your query is in 'select..endselect', check is you can remove that from select.. endselect and keep in loop of another internal table or use for all entries.. This way you are saving database hits.

Define range for the field in your where clause IN ('X' 'Y'......)

thnx, Award points if you find this useful..

Ags..

[Former Member]

Jayadeep - well, you're wrong about including key fields in the SELECT. I counted a number of them. But the SELECT is quite complex and the optimizer probably cannot determine the best index to use and just does a full table scan.

You can try a couple of things:

Remove all non-key (primary and secondary) fields from the SELECT and filter data from the internal table afterwards.

If that works, fine; if not, analyze your range tables to determine which (if any are empty) and construct a number of SELECTs which only use one or two non-empty range tables in the WHERE. Try to use the best SELECT. Again, you have to filter after5wards.

If that works, fine; if not, run it in the background.

Rob

[Former Member]

Hi,

Avoid the select..endselect statement since this will hit the database fetched.

Use Open cursor statements to package the fetches from DB.

Avoid For all entries since this will again increase the DB fetched

Shruthi