topic Re: help in performance in Application Development and Automation Discussions

help in performance

Former Member — Sun, 28 Jun 2009 09:34:39 GMT

Hi All,

i want to know if from performace side ,if it's good to select data and see if it's need to be update,or update any way.

e.g. i get data on user and my quesion is if to do select single and see if the user is exist and if the user exist see if i need to update his data on my DB tables

or create it anyway.

Best Regards

Michael

Re: help in performance

MarcinPciak — Sun, 28 Jun 2009 10:33:43 GMT

Hi Michael,

Generally every step that requires some communication with DB affects application performance. If you requirement involves couple (houndreds) users at most the performance quality shouldn't get worse too much. If on the other hand you are worinkg on huge data structures (where DB table has milions of records) this could drastically affect it.

If you use select single providing entire primaty key, the access to DB is really good as index is used (most probably). If, on the other hand, you ommit any field form primary key in query, it will work as usual SELECT ENDSELECT query.

As for the example you gave, you can use UPDATE+INSERT statements instead of combination of SELECT SINGLE + UPDATE. It could look like:


UPDATE dbtab SET field1 = ...
                                field2 = ...
                   WHERE field3 = ...  "this statment works like SELECT + UPDATE (so only those records which fullfills where condition are to be udpdated i.e. if user exists)
if sy-subrc = 0.
   "if no record updated, create it
  INSERT dbtab FROM wa.
endif.

Of course this is just example solution. It must always depend on certain conditons, like (as I said above) how many records are affected. If it is for single users, performance should not be the primary aspect to consider.

Regards

Marcin

Re: help in performance

Former Member — Sun, 28 Jun 2009 20:32:07 GMT

Hi Marcin,

the table is up to 1,000,000 records what do u think it's good to do for performance aspects ?

Michael

Re: help in performance

Former Member — Mon, 29 Jun 2009 03:30:04 GMT

Hi ,

I think in your case its better to use modify since that would take care of both insert and modify, because on other ways you have to select and check and its a over head.

Nafran

Re: help in performance

Former Member — Mon, 29 Jun 2009 03:46:40 GMT

Hi,

MODIFY dbtab statement will solve your problem.

See F1 help for Modify, it will update if record already exists otherwise insert a new record.

Usage;

MODIFY db_table FROM TABLE itab.

Regards

Karthik D

Re: help in performance

Former Member — Mon, 29 Jun 2009 05:25:45 GMT

HI Karthik,

There is a way to know if the modify statement do update or insert or noting ?

since this data is important in my case

Best Regards

Michael

Re: help in performance

Former Member — Mon, 29 Jun 2009 05:31:09 GMT


> There is a way to know if the modify statement do update or insert or noting ?
> since this data is important in my case

Hi,

you don't have to bother about that while using MODIFY. But there is no way to find how many lines are inserted and updated as MODIFY only return sy-subrc as 0(all internal table lines processed) or 4(Some lines are not processed) and sy-dbcnt will be having the number of lines successfully processed.

Regards

Karthik D

Re: help in performance

Former Member — Mon, 29 Jun 2009 05:45:14 GMT

The MODIFY statement sets sy-dbcnt to the number of processed lines.

then if you want you cant get the count of a primary key and number of entries and reduce it from the early one but i think your asking this to put a message if that's the case don't do it just convince the client that you will give the number of processed.

Nafran

Re: help in performance

Former Member — Mon, 29 Jun 2009 05:49:27 GMT

Hi,

The indirect way is to find the number of records of the dbtable before and after modify using SELECT COUNT(*) statement, so that you can calculate as;

Inserted rows  = Previous record count - current record count.
Updated Rows = internal table record count - Inserted Rows.

Hope this help you.

Regards

Karthik D

Re: help in performance

MarcinPciak — Mon, 29 Jun 2009 07:48:03 GMT

Hi Michael,

What Nafran and Karthik suggested is of course correct and maybe the best solution, But if you want to query a table with around 1milion records there should be some other performance aspects considered too.

MODIFY is ok (as it would handle INSERT + UPDATE at once - so access to DB is also perfomed once per each full table key). On the other hand you have to transport all the fields within work area which you are modifying DB from. So here performance might be "licking". While UPDATE allows you to transport only necessary fields (those selected and those queried), INSERT works similary to MODIFY in terms of transports (as data must come from work area too). So here, for testing performance, you could use trial and error approach, I mean first do your coding with MODIFY and check the results using ABAP runtime analysis ( SE30 ) then rebuilt it to work with UPDATE + INSERT and check again the results. In real time examples it still depends on different factors how DB Optimizer will access DB. Trully it is never known until runtime.

Regards

Marcin