Hi,

I am currently working on a data extract project and have hit upon a problem that seems like a Firebird limitation but I wish to confirm if this is the case.

My development environment:

Firebird Version:

2.1.2 (x86)

Server Version:

WI-V6.3.2.18118 Firebird 2.1

Server Model:

SuperServer

Server Config:

<All Defaults>

DB Page Size:

4096

DB Page Buffers:

0 (default)

IDE:

Delphi 2007

Data Access:

IB-Objects 4.8 Build 10
TCP/IP

OS:

Windows 7 Professional (x64)

RAM:

4GB


The extract performs various aggregations on data to pull statistics and output to XML for import into 3rd party software. We had initially implemented elaborate queries for each section of the extract but hit major performance issues - it would never finish. We discovered that by breaking the queries into pieces and executing them in stored procedures to build the data incrementally, it would run much faster. To achieve this we have employed a few temporary tables which we fill with the aggregated data and then do a simple select as the final step. There are currently 3 or 4 temp tables with an average of 200 columns and a few indexes where required for joins and sorting.

Unfortunately with larger customer databases we have found the first stored proc fails after a minute with this error:

ISC ERROR CODE:335544351

ISC ERROR MESSAGE:
unsuccessful metadata update
cannot add index, index root page is full.

The point at which this occurs seems to vary between test platforms. It occurs even on fresh boot with only 1 active connection to the DB. Can anyone tell me how this could be happening? Are there any Firebird config settings that could help? My guess is that we may be pushing the limits of temp tables; it would be nice to find out what those practical limits are. Given that these are in-memory tables I have considered dropping the indexes to see if that works without too much sacrifice to performance.

There is the possibility this extract may be run on live servers with other users connected. If our implementation poses any risk there, we may need to revert to permanent tables though there is no need for us to retain the working data.

Thank you in advance for any assistance you can provide.


Best regards,

Ken Coffman
Communicare Development Team
Communicare Systems Pty Ltd
Tel: 08 6212 6900 Fax: 08 6212 6980
ken.coffman@...<mailto:ken.coffman@...>
www.communicaresystems.com.au<http://www.communicaresystems.com.au/>

Communicare Systems advises that this email account is not continuously monitored. Please forward any Helpdesk enquiries to our monitored address helpdesk@...<mailto:helpdesk@...> and note that urgent matters must be advised by telephone. This message, and any attachments, are intended only for the use of the individual or entity to which it is addressed and may contain information that is confidential. Any unauthorised use, disclosure, reproduction or distribution of this message and its attachments is prohibited. This message is forwarded from an individual and does not necessarily represent the views of Communicare Systems. If you have received this message in error kindly notify the sender and then delete the contents.



[Non-text portions of this message have been removed]