AW: Frontbase optimization

Rudolf Bargholz bargholz at onlinetravel.ch
Tue Apr 14 08:50:58 EDT 2009


Hi Nick,

1) Use bind variables, implicit when using $insert().
2) Use sessions, and do FrontbaseSessionObj.$commit() at regular intervals, e.g. every 100 records
3) Your import code could to look as follows:

Calculate FrontbaseStatementObj.$transactionmode as kSessionTranManual
For LineNumber from 1 to List.$linecount step 1
  Calculate List.$line as LineNumber
  Do Row.$assignrow(List)
  If LineNumber =1
    Do Row.$insert()
  Else
    Do FrontbaseStatementObj.$execute()
  End If
  If mod(LineNumber,100)=99
    Do FrontbaseSessionObj.$commit()
  End If
End For
Do FrontbaseSessionObj.$commit()

This will fill the Omnis SQL Statement buffer with the SQL insert statement. Provided you use bind variables and do not overwrite the SQL buffer for this statement with another SQL, you can issue an $execute, and this will fetch the current values of the bind variables before sending the data to the database server. This is much faster than telling Omnis to refill the SQL statement buffer for each row you are sending to the database server. You only need to do this for the first record you send to the server, i.e. List line 1.

Note, if you do fancy stuff like fetch the next sequence number before inserting the new record, the above will fail, as you will probably be overwriting your SQL statement buffer when you fetch the next id value. In order to use the structure above you will have to fetch the id values for all list lines you want to insert BEFORE executing the code above. We have a method that fetches the next x id records, so we only have to issue one SQL to fetch the sequence numbers for a large number of records. Also, you don't necessarily need to use a table class to insert the data, a simple schema class will do or you could manually build your SQL using a statement (Begin statement ... End statement). This only really makes sense if you do not have too many tables to transfer, but will speed up your code depending on how much complexity you have built into your

Some database client software (not sure about Frontbase) will allow you to import data into a table from a delimited text file. This will be very quick, but only makes sense if you have the option available, and you ensure that record sequences are not a problem.

If you are on a WAN connection to the database, think about uploading your import files to a file server on site in the same network as the Frontbase database and having a process there automatically import your files. The ideas above will not bring you much if the latency of the database connection is high.

Regards

Rudolf Bargholz

-----Ursprüngliche Nachricht-----
Von: omnisdev-en-bounces at lists.omnis-dev.com [mailto:omnisdev-en-bounces at lists.omnis-dev.com] Im Auftrag von Nick Renders
Gesendet: Dienstag, 14. April 2009 13:54
An: OmnisDev List - English
Betreff: 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



More information about the omnisdev-en mailing list