topic Re: Update Command Issue in Application Development and Automation Discussions

Update Command Issue

Former Member — Tue, 30 Oct 2007 14:46:04 GMT

Dear All,

I have a scenarion to update only few fields to custom tables .

Here i should not use Modify command since other program runs every five minutes to update other fields in same table.

Some how the below program is not updating the data .

See the program and can you please tell me about performance if i use update within loop.

tables : zwvbak.

data : int_zwvbak LIKE zwvbak OCCURS 0 WITH HEADER LINE.

data : begin of wa_zwvbak ,

vbeln like zwvbak-vbeln,

BELNR like zwvbak-belnr,

ZZINVDATE like zwvbak-ZZINVDATE,

ZZINVTIME like zwvbak-ZZINVTIME,

end of wa_zwvbak.

data: f1 type i,

f2 type i,

f3 type i.

parameters p_vdatu like zwvbak-vdatu obligatory.

start-of-selection.

select * from zwvbak into corresponding fields of table int_zwvbak

where vdatu = p_vdatu.

get run time field f1.

loop at int_zwvbak.

clear : wa_zwvbak.

Accounting document number

MOVE '87999' TO wa_zwvbak-belnr.

Invoice date

move sy-datum to wa_zwvbak-zzinvdate.

Invoice Time

move sy-uzeit to wa_zwvbak-zzinvtime.

Sales order number

move int_zwvbak-vbeln to wa_zwvbak-vbeln.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = wa_zwvbak-vbeln

IMPORTING

OUTPUT = wa_zwvbak-vbeln

UPDATE zwvbak SET: zzinvdate = wa_zwvbak-zzinvdate

zzinvtime = wa_zwvbak-zzinvtime

belnr = wa_zwvbak-belnr

WHERE vbeln = wa_zwvbak-vbeln.

commit work.

endloop.

update zwvbak from table int_zwvbak.

commit work.

get run time field f2.

f3 = f2 - f1.

all inputs are welcome.

Thanks

Seshu

Re: Update Command Issue

former_member195698 — Tue, 30 Oct 2007 14:50:57 GMT

Using UPDATE from Table will be a better option as it will hit the database only once.

You can schedule this program in such a way that it doesn't collide with the Other program schedule.

Regards,

Abhishek

Re: Update Command Issue

Former Member — Tue, 30 Oct 2007 15:07:54 GMT

It looks to me like you are updating individual lines within the table in the loop, but then updating it again, setting it back the way it was in the final UPDATE statement.

Rob

Re: Update Command Issue

Former Member — Tue, 30 Oct 2007 15:12:57 GMT

Thanks Rob, I just kept some dummy code here.. anyhow when i use client specified it works fine.

Can you please explain about update within loop,is it performance issue since i should not use update or modify outside of the loop .

I have many programs to update same table based on some conditions,if all programs are on same time it would be issue if i use modify statement.

I hope you got my point.

Thanks

Seshu

Re: Update Command Issue

Former Member — Tue, 30 Oct 2007 15:20:35 GMT

I would use the table update so long as the amount of data is reasonably small. If it is large, you have to split it into a number of updates with a COMMIT in between. This is done in order to avoid dumping when the rollback area is filled.

Rob

Re: Update Command Issue

Former Member — Tue, 30 Oct 2007 15:23:46 GMT

Rob,

May be i may have maximum 300 to 400 records each time since this job will run every 30 minutes.

so is it takes long time ?

Right now it is taking 180 seconds .

Thanks

Seshu

Re: Update Command Issue

Former Member — Tue, 30 Oct 2007 15:26:30 GMT

Is VBELN the first field in the primary key of your table?

Rob

Re: Update Command Issue

Former Member — Tue, 30 Oct 2007 15:29:43 GMT

Yes VBELN is the Key field .

custom tables contains only one key field called VBELN.

Thanks

Seshu

Re: Update Command Issue

Former Member — Tue, 30 Oct 2007 17:58:59 GMT

The time does seem excessive, but I don't really know why.

One thing I would defineitely do would be to get rod of the colon ( in the UPDATE statement. If you mis-place a comma in it as well, you can end up updating the entire database incorrectly.

Rob

Re: Update Command Issue

Former Member — Thu, 01 Nov 2007 18:35:23 GMT

Thanks Abhishek and Rob.

It is solved using update command within loop ,but little bit performance issue when you compare to modify command in out of loop.( 30 seconds extra).

Thank you for your support.

Thanks

Seshu