Frontbase optimization

Joe Maus jmaus at theccgllc.com
Wed Apr 15 09:57:11 EDT 2009


> The situation is a little more complicated then I made out in my  
> first email,

Isn't always : )

> 
> The syncing is a little more complicated as well: some records are  
> already
> in Frontbase so instead of an insert, we need to update the record. I  
> thought
> it would be faster (I could be way off here) to first try to insert  
> the record, if
> that fails, see if the record exists and update it. Since this means  
> using
> insert, select and update statements in no particular order,  
> commiting the
> transactions every n records isn't an option.

Actually you should be able to do an update first and test to see if any rows were affected.  I don't have my FrontBase manual handy but I'll bet there is a variable you can query (or use the dam's version of this - I think it's $rowaffected) to see if the update actually updated any rows.  If none were updated that means the row does not exist and you can do the insert.  If it does you do the update.  Just a simple if then else really.  Use it all the time in other backends.  (Sorry Geert, still looking for that first FrontBase project...)  So you CAN in fact do this:

select a.*, currentTime from omnis_datafile a where a.update_time >= last time we ran this code
calculate myTimeToWriteBack as List.1.currentTime
for each row in the list
 update
 if rowsaffected = 0
   insert
 end if
 next
end for
update someTable set lastUpdateTime to myTimeToWriteBack
if rowsaffected > 1
 commit
end if
rollback

I would only commit when ALL the work is done.  Otherwise you don't know what was committed and what was not.  In any event you'll probably have to update the time you last ran this somewhere and that should be part of the code as shown above to assure your times are held properly and you don't miss any rows that have been updated by the O7/Studio applications hitting the datafile.

Hope this is helpful...

Regards,

Joe Maus



More information about the omnisdev-en mailing list