topic Re: Select Query not workin as expected in Application Development and Automation Discussions

Select Query not workin as expected

Former Member — Wed, 29 Aug 2007 07:21:00 GMT

HI Guru's,

Here is a select query which takes more time when I incerase the search criteria . The Table I_MKPF is not initial.

select mblnr mjahr zeile bwart matnr werks charg lifnr

shkzg bwtar menge meins ebeln aufnr bukrs prctr

from mseg into corresponding fields of table z_rec

for all entries in i_mkpf

where

mblnr eq i_mkpf-mblnr and

mjahr eq i_mkpf-mjahr and

bwart in s_bwart and

werks in s_werks and

bukrs in s_bukrs.

In the above query MBLNR and MJAHR are primary key fields in the same order as in the database.

The query fetches 2 records and takes around 20 minutes.

I did some more testing and found one peculiar issue.

The query takes long time only when S_WERKS and/or S_BUKRS is entered on the selection screen.

If these two fields are left blank it works super quick.

I am baffled as to why this is happening. Should it not have been the other way around..?

Could please take a look and tell me what the issue might be??

Thanks in Advance,

Imran

Re: Select Query not workin as expected

former_member404244 — Wed, 29 Aug 2007 07:26:07 GMT

hI,

Instead of going for into corresponding fields of table z_rec ,go for into table z_rec by declarng all the fields in the sequence which u selct from database.

2>in the where condition specify the fields inorder how they appaers in SE11 fro table MSEG

3>check for the initiality of the internal table I_MKPF before the select query.

Regards,

Nagaraj

Re: Select Query not workin as expected

Former Member — Wed, 29 Aug 2007 07:27:02 GMT

Hi Imran,

please check the SQL performance in transaction ST05 > "Explaine one SQL request" for this slightly modified statement:


select mblnr, mjahr, zeile, bwart, matnr, werks, charg, lifnr, 
shkzg, bwtar, menge, meins, ebeln, aufnr bukrs, prctr 
from mseg
where 
(
mblnr = '1' and 
mjahr = '2' and 
bwart in ('1', '2') and 
werks in ('1', '2') and 
bukrs in ('1', '2')
)
or
(
mblnr = '1' and 
mjahr = '2' and 
bwart in ('1', '2') and 
werks in ('1', '2') and 
bukrs in ('1', '2')
)
or
(
mblnr = '1' and 
mjahr = '2' and 
bwart in ('1', '2') and 
werks in ('1', '2') and 
bukrs in ('1', '2')
)
or
(
mblnr = '1' and 
mjahr = '2' and 
bwart in ('1', '2') and 
werks in ('1', '2') and 
bukrs in ('1', '2')
)

Please answer back with the results. Best regards,

Alvaro

Re: Select Query not workin as expected

Former Member — Wed, 29 Aug 2007 07:31:20 GMT

The table I_MKPF is not intial.

The where condition is using the fields in the order used in SE11 in MSEG.

Thanks for your help , but the problem seems to be something different.

Re: Select Query not workin as expected

Former Member — Wed, 29 Aug 2007 07:43:53 GMT

HI,

First select the data based on the fields MBLNR and MJAHR.After fethching the data into the internal table then delete the entries ,which will not match the BUKRS and WERKS.

DO this deletion only select option have the data.

I think this will solves the problem.

Thanks,

CSR.

****Please Reward if helpful.