topic Re: SQL - Tuning in Application Development and Automation Discussions

SQL - Tuning

Former Member — Mon, 25 Jun 2007 15:01:32 GMT

Hi Experts,

I hv SQL code like,

1 - SELECT bname

INTO TABLE bname_itab

FROM vbak

WHERE ernam IN s_ernam

AND bname NE space

AND auart IN s_auart

AND vkorg IN s_org

and ktokd in s_c_ag

AND vtweg IN s_d_ch

AND spart IN s_div.

2 - SORT bname_itab.

3 - DELETE ADJACENT DUPLICATES FROM bname_itab.

So, the 1st statement is, consuming much time, as there 1000's BNAMEs in VBAK.

So,

How to avoide selecting duplicates in the 1st SELECT statement itself?

I mean, only the new/differed BNAMEs shuld b selected!

(so, I dont use 2ns and 3rd stetents any more)

thanq.

Re: SQL - Tuning

Former Member — Mon, 25 Jun 2007 15:10:25 GMT

SELECT bname

INTO TABLE bname_itab

FROM vbak

WHERE ernam IN s_ernam

AND bname NE space

AND auart IN s_auart

AND vkorg IN s_org

and ktokd in s_c_ag

AND vtweg IN s_d_ch

AND spart IN s_div

group by bname.

Regards,

Ernst

Re: SQL - Tuning

Former Member — Mon, 25 Jun 2007 17:56:33 GMT

You can use the distinct keyword to make all results unique, but in terms of performance it will likely be the same. The performance issue is that you are doing a scan on a field that is not indexed (bname). If you can somehow narrow the result set by using a date range, or something of that sort, it would help.

SELECT <b>DISTINCT</b> bname

INTO TABLE bname_itab

FROM vbak

WHERE ernam IN s_ernam

AND bname NE space

AND auart IN s_auart

AND vkorg IN s_org

and ktokd in s_c_ag

AND vtweg IN s_d_ch

AND spart IN s_div.

Re: SQL - Tuning

kesavadas_thekkillath — Fri, 06 Jul 2007 05:03:11 GMT

Try to reduce maximum IN statements by using EQ. because IN statement makes a complete search of that column......

Because there is nearly 1000 records its better to use group by clause than using distinct clause.