topic Re: performance by select / update db in Application Development and Automation Discussions

performance by select / update db

Former Member — Tue, 15 Apr 2008 10:32:54 GMT

Hello,

i must be update a customer table with 3million entry.

how can i get performant the entries and update one field?

I thought i create a select and use the "open cursor" command.

and i update the selected entries and then i get the next entries.

Can anyone give me some tips...thanks

Re: performance by select / update db

Former Member — Tue, 15 Apr 2008 10:43:13 GMT

get all the data from the table and move it a ITAB

then use modify to change the field

If the field is not primary key then use MODIFY TABLE or UPDATE TABLE to update table otherwise delete the table entries and use INSERT TABLE

Regards

Shiva

Re: performance by select / update db

Former Member — Tue, 15 Apr 2008 10:55:29 GMT

Hi Shiva,

can i get all 3.000.000 Entries from DB?!? and is it then performant?

Re: performance by select / update db

Former Member — Tue, 15 Apr 2008 11:00:32 GMT

Schedlue it in backgroound

first do it for some 10 records by using up to 20 rows

if it works then remove it and schedlue a background job

Try like this

Regards

Shiva

Re: performance by select / update db

Former Member — Tue, 15 Apr 2008 11:03:52 GMT

ok.

what do you think about this...

SELECT xxxx xxxx

FROM dbtab

APPENDING TABLE gt_itab

PACKAGE SIZE 20000

WHERE lfdnr IN so_lfdnr.

modify ......

update db....

refresh gt_itab.

ENDSELECT.

Re: performance by select / update db

Former Member — Tue, 15 Apr 2008 11:06:14 GMT

SELECT xxxx xxxx

into TABLE gt_itab

FROM dbtab.

loop at gt_itab.

modify.

endloop.

Re: performance by select / update db

Former Member — Tue, 15 Apr 2008 23:27:59 GMT

Hi DDC,

You can improve your LOOP run choosing an FIELD-SYMBOL:

FIELD-SYMBOLS: <fs_itab> LIKE "structure of gt_itab".

SELECT xxxx xxxx INTO TABLE gt_itab FROM dbtab.

LOOP AT gt_itab assigning <fs_itab>.
....
<fs_itab>-field = 'abcd'.
.... 
ENDLOOP.

In this case there is no MODIFY needed at all. This is a proven method for saving system time. Hint: The internal table should be declared WITHOUT headerline; i.e. data: gt_itab like table of "structure".!

Try it,

Heinz

Re: performance by select / update db

Former Member — Wed, 16 Apr 2008 06:56:52 GMT

You can't download 3.000.000 entries! These recommendations are ....

You should not use the modify db-table, that the is the only command which works not with arrays.

What do you want to do?

The fastest command is


Updata dbtab set field1 = a
           where ....

you can also write field1 = field1 + a or + field10

If this enough then use it.

Otherwise you must download in package of about 10.000 and update.

Siegfried

Re: performance by select / update db

Former Member — Wed, 16 Apr 2008 08:18:45 GMT

now i select all entries and use this command:

Updata dbtab set field1 = a

where ....

it doesn´t takes a long time. It was very fast....

thanks a lot