[SOLVED] Embedded SQL in RPGLE performance in Update

Embedded SQL in RPGLE performance in Update

When updating date/time fields in a file using embedded SQL in an RPGLE program I can use either CURRENT_DATE/CURRENT_TIME or store the current date/time value into a host variable. And use this host for assignign.

Now I wonder which way is the faster one? Or is this irrelevant?

exec sql
  update testpf
  set t1date = CURRENT_DATE, t1time = CURRENT_TIME
  where t1key = someValue;

dcl-s date date;
dcl-s time time;
exec sql
  set :date = CURRENT_DATE;
exec sql
  set :time = CURRENT_TIME;
exec sql
  update testpf
  set t1date = :date, t1time = :time
  where t1key = someValue;

Note: This is all written "on the fly"! But I hope you get what I mean

Edit: To clarify, the goal is not to update just one row, its ment for several updates. Like having a database with invoice positions and a state field. This state field has 3 neigbour fields which track the user changing it on which day at which time. And in my case there can be several hundrets of positions where I need to update the time and date.

Solution

I think your question have no a simple answer, infact if you search info about host variable, also IBM doesn't give you a quick answer, look here:

Host variables require default filter factors. When you bind a static SQL statement that contains host variables, Db2 uses a default filter factor to determine the best access path for the SQL statement. Db2 often chooses an access path that performs well for a query with several host variables. However, in a new release or after maintenance has been applied, Db2 might choose a new access path that does not perform as well as the old access path. In many cases, the change in access paths is due to the default filter factors, which might lead Db2 to optimize the query in a different way.