Select stmt issue, performance tuning.

[Former Member]

Hi Group,

I have a question.

There is a select stmt which is using inner join, and its taking good amoutn of time to get executed. I was told to tune the performance of that query. And my TL suggested me to use ranges in that select stmt, like the sign, option, low, high.

Could you please help me out on this issue? Urgent.

Thank you in advance.

Regards,

P. Varun Kumar.

[Former Member]

If possible try replace the inner join with For all entries.

[Former Member]

hi,

paste the part of the code that you want to tune for performance..

that will help us understand better.

regards,

Aparna

[Former Member]

They are suggesting to use ranges rather than For all entries.

Here is the query:

SELECT b~kunnr

b~zz_echan_reg

a~title

a~first_name

a~last_name

a~email_addr INTO TABLE gt_ecustomers

FROM zyd_contacts AS a

INNER JOIN kna1 AS b

ON a_{yd_kunnr = b}kunnr

WHERE zz_echan_reg EQ 'X' OR zz_echan_reg EQ 'A'

AND zzcap_divert EQ space

AND email_addr NE space.

Please check this out.

Thanks again for any help in advance.

[Former Member]

hi,

write 2 seperate selects. use FOR ALL ENTRIES.before using for all entries delete the duplicate entries.

then remove NE in Where clause of the select statement. instead delete the unwanted entries after your select.

**reward if helpful

regards,

madhu

[Former Member]

hi Vaurn,

Avoid using joins and use FOR ALL ENTRIES statement ...

Regards,

Santosh

[Former Member]

1. If your TL talked about ranges, this how you have to use

Declare ranges:

ranges: r1 for kna1-zz_echan_reg

Fill the ranges:

r1-sign = 'I'.

r1-option = 'EQ'.

r1-low = 'X'

append r1.

r1-sign = 'I'.

r1-option = 'EQ'.

r1-low = 'A'

append r1.

this ranges you use in where condition of the select statement

as WHERE zz_echan_reg in r1.

2. Avoid using joins and use for all entries.

3. in the where condition remove email_addr NE space.

after you get the data into internal table you delete the unwanted entries.

Hope this helps.

[Former Member]

Hi Group,

Now I have to only tune this select query.

SELECT vbeln

posnr

matnr

matkl

zzact_start_date

zzad_line_status

zznm_pre_date

INTO TABLE gt_vbap

FROM vbap

WHERE ( ( zzact_start_date = p_datum

AND zzad_line_status = c_processed )

OR zzact_start_date = p_datum ).

Please suggest thank you in advance.

[Former Member]

hi

this select is not using the primary keys.try to add primary keys in your WHERE clauseor try creating an index so that the select will be faster.

regards,

madhu

[Former Member]

In your first query, you should change the conditions on ZZ_ECHAN_REG to:

 WHERE ZZ_ECHAN_REG  IN  ('A', 'X')

This will remove the OR and the time to build the range.

For your second Select look carefully at your conditions. As "zzact_start_date = p_datum" will get all the records in "zzact_start_date = p_datum

AND zzad_line_status = c_processed" and more.

MattG.

[Former Member]

Hi Varun,

Consider creating a secondary index on field zzact_start_date and break your sql statement into two statements:

SELECT vbeln

posnr

matnr

matkl

zzact_start_date

zzad_line_status

zznm_pre_date

INTO TABLE gt_vbap

FROM vbap

WHERE zzact_start_date = p_datum

AND zzad_line_status = c_processed.

• Select using 'Appending table' statement

SELECT vbeln

posnr

matnr

matkl

zzact_start_date

zzad_line_status

zznm_pre_date

APPENDING TABLE gt_vbap

FROM vbap

WHERE zzact_start_date = p_datum.

delete adjacent duplicates from gt_vbap.

Regards,

Shyam Khemani

[Former Member]

hi

check for any existing view on the tables. If exists, write select stm on that view. If not exists use for all entries along with the ranges

[Former Member]

Thank you all people. Keep up the good work.

God Bless.