topic Re: Optimising SELECT from a database in Application Development and Automation Discussions

Optimising SELECT from a database

Former Member — Mon, 29 Jan 2007 14:38:09 GMT

I'm trying to retrieve records from a huge transaction table.

1. I want to retrieve records into an internal table

2. If there was a duplicate record that is about to move into the same internal table it will be moved into another internal table.

3. Someone told me that using a LOOP may not be that effective. Using internal table KEYS and BINARY SEARCH would be ideal.

I have come here to seek for urgent guidance and knowledge.

Hope you all can help.

Re: Optimising SELECT from a database

Former Member — Mon, 29 Jan 2007 14:41:07 GMT

u need to write a select statement with key fields and other fields....before that try to create an index for the table based on the fields used in where list....so database access will be fast....

now, looping the internal table check and move data to another internal table using binary search and with key fields....before this u need to do a sort on the table for binary search to be useful.

Message was edited by:

Ramesh Babu Chirumamilla

Re: Optimising SELECT from a database

Former Member — Mon, 29 Jan 2007 15:05:42 GMT

First of all, try to use an existing table index on the SELECT statement. I think you will have to use a loop to go through each row of the table to see if there are duplicates.

It would be best if you could post the code you have or at least more detailed requirements.

Rob

Re: Optimising SELECT from a database

Former Member — Tue, 30 Jan 2007 03:53:09 GMT

Hi,

Suppose you are trying to get records from a table TAB, and A, B and C are the records which need to be fetched.

Select A B C

from TAB

into wa

where <cond>

order by A B C.

if sy-subrc eq 0.

if sy-index eq 1.

append wa to itab1.

endif.

if wa-A eq wa1-A and wa-B eq wa1-B and wa-C eq wa1-C.

append wa to itab2.

else.

append wa to itab1.

endif.

move wa to wa1.

endif.

endselect.

At the end of this select, all the unique records will be in itab1 and all the duplicate records will be in itab2.

Reward ponits if found useful..!

Cheers

Abhishek

Re: Optimising SELECT from a database

Former Member — Tue, 30 Jan 2007 05:23:19 GMT

Hi,

Get only required fields from the table based on thier position in table

put maximum comparision fields in where conditions.

sort the table and move the duplicate entry into another table.

Thanks

Shiva

Re: Optimising SELECT from a database

Former Member — Tue, 30 Jan 2007 12:29:50 GMT

Hi Andrew,

do u want to select the records from the databese... then move the duplicate record in to another internal table.. is this u requirement!!

first select the data into let us say Ex IT_TAB1.

suppose F1 and F2 Are key fields in u r table.

then

SORT IT_TAB1 BY F1 F2.

THEN

IT_TAB2[] = IT_TAB1[].
SORT IT_TAB2 BY F1 F2.
DELETE ADJACENT DUPLICATE FROM IT_TAB2 COMPLARING F1 F2..

now u have two tables IT_TAB1 contains all the Records

IT_TAB2 contains only the single records..

Re: Optimising SELECT from a database

Former Member — Tue, 30 Jan 2007 14:33:01 GMT

Hi,

ur 2nd requirement is not very clear.

u want to have unique entries in one table and the duplicate entries in another table ?

if so...u can try the following logic:

for unique entries:

itab1[] = itab[].

sort itab1 by f1.

delete adjacent duplicates from itab1 comparing f1.

for duplicate entries:

sort itab by f1.

loop at itab.

if itab-f1 = itab2-f1.

move itab-f1 to itab3-f1.

append itab3.

endif.

itab2 = itab.

endloop.

this itab3 will have all duplicate entries.

regards,

madhu