10-22-2007 6:46 AM
Dear Experts
I have used the following query, but it takes long time to execute and affects the performance too.
How can i change this query, i tried the BSAK and BSIK tables also, but
KOART and NEBTR fields not available in those tables, so am supposed to use the table BSEG.
Please advise me to improve the performance of the coding.
Thanks in advance.
Regards
Rajaram
SELECT shkzg
skfbt
dmbtr
wskto
belnr
gjahr
bukrs
qbshb
lifnr
bschl
nebtr
FROM bseg
INTO TABLE ibseg
WHERE augbl = p_vblnr
AND bukrs = p_bukrs
AND koart = 'K'
AND auggj = p_gjahr
AND belnr NE p_vblnr.
10-22-2007 6:49 AM
Change your WHERE clause and it will help for runtime.
Firs get data for bukrs = p_bukrs and belnr NE p_vblnr and auggj = p_gjahr. I nthis way you are passing all primary keys.
Once data is in the internal table then filter the same with conditions augbl = p_vblnr and koart = 'K'
Hope this helps.
ashish
10-22-2007 6:49 AM
Change your WHERE clause and it will help for runtime.
Firs get data for bukrs = p_bukrs and belnr NE p_vblnr and auggj = p_gjahr. I nthis way you are passing all primary keys.
Once data is in the internal table then filter the same with conditions augbl = p_vblnr and koart = 'K'
Hope this helps.
ashish
10-22-2007 7:03 AM
I have changed my query as per your advise , but still there is no change in performance.
see the query
SELECT shkzg
skfbt
dmbtr
wskto
belnr
gjahr
bukrs
qbshb
lifnr
bschl
nebtr
FROM bseg
INTO TABLE ibseg
WHERE bukrs = p_bukrs
AND belnr NE p_vblnr
AND auggj = p_gjahr
AND augbl = p_vblnr
AND koart = 'K'.
Regards
Rajaram
10-22-2007 7:06 AM
Can you change AUGGJ to GJAHR?? Check once if Fiscal year and fiscal year of posting document are same. this will save a lot of execution time.
ashish
10-22-2007 7:09 AM
Hi Ram ,
What i would suggest is since you are selecting the data from one table so ,use a subset of the data used in you select statement and use if to select data from table bseg using se11 transaction and see if the perofrmance i better or not , if it is better than you program then check the SQL trace of SE11 execution and see how the query is passed to the database by the system and try to replicate it in your program also.
Regards
Arun
10-22-2007 6:54 AM
use this
WHERE
bukrs = p_bukrs
AND belnr NE p_vblnr
AND auggj = p_gjahr and
augbl = p_vblnr and
AND koart = 'K'.
10-22-2007 7:01 AM
Hi,
try to use only the primary key fields in where clause .
It will drastically improve thr performance.
so try to avoid comparing non primary key fields in where statement.
Reward if usefull......
10-22-2007 7:13 AM
Hi
I am supposed to check the non-primary keys in the where condition lik
auggj , augbl , koart.
Please advise me how to solve this probelem.
Regards
Rajaram
10-22-2007 7:24 AM
Here is a suggestion - as you have clearing document information, get first data from BSAK. also populate BUKRS BELNR and GJAHR, BUZEI, use these values to get data from BSEG. It will be fast.
If possible in BSAK, you can also use index or if required create custom index.
Hope this helps.
ashish
10-22-2007 7:09 AM
hi
good
1-try to use move corresponding fields of statement with your select query and check the difference.
2-check wheather you can avoid the use of bseg table.
reward point if helpful.
thanks
mrutyun^
10-22-2007 7:28 AM
shkzg
skfbt
dmbtr
wskto
belnr
gjahr
bukrs
qbshb
lifnr
bschl
nebtr
change these fields and your internal table in the right sequence.
my mean the order in which they appear in bseg table.