topic Re: Select Query in Application Development and Automation Discussions

Select Query

Former Member — Fri, 16 May 2008 10:36:46 GMT

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.

Re: Select Query

Former Member — Fri, 16 May 2008 11:03:43 GMT

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

Re: Select Query

Former Member — Fri, 16 May 2008 11:20:57 GMT

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.

Re: Select Query

Former Member — Fri, 16 May 2008 11:36:01 GMT

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

Re: Select Query

Former Member — Sat, 17 May 2008 15:10:26 GMT

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..

Re: Select Query

Former Member — Sat, 17 May 2008 19:49:59 GMT

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

Re: Select Query

Former Member — Mon, 19 May 2008 04:11:24 GMT

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