Update -

First, thanks to everyone for helping me think my way through this problem that seems to be resolved.

First, for safety, I created a backup of the SAM database using the SAM tool as well as a simple directory copy. (seems to be the same thing).

I then backed up the SAM database with FlameRobin with SAM Broadcaster shut down completely. The resultant backup file was more than 50% smaller than the production file.

I then restored the FlameRobin backup over top of the production database. All the performance problems seem to be solved.

I will consider upgrading to a newer version of Firebird after playing with SAM on another computer...

Thanks again to all.

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 04:48 AM 20/02/2010, smgold wrote:
> >Is there any advantage to upgrading the Firebird 2.1.0 database that
> >holds my SAM Broadcaster music information? There are approximately
> >80,000 songs in the database and I suspect I may have reached some
> >operational limitations, especially in terms of searching.
> >
> >If an upgrade to 2.1.3 is recommended, how would I go about doing this
> >please?
>
> At this point, I would NOT recommend anything until we have better information about what you are dealing with here. If you want help from this list, you could begin by telling us the operating system running on your machine (network?), the NAME of the database file (this really *matters* on Windows!), and about the directory structure and contents of the Firebird part of your installation.
>
> As a reality check, do you have (or could you borrow) another computer, on which you could install Firebird 2.1.3 temporarily and work at "knife-and-fork level" with a copy of that database?
>
> From what you have mentioned in other posts, the problem you are encountering will not be cured by upgrading - but that's not an advice *against* upgrading once the database is fixed. Firebird 2.1.0 is pretty old and many bugs and performance issues have been addressed in the three succeeding sub-releases. If you want convincing about that, read this document:
> http://www.firebirdsql.org/rlsnotesh/bugfix210.html
>
> Your mention that the vendors recommend MySQL for "large" databases at least raises the suspicion that the software is not addressing the issue of transaction management. Degradation of performance as a Firebird database grows is a major symptom of an application program that does not take proper care of transactions.
>
> An "operational limit" of sorts can develop, therefore, due to flaws in the programs that write to the database. It should not exist, but Firebird does not perform well on a dirty database. The dirtier it gets, the slower it gets. An application written for MySQL (which has a totally different architecture and does not do transactions) will thus be a progressive killer for Firebird.
>
> That means the problems lie with the SAM software application itself and you probably don't have much hope of preventing it developing again, once you've fixed the database.
>
> What happens with application flaws like those we can suspect is that "garbage" builds up and builds up as records are deleted or updated. Not only does the database itself become huge from all the garbage left behind, but the time to find the "good" records amongst all this cruft just takes longer and longer as the engine ploughs through thousands of pages of old record versions to find the active ones and choose the requested one. A dirty database typically also develops inefficient index organisation....and optimal indexes are essential for fast searches.
>
> Upgrading to a higher (and therefore better) sub-release of Firebird does not change the database. Therefore, although that upgrade is desirable for eliminating the effects of known bugs, the newer engine version will have the same problems with a dirty database.
>
> The first thing I would want to do is find out about the hygiene status of that database. Firebird does come with tools that can help to look at that. They might or might be present in your installation.
>
> Have you *ever* tried to back up the database? If not...and you have 80,000 very large blobs and an unknown and possibly huge number of dead ones....then I would handle the application's backup and restore feature with extreme caution. On the other hand, if the application offers a "backup only" option, it could be worth trying - provided it uses Firebird's gbak code. Just performing a gbak backup will do a lot of garbage clean-up, possibly even enough to alleviate the performance symptoms for a while.
>
> Even so, there are so many unknowns. If backup-only is available, and you do it, be prepared for it to take many hours, as it probably has a huge amount of cleanup to do on its way through; and it seems likely that there will be huge numbers of blobs to stream out to the backup file (which is not a database, but a specially formatted compressed text file).
>
> If you do it, make certain that the application is not doing *anything* else, i.e., make it the first and ONLY thing you do in the SAM session. This isn't normally a requirement - gbak can happily do a "hot backup" - but because you have a problem database here, we want to isolate the transaction that gbak is running in from any (or as many as possible) other transactions. Of course, don't be trying to run other applications (such as FlameRobin) at the same time, either.
>
> You must also make certain that you have plenty of disk space available to receive the data. Usually, a backup file is smaller than the database but, with all those huge blobs, it might not be the case.
>
> Given the other symptoms, we need to be suspicious about the safety of everything that is implemented. If the only option is "backup and restore" then don't do it from the SAM application at all; or, at the very least, take a file copy of the database file BEFORE you start SAM and store it in a very safe place.
>
> ./heLen
>