Frontbase optimization

[Nick Renders]

Hi guys,

Thanks for your replies.

The situation is a little more complicated then I made out in my  
first email,
so allow me to elaborate: our main Database are several Omnis Datafiles
which are used by both Omnis 7 and Omnis Studio (4.3) libraries. A few
tables from that Database have copies in Frontbase. The Frontbase DB is
only used for select statements, so any actual changes in the Database
are first done in the Omnis Datafiles and then later synced to  
Frontbase.
The Omnis 7 library is the major reason why we aren't writing to  
Frontbase
right now and the conversion to Studio is our first priority.

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.

Same goes for exporting to text and importing into Frontbase (just  
read your
email Geir), although that sounds like a good idea for resetting the  
Frontbase
Database.


Nick



On 15 Apr 2009, at 09:09, Geir Fjærli wrote:

> Hi Nick.
>
> One of my customer had exactly the same scenario a few years back.  
> Data was
> pulled from an Omnis data file, then written to Frontbase one by one.
>
> All the optimalisation in the world isn't going to make that fast,
> unfortunately.
>
> So what we ended up doing was using the strong points of each  
> platform:
> 1. We wrote the data to a delimited file using the Omnis report  
> engine,
> which is very fast.
> 2. Then we used the - also very fast - text file import tool in  
> Frontbase to
> import them.
>
> Back then, with Omnis being non Unicode, we faced the issue of  
> converting
> the text file to the appropriate format. The kind people at  
> Frontbase wrote
> a small conversion tool for us. Not sure if that would still be  
> required if
> using the Unicode version of Omnis.
>
> Unfortunately I do not have neither the code nor the conversion  
> tool handy,
> but I assume the customer still does. Contact me off list if you are
> interested in more info.
>
> Geir :)
>
> -----Original Message-----
> From: omnisdev-en-bounces at lists.omnis-dev.com
> [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On Behalf Of Nick  
> Renders
> Sent: 14. april 2009 13:54
> To: OmnisDev List - English
> Subject: Frontbase optimization
>
> Hi,
>
> We have an Omnis Studio (v4.3.1.4) application that syncs tables from
> an Omnis Datafile to a Frontbase Database. The premise is very simple:
> load the data in the instance of a table class (connected to Omnis  
> DF),
> copy it to an instance of a table class that is connected to
> Frontbase, and
> write everything to Frontbase.
>
> This works but is very slow when writing the changes to Frontbase.  
> I was
> wondering if anyone has any pointers on how to optimize this process.
>
> There are 2 issues that come to mind:
>
> 1) We use the standard v3 DAM methods $insert() and $update() to write
> 	the data to Frontbase. Will there be much difference if I override
> these
> 	methods and use the $execdirect() command with Bind Variables?
>
> 2) Everytime we connect to Frontbase, we use a new session (or  
> overwrite
> 	an existing one). The application is syncing every few minutes which
> 	means we could have one constant session open except that I haven't
> 	found a way to determine wether the session is still valid (like the
>
> $state
> 	property). Sometimes the Frontbase Database will go offline and the
> 	application is still	able to call $insert() and $update() with
> kTrue as
> 	return value.
>
> Any input is very much appreciated.
>
> Regards,
>
>
> Nick Renders
>
> ARC - your ICT service partner
> H. D. Saviolaan 8
> 1700 Dilbeek
> T: (00 32) (0)2 466 50 00
> F: (00 32) (0)2 466 88 33
> http://www.arcict.com
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com