topic Re: sorted table... in Application Development and Automation Discussions

sorted table...

aris_hidalgo — Mon, 13 Mar 2006 06:17:01 GMT

Hello experts,

I am recently having a problem in sorting an itab because it contains almost 3 million records and I'm thinking if I just make it a sorted table. Now, how can I make a table based on this sort:

SORT t_ztm0019_tmp BY sernr datum DESCENDING uzeit DESCENDING

here is the structure of my itab:

DATA: BEGIN OF t_ztm0019 OCCURS 0.

INCLUDE STRUCTURE ztm0019.

DATA: END OF t_ztm0019.

And here is the complete code below. When I try to run it in prod server it produces a run time error. Any help would really be appreciated. Thanks a lot guys!

*get all records from table ztm0019 and append to itab.

SELECT * FROM ztm0019

APPENDING TABLE t_ztm0019.

*populate itab t_ztm0019_tmp.

t_ztm0019_tmp[] = t_ztm0019[].

CLEAR t_ztm0019[].

*sort itab according to latest date and time grouped by serial no.

SORT t_ztm0019_tmp BY sernr datum DESCENDING uzeit DESCENDING.

DELETE ADJACENT DUPLICATES FROM t_ztm0019_tmp COMPARING

sernr

bwart

mblnr.

LOOP AT t_ztm0019_tmp.

*read first record from itab.

READ TABLE t_ztm0019_tmp INDEX sy-index.

IF t_ztm0019_tmp-bwart EQ '702'

OR t_ztm0019_tmp-bwart EQ '708'

OR t_ztm0019_tmp-bwart EQ '712'

OR t_ztm0019_tmp-bwart EQ '718'.

LOOP AT t_ztm0019_tmp WHERE sernr = t_ztm0019_tmp-sernr.

APPEND t_ztm0019_tmp TO t_ztm_acc_variance.

CLEAR t_ztm0019_tmp.

DELETE t_ztm0019_tmp.

ENDLOOP.

ELSE.

APPEND t_ztm0019_tmp TO t_ztm0019.

CLEAR t_ztm0019_tmp.

DELETE t_ztm0019_tmp.

ENDIF.

CONTINUE. " Go for next SERNR

ENDLOOP.

*inserts or updates records in db table ztm_acc_variance based on itab

MODIFY ztm_acc_variance FROM TABLE t_ztm_acc_variance.

*inserts or update unaccounted variances from itab

MODIFY ztm0019 FROM TABLE t_ztm0019.

*deletes records from db table ztm0019 based on matching entries in itab

DELETE ztm0019 FROM TABLE t_ztm_acc_variance.

Re: sorted table...

Former Member — Mon, 13 Mar 2006 06:38:58 GMT

Dear Viraylab,

You can sort the table while fetching data from database.

Just try this statement and remove sort statement.

SELECT * FROM ztm0019

APPENDING TABLE t_ztm0019

order by sernr ASCENDING datum DESCENDING uzeit DESCENDING

Regards.

Abhijit Dey.

Re: sorted table...

aris_hidalgo — Mon, 13 Mar 2006 06:44:02 GMT

Dear Abhiijit,

wouldnt that be much slower?

Re: sorted table...

Former Member — Mon, 13 Mar 2006 06:44:40 GMT

Hi,

If your internal table is going to contain huge data,

you can go for Hashed tables instead of standard or dorted table.

Hashed tables have theier own Hashed algorithm, which makes it possible to process the internal table irrespective of number of records.

Hope this helps..

Regards,

Shashank

Re: sorted table...

vinod_gunaware2 — Mon, 13 Mar 2006 06:49:45 GMT

Use proper indexing and maitain same sequence(like DATABASE). Use only field which are required instead of *.

Then use sort on only those field which required coz it will degrade the perfomance.

Remove Nested loop instead of it use Read statement becuae u r using same interna table.

Use binary seach in Read statament.

Use modify with transporting.

Use delete statement before other and write continue after delete.

Hope this will be useful.

Regards

vinod

Re: sorted table...

aris_hidalgo — Mon, 13 Mar 2006 06:50:05 GMT

Dear Abhiijit,

wouldnt that be much slower?

Re: sorted table...

athavanraja — Mon, 13 Mar 2006 06:51:12 GMT

if the no. of records to process are too huge its better to go for FIELD-GROUPS

Regards

Raja

Re: sorted table...

Former Member — Mon, 13 Mar 2006 06:58:27 GMT

Dear Viraylab,

You have to test it this option. It completely depends upon the Memory of the Database Server.

Otherwise you have to split the query based on SERNR and sort the table and at last append all splitted table in a single one.

Regards.

Abhijit Dey

Re: sorted table...

Former Member — Mon, 13 Mar 2006 07:25:38 GMT

hi ,

In ur select query

Try to differentiate the select process by

Select f1 f2 f3 f4 and so on

Where clause

Order by sernr bwart mblnr .

2.this reduces the sort criteria means to say u need not again sort explicitly on some fields .

Instead of READ TABLE t_ztm0019_tmp INDEX sy-index

Use READ TABLE t_ztm0019_tmp with key f1 = t_ztm0019_tmp-f1

F2 = t_ztm0019_tmp-f2

F3 = t_ztm0019_tmp-f3.

IF t_ztm0019_tmp-bwart EQ '702'

OR t_ztm0019_tmp-bwart EQ '708'

OR t_ztm0019_tmp-bwart EQ '712'

OR t_ztm0019_tmp-bwart EQ '718'.

I think this can be eliminated if u can make proper use of read statement as the comparisions can be brought in itself here .

4 LOOP AT t_ztm0019_tmp WHERE sernr = t_ztm0019_tmp-sernr.

Loop in loop is not advisable when u r dealing with 3 million or more records as this will degrade the performance .

Instead of this use the condition in read itself .

READ TABLE t_ztm0019_tmp with key sernr = t_ztm0019_tmp-sernr .

If sy-subrc – 0.

APPEND t_ztm0019_tmp TO t_ztm_acc_variance.

CLEAR t_ztm0019_tmp.

Else .

APPEND t_ztm0019_tmp TO t_ztm0019_tmp.

Clear t_ztm0019_tmp.

Endif.

5. modify the changes and run the performance repeated ly and check the same .

6. use code inspector to get the appropriate sequence .

Hope this helps .

Vijay.

Re: sorted table...

Former Member — Mon, 13 Mar 2006 08:02:27 GMT

HI Viray,

You cannot sort a sorted table using the SORT statement. The system always maintains these

tables automatically by their sort order..

If an internal table is statically recognizable as a sorted table, the SORT statement causes a syntax error...

If the table is a generic sorted table, the

SORT statement causes a runtime error if the sort key is not the same as an extract of the

beginning of the table key, you sort in descending order, or use the AS TEXT addition.. with SORT

In other words, the SORT statement is only allowed for generic internal tables, if it does not violate the

internal sort order..

regards

satesh

Re: sorted table...

aris_hidalgo — Mon, 13 Mar 2006 08:19:04 GMT

Hello satesh,

The table is not sorted as you can see from my code.It is a standard table.I just modified my program that instead of doing:

sort t_ztm0019_tmp by sernr datum descending uzeit descending

I used:

SELECT * FROM ztm0019

APPENDING TABLE t_ztm0019

ORDER BY sernr

datum DESCENDING

uzeit DESCENDING.

I hope this would work. Also, the structure of the internal table is like this:

DATA: BEGIN OF t_ztm0019_tmp OCCURS 0.

INCLUDE STRUCTURE ztm0019.

DATA: END OF t_ztm0019_tmp.

Now, how can I decalre this as an sorted table so I can remove my sort statement?Thanks a lot guys and take care!

Re: sorted table...

Former Member — Mon, 13 Mar 2006 08:22:51 GMT

DATA gt_list_2 LIKE SORTED TABLE OF list_2 WITH UNIQUE KEY <field1>.