topic Re: How to replace SELECT DISTINCT; Performace problem. in Application Development and Automation Discussions

How to replace SELECT DISTINCT; Performace problem.

ullas_u2 — Mon, 15 Nov 2010 10:23:59 GMT

Dear All,

In my code; I am having a SELECT DISTINCT. Since it is found that, the query is tabking much more time to execute, can anybody suggest a way to replace the code.

The code looks like..

SELECT DISTINCT column1 column2

INTO itab

BYPASSING BUFFER....

Can I use a code like this..

SELECT column1 column2

INTO...

followed by SORT itab by column1 column2. and DELETE ADJACENT DUPLICATES COMPARING column1 column2.

I want to know whether both will provide same output.

Regards

Ullas

Re: How to replace SELECT DISTINCT; Performace problem.

Former Member — Mon, 15 Nov 2010 10:37:17 GMT

I think it would be good or easier to tell only if let us know what fields you are selecting, from which table your selcting and what is your where condition that you are passing to select them.

But still try using group by col1, col2 etc at the end of your SELECT statement to enhance the performance at best. Remember col1 etc specified with GROUP BY clause should be a part of the primary key or atleast a part of the Secondary key (INDEX's) of the table you are fetching from.

Bye.

Re: How to replace SELECT DISTINCT; Performace problem.

SuhaSaha — Mon, 15 Nov 2010 10:42:09 GMT

Hello,

Why have you used BYPASSING BUFFER? I can suggest compare the 2 approaches in ABAP Runtime Tool(SE30) "Tips & Tricks" section & check which one is faster.

BR,

SUhas

Re: How to replace SELECT DISTINCT; Performace problem.

ullas_u2 — Mon, 15 Nov 2010 11:01:59 GMT

BY PASSING BUFFER is must on DISTINCT.

Also rather than performance, I am afraid about the output. So 1st of all I need to know whether the mentioned logic provides same output or not.

-Ullas

Edited by: UllasU on Nov 15, 2010 12:10 PM

Re: How to replace SELECT DISTINCT; Performace problem.

ravi_lanjewar — Mon, 15 Nov 2010 12:28:32 GMT

Hi,

There is no need to write the by pass buffer, when you used the distinct it automatically bypass the buffer.

when you used the distinct in select sql then system sort table whole table, search and provide the result.

Put the Order by in SQL and try.

Re: How to replace SELECT DISTINCT; Performace problem.

SuhaSaha — Mon, 15 Nov 2010 12:29:24 GMT

BY PASSING BUFFER is must on DISTINCT

When using SELECT DISTINCT, SELECT overrides the buffer implicitly!

Re: How to replace SELECT DISTINCT; Performace problem.

Former Member — Tue, 16 Nov 2010 16:35:59 GMT

the SELECT DISTINCT can not use the table buffer ....

But, is your table in the table buffer?

If so, then you should use your second option, because this can use the table buffer and is at least 10 times faster.

... If the WHERE-clause also fits to the buffer setting.

If not, if the table in only in the database, then you should leave the DISTINCT where it is and chekc the WHERE-clause, whether it can use an index.

Siegfried

Re: How to replace SELECT DISTINCT; Performace problem.

Former Member — Fri, 19 Nov 2010 12:33:42 GMT

To answer one of the initial questions: The output should be the same with SELECT DISTINCT and SORT/DELETE. And I can confirm that on some systems the SORT/DELETE might be faster than SELECT DISTINCT. Maybe it's even faster if you select directly into a sorted table, like:

DATA: itab TYPE SORTED TABLE OF dtab WITH NON-UNIQUE KEY colum1 colum2.

SELECT column1 column2
FROM dtab
  INTO TABLE itab.

DELETE ADJACENT DUPLICATES FROM itab.