At 11:11 PM 6/10/2007, you wrote:

>We are using a Firebird 1.5 SQL database for a data warehouse on a number of
>sites, where an external application does a nightly 'kill & fill' of the
>database. The database contains about 20 tables of business data, and about
>1/4 of the tables contain in excess of 20,000 rows.
>
>What we have been noticing is a gradual deterioration of speed over time
>with this process. Yet the performance of the database for queries is
>acceptible.
>
>The nightly process first does a DELETE for all rows in all tables, setting
>the database to empty. Then it does a parse through the 20 core tables
>compiling the business data to load, and constructing an INSERT statement
>for the data into those tables. This is where we are seeing the
>deterioration of speed. What started off as a 1 hour process for one site,
>has gradually deteriorated to a 3 hour process, etc. My concern is that if
>this continues, the nightly process will spill over to daily transaction
>processing on their master system, making it unusable.
>
>Is there anything that can be done to optimize a database for this type of
>use? I'm happy to reduce the query performance down a bit to gain on INSERT
>performance. There are some foreign key indicies on this database for
>referential integrity, and I took a sample of one table that is noticeably
>slower for inserting over time, and noticed that it had 4 FKs on it, but NO
>ACTION on the Constraints for them. There are Generators on each time with
>a Before Insert trigger, but that is simply to assign a Unique ID for the
>new rows. That's about it.
>
>All help in optimization suggestions are greatly appreciated.

Are you shutting down the database and then doing a sweep in
exclusive mode after all those deletes? If not, then it's likely you
have a heck of a lot of pages (data and index) that are partly full
or full of uncollected garbage. If you're running SS there there's
probably going to be an added benefit in the shutdown besides this,
to 'uck out the cache and lose cached pages that no longer exist.

Do you schedule a full backup and restore periodically?

If you're using Delphi apps that haven't had a rigorous going-through
for a while, you might find some garbage-retention black holes that
you could aim to fix. (Unless you already got caught in the Commit
Retaining trap earlier in history and have already done the detox.)

./hb

>Regards,
>Myles
>
>============================
>Myles Wakeham
>Director of Engineering
>Tech Solutions USA, Inc.
>Scottsdale, Arizona USA
>www.techsol.org
>Phone +1-480-451-7440
>
>Try our new Outlook Utility 'Split Personality'
>http://splitpersonality.techsol.org
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://www.firebirdsql.org and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>