Subject Re: [firebird-support] Firebird, ODBC, web access and various beginner questions
Author Svein Erling Tysvaer
Hi Thomas, welcome to the world of Firebird!

Firebird and Access are two very different databases, and some things
that work OK with Access will destroy all performance if done the same
way with Firebird, e.g. Access will not suffer too much if you go
sequentially through a table, whereas Firebird thrive a lot better when
queries select only a few rows and columns. Likewise, doing things
across a local network or over the internet requires different approaches.

From your e-mail, I read that you are starting to get performance
issues regarding the databases, and this is a good place to obtain
suggestions regarding Firebird performance (second only to the Firebird
conference that is due in Hamburg next month?). To get the best answers,
you ought to tell more about your current system - for Firebird, that
includes information about things like database statistics when things
are slow, transactions, how many concurrent users, more exact
information about what is slow and possibly some information about your
queries. Sometimes, performance problems are easily solved by simply
taking proper care of transactions, modifying queries or adding indexes,
at other times, more drastic changes are required.

Sorry for not answering your questions,

Thomas Løcke wrote:
> Hey all,
> In my business I have a mission critical application. This application
> is more or less the core of my product. It was custom build for my
> company a good 6-7 years ago. It utilizes 3 databases: 1 Firebird and 2
> Microsoft Access. All three databases are located on the same server as
> the application itself. The application is Windows only, so naturally
> the server is installed with Windows.
> The connection to the Firebird database is done "natively" in the
> application, which is programmed in Delphi. The 2 Microsoft Access
> databases are accessed using ODBC. One of the Microsoft Access databases
> are also being accessed by a webserver (also via ODBC). This webserver
> is also installed on the server.
> Lately my business have seen a surge in new customers, and performance
> is starting to be an issue. The server is already running the "fastest"
> single core CPU money can buy, as going multicore is not an option. The
> application craps out in the presence of more cores. Redoing the
> application to run on a multicore system is too expensive, so that's not
> an option. The performance issues are database related, as the
> application itself doesn't use a lot of resources.
> The amount of data being moved to and from the databases are miniscule,
> but the queries can be quite taxing.
> I've thought about moving the three databases to a new multicore Linux
> server with Firebird installed. I would then convert the two Microsoft
> Access databases to Firebird and still access them using ODBC.
> My questions are:
> Is the Firebird ODBC driver stable? I've noticed the latest versions are
> considered BETA, and the non-BETA versions are quite old. Which one
> should I opt for?
> Is Firebird suitable for web access? I will be accessing the database
> from a PHP powered web application situated on a dedicated web server
> (Apache). The current solution where I access the Microsoft Access
> database using ODBC is by no means fast, so I'm not spoiled.
> Classic or Super? I've read the papers on the subject, and it seems to
> me that I should go for Classic, seeing as I would like to fully utilize
> a multicore system. Is this assumption correct?
> How important is RAM? The databases aren't very large (a few hundred
> megs total). On the Windows server it seems the Firebird instance never
> grows beyond 25-30 MB. I know from a few MySQL servers I run that RAM is
> God. The more the better. It seems this might not be the case for Firebird?
> How important is harddrive speed? IDE, SATA, SCSI?
> Intel or AMD? I personally have no preference, except I tend to cheer
> for the little guy. :o)
> OS suggestions? I plan on using Slackware, as all my other servers are
> running Slack (except of course for the one Windows server discussed above).
> This is my very first venture into the Firebird world. Any and all
> advice is welcome.