Subject Performance Issue Solved
Author Todd Brasseur <>
First of all, thanks to everyone for your responses to my problem.
Today I found the problem and I will try to explain in case anyone
else runs into the same thing.

We have been operating this application for over two years now and
have never been happy with the performance. As I said in an earlier
post, a calculation that took 10 minutes in the old DOS version now
was taking approx. 2 hours. The good thing was that this process only
needs to be run periodically. The problem was that simple reports and
processes took maybe 5 or 10 seconds when they should have been almost

Well today I starting remarking out code and running the procedure
with portions of the code removed. My test database took 10 minutes
to run the procedure. After a couple of hours, I found that by
removing one call to a procedure, the time went down to 2 minutes. By
looking at the procedure I pinpointed my problem to one table.

This table (CodeRates) is used intensively in the application (85,000
records). It stores rates associated with Codes. It consisted of a
RateType, a Rate, a SingleCode, and a CombinationCode. The intent was
to either have a SingleCode, RateType and Rate or a CombinationCode,
RateType and Rate (never both a SingleCode and CombinationCode).
Since one or the other was always NULL, I couldn't declare a Primary
Key. But I built Foreign Keys and Indexes and also check contraints
to ensure data integrity. When I tested queries against the table
Firebird showed it was using appropriate indexes. When I ran stored
procedures and looked at performance (Using QuickDesk) it showed that
all reads were indexed. Still I knew the problem was with this table.
I figured it must be because this table didn't have a Primary Key defined.

So I added a Primary Key. It wasn't used for anything. Just a dummy
variable to make each record unique and to allow me to declare the
Primary Key. After doing this and running the procedure, It still
took 10 minutes.

So I split the data into two tables (CodeRates &
CodeCombinationRates). Each has a primary key (Code and RateType).
I changed my procedures (61 of them) and triggers (12) and tables (2).
To now look at the different tables.

Well I am extremely happy to say that the procedure now takes 1.5
minutes. The one test we ran showed that Firebird was quicker now
than the old DOS version. Reports that were taking many minutes now
happen in seconds.

Now we just have to change a bunch of forms in the interface and we
can get it out to our clients. It is going to be a Happy Christmas
for them, as well as for us here at COMPASS Municipal Services Inc.

Thanks again everyone for your help.

Todd Brasseur
COMPASS Municipal Services Inc.
Edmonton, Alberta, Canada