SQL - Tuning

[Former Member]

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.

[Former Member]

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

[Former Member]

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.

[kesavadas_thekkillath]

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.