topic Re: Select stmt issue, performance tuning. in Application Development and Automation Discussions

Select stmt issue, performance tuning.

Former Member — Wed, 28 Mar 2007 10:53:22 GMT

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.

Re: Select stmt issue, performance tuning.

Former Member — Wed, 28 Mar 2007 10:58:43 GMT

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

Re: Select stmt issue, performance tuning.

Former Member — Wed, 28 Mar 2007 11:00:35 GMT

hi,

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

that will help us understand better.

regards,

Aparna

Re: Select stmt issue, performance tuning.

Former Member — Wed, 28 Mar 2007 11:07:31 GMT

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.

Re: Select stmt issue, performance tuning.

Former Member — Wed, 28 Mar 2007 11:29:09 GMT

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

Re: Select stmt issue, performance tuning.

Former Member — Wed, 28 Mar 2007 11:33:16 GMT

hi Vaurn,

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

Regards,

Santosh

Re: Select stmt issue, performance tuning.

Former Member — Thu, 29 Mar 2007 11:49:58 GMT

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.

Re: Select stmt issue, performance tuning.

Former Member — Thu, 29 Mar 2007 11:58:39 GMT

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.

Re: Select stmt issue, performance tuning.

Former Member — Thu, 29 Mar 2007 12:22:16 GMT

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

Re: Select stmt issue, performance tuning.

Former Member — Mon, 02 Apr 2007 11:11:22 GMT

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

Re: Select stmt issue, performance tuning.

Former Member — Mon, 02 Apr 2007 12:44:15 GMT

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.

Re: Select stmt issue, performance tuning.

Former Member — Tue, 03 Apr 2007 16:32:49 GMT

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

Re: Select stmt issue, performance tuning.

Former Member — Thu, 05 Apr 2007 11:02:53 GMT

Thank you all people. Keep up the good work.

God Bless.