At 10:18 AM 31-01-02 +0000, you wrote:

>We have converted an app from paradox to interbase.
>So the tables are the same, the indexes are the same, even the data
>is the same dataset - just datapumped from paradox to interbase.
>
>Using TUTI on the paradox db, the query
>
>SELECT sum(routing.subtotal) as SumAgentTotal
>
>FROM hub, waybill, routing, agent
>
>WHERE costcntr = 2
> and routing.agent = agent.agent
> and agent.ownresource = FALSE
>
> and waybill.orighub = hub.hub
> and waybill.waybill = routing.waybill
>
> and invdate >= '08/01/2001'
> and invdate <= '08/31/2001'
>
>takes 3 seconds
>
>Using IBConsole on the interbase db the same query (on the same PC)
>takes 50 seconds - the only diff in the query using 0 instead of FALSE
>
>Using D5 - IB6.
>
>Any ideas ?

Plenty.

1. InterBase is not a desktop database (not even the "desktop edition" is). So the principles of both storage and retrieval are quite different. Indices do quite different jobs in each; and the optimal embedded primary key structures of Paradox simple tie up InterBase, for numerous reasons.

2. The Borland Database Engine (BDE) is the native engine for Paradox and is designed to work with physical images of Paradox tables in memory. The BDE forces all databases to behave as if they were Paradox. This does not work well for client/server databases such as InterBase, which does not store data in physical tables at all.

3. In fact, the BDE cripples databases designed for client/server use, not least because it ignores the interplay of multiple transactions in IB's true operating conditions. In conversions such as yours, it is not unusual to discover that your machine is being slowed down by large numbers of uncommitted transactions over the course of a day, inhibiting or completely prevent garbage collection.

4. InterBase was not designed to work with the server, the client and the client application running on the same machine, competing for resources. Paradox was very skilfully designed for a single user on a single machine and, natively, is very economical with the resources of a single machine. InterBase Server was designed to work on a dedicated server machine and to have its databases accessed by remote client workstations.

5. If you are running IB Server on a SMP machine under Windows, you will experience the "ping-pong" effect, where, instead of sharing processing demand between multiple CPUs, Windows swaps the entire load constantly from one CPU to another. (Both Firebird and commercial IB 6.5 allow you to set processor affinity in the server configuration. The IB_Affinity utility is available for other versions, provided they run as an application under Windows.)

6. If your server is running on ME or XP, some really horrible effects will be observed with database files carrying the .gdb extension, the result of a Microsoft plot named SystemRestore.

7. The query example you give here is very poor SQL, characteristic of the restricted pseudo-SQL that Borland provides as a lowest-common-denominator of the SQL language, in order to support mashing statements down internally into the languages of the desktop database engines. Real SQL databases support much richer language sets.

In short, a Paradox to InterBase conversion is not trivial. The initial conversion, datatype for datatype, data for data, is relatively simple. The review and re-design of keys, structures, indices and query statements is major - a fairly steep learning curve to begin with, but well worth the effort.

If it's a desktop database you want, then IB will be a far-from-perfect substitute for Paradox. It simply isn't in the desktop database race.

regards,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________