Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

SELECT query - performance improvement needed

giri_raaj
Explorer
0 Kudos

HI guys.

can u help me to improve the performance of the following query.

Observations :

1. the index hit for the query correct(primary key).

2. removed duplicates from the internal table before calling

*Please let me know any other way we can improve this query.

SORT t_delno1 BY vbelv.

DELETE ADJACENT DUPLICATES FROM t_delno1 COMPARING vbelv.

SELECT vbeln

vgbel

ladgr

FROM lips

INTO TABLE t_lips2

FOR ALL ENTRIES IN t_delno1

WHERE vbeln = t_delno1-vbelv.

regards

girish

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Giri,

Your query is using main primary key field VBELN and seems to be perfect. Just note the below points which may further help.

>> If you have ITEM field in t_delno1 table, then also include POSNR = t_delno1-POSNR in where condition.

>> Before using SELECT, check that t_delno1 table should not be blank, otherwise FOR ALL ENTRIES will fetch all entries from LIPS table.

>> Also, better to use before select "delete t_delno1 where vbelv = space'.

BR

Diwakar

6 REPLIES 6

Former Member
0 Kudos

Giri,

Your query is using main primary key field VBELN and seems to be perfect. Just note the below points which may further help.

>> If you have ITEM field in t_delno1 table, then also include POSNR = t_delno1-POSNR in where condition.

>> Before using SELECT, check that t_delno1 table should not be blank, otherwise FOR ALL ENTRIES will fetch all entries from LIPS table.

>> Also, better to use before select "delete t_delno1 where vbelv = space'.

BR

Diwakar

0 Kudos

hi diwakar.

Thx for your input.

1. there is no posnr field in the internal table.

2. there is no blank field in the internal table.

can u come up with any other suggestion to improve the same.

former_member192616
Active Contributor
0 Kudos

Hi Giri,

what are your execution times (time per record) and kpis (buffer gets per record)?

I assume you have an index range scan on the primary key, right?

Depending on the db plattform, increasing the blocking factors could squeeze out

a little bit more. For ORACLE you can e.g. try to add

%_HINTS ORACLE '&max_in_blocking_factor 100&u2019.

Kind regards,

Hermann

p.s.: for this query it should be max_in_blocking_factor of course.

Edited by: Hermann Gahm on Aug 8, 2011 1:49 PM

Former Member
0 Kudos

Hi Giri,

For your points:

1. there is no posnr field in the internal table.
2. there is no blank field in the internal table.

Point 1.

There is POSNR field in t_delno1 fine. So you need to add POSNR field in strcuture of t_delno1 and also select the field posnr while filling t_delno1.

This is important because when you are selected values from LIPS, fields vbeln and posnr are part of primary key. If you use all th fields of a primary key the execution time will be fast.

Point 2.

When you do a select query to fill t_delno1 there are chances that few records might get filled into t_delno1 where vbeln value is space i.e empty.

Hope you got it.

BR

Dep

former_member194613
Active Contributor
0 Kudos

How much do you want to improve? You can't win a formula-1 race with a normal street car.

So your SELECT will depend on the number of records in LILs on the database and on the number of entries in the FOR ALL ENTRIES. What are these numbers, use SQl trace.

The posnr will actually not improve your performance if you need records belonging to the different values of posnr!

So specify everything on the result set what you know.

The blocksize of the FOR ALL ENTRIES will have an influence, here the rsdb/prefer_in_itab_opt and a larger value for rsdb/max_in_blocking_factor could be an option. Ask your system admin, or use a hint to test.

Siegfried

0 Kudos

Giri,

As you said that


1. there is no posnr field in the internal table.
2. there is no blank field in the internal table.

I believe there is no further improvement possible without changing basis parameters or so.. and you should be having satisfactory results with this query.

BR,

Diwakar