Migration from IB 6.0.2.0 -> Success, but 1 major issue :(

We've successfully migrated or Visual dBase 7.5 app that was running for quite some years on IB6 on a NT4 machine to a new (win 2003) server.

Due to the incompatibility between IB6 and Win2003 (lag connecting to DB the first time of 15-30 seconds), we were forced to upgrade to Firebird 1.5.3.

With some minor adjustment in the sloppy application-code everything works the way it should. (Finally got rid of ol' NT4 and performance in general went up a lot).

The application has a scheduled update every night to update the IB/FB database with the latest data. With IB this process took about 45 minutes to 60 minutes. With FB this takes about 180 minutes now.

I'll try to describe what happens during this scheduled update below:

All relevant tables are copied from IB/FB to the local drive, where processing of the files is done (a lot of DBF and MDX files are created, whole process takes about 15 minutes). Afterwards all the data is send back to IB/FB database. (Took 30-45 minutes with IB, takes 2 hours, 45 minutes now with FB).

Server is Dual CPU, Intel, 3GHZ, 1GB mem, win2003. Network connection has been tested thoroughly. Also disabling the virus scanner, firewall etc.

Any idea how to improve this?

A piece to the Visual dBase code that sends the data back to FB:



[edit]
The largest table has about 250.000 records
[/edit]

Just did a test on my home PC (Athlon 1800+, win2k pro, 512 MB). Total update takes approx. 50-60 minutes which is just like with IB.

So win2003 seems to be the problem here ? Any ideas or suggestions? Will try and see if I can run the DB on a win2k machine tomorrow at work.

LS,

You did not specify which version of FB you are using. Is it the Super Server or the Classic ?

Note that for making use of SS you have to disable one of the cpu's. Otherwise, behaviour like you describe is to be expected. This is documented.

Kind regards,
Lucien Ceder

On SS is not really to disable one of the CPU but to pin Firebird to only one CPU, look at the config file.

I tried both Classic and SS of 1.5.3, both seem to have the same problem

Should the performance improve a lot if I turn off the indexes while inserting records and enabling them afterwards?

Yes

I've been closely checking what seems to slow down the insert. I was able to already improve the speed with 50% by running the update on the same machine as where the database itself is located. Obviously not sending the data over the network seems to increase the performance a lot.

What the update procedure does is the following:

Fetches all the needed tables from DB and converts them to DBASE tables. All processing is then done on the local DBASE tables. Once completed, all old data will be removed from the tables and the contents of the DBASE tables will be copied to the Firebird database.

The connection from the Visual dBase application to the database goes through BDE 5.2.

I noticed a huge difference in while inserting into tables that have TIMESTAMP and/or BLOB fields and ones that don't. The ones without have a lot better performance. At least 4-5x faster.

Since the biggest tables all have TIMESTAMP fields and/or BLOB fields, this is causing the biggest delay.

Any idea on how to optimize this?

Why BLOB? Are you storing images in the database?

Don't ask me why, but the manufacturer stores memo's or notes in a BLOB field (probably because this way the layout is preserved).

The 2 tables that contains the BLOB's are not that big (about 25000 records) each. The 6 tables that contain the TIMESTAMP fields are larger (about 250.000 to 300.000 records each). So the ones with the TIMESTAMP are more likely to cause the problems.