Frontbase optimization

[Nick Renders]

I agree that your strategy is much cleaner. It has always bugged me  
to try insert
first and rely on the key constraints to return an error, but most of  
the tables we
sync mostly have new records and very few changes to existing  
records. Still, I
think I will take on your approach and see if there is much  
difference in speed.

I don't agree about commiting everything at the end, though. If an  
insert or
update for one record has failed, we report that error and sync the  
other records.
That way the Database is more or less up to date and the other  
records can be
used by the other applications. The "lastUpdateTime" isn't updated of  
course,
to make sure everything is in order when we figure out what went wrong.

On a completely unrelated note: am I the only one who finds it harder  
to use the
Omnis Help with every new release? Whatever happened to the "List  
defined
from SQL class" topic in 4.3?

Regards,

Nick



On 15 Apr 2009, at 15:57, Joe Maus wrote:

>
>> 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
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com