Hi Todd,

> There are 46,527 records in the main table, but the calculation touches
> many other tables. Here is a copy of the analysis of the main procedure
> from IB Expert:
>
> Query
> ------------------------------------------------
>
>
> Plan
> ------------------------------------------------
>
>
> Query Time
> ------------------------------------------------
> Prepare : 156.00 ms
> Execute : 16.00 ms
> Avg fetch time: 16.00 ms

How does that fit with your previously mentioned ~ 5 minutes?

> Memory
> ------------------------------------------------
> Current: 9,821,456
> Max : 9,951,376
> Buffers: 75

Default buffers value of 75, which is completely undersized these days.
Depending on the page size of the database, e.g. 8K, this results in ~
600K RAM usage for the page cache per connection in SuperClassic (SC)
and Classic (CS). Max memory usage looks like that the default 8MB for
the temp cache limit per connection is in place, again for SC/CS.

Especially for GROUP/ORDER BY statements an increased temp cache might
result in better performance, because statements with bigger memory
requirements to fulfill in memory sorts won't spool onto disk.

> Operations
> ------------------------------------------------
> Read : 2,083
> Writes : 3
> Fetches: 44,488
> Marks : 13
>
>
> Enchanced Info:
> +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
> | Table Name | Records | Indexed | Non-Indexed |
> Updates | Deletes | Inserts | Backouts | Purges | Expunges |
> | | Total | reads | reads
> | | | | | | |
> +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
> |CODERATES | 43591 | 7 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |CODES | 18127 | 4 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |CODEVALIDATIONS | 88777 | 6 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |CODEVALRATES | 38319 | 2 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |IMPROVEMENTHEADER | 74804 | 6 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |INCOMEHEADER | 9842 | 1 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |LOCATION_TABLEEXT | 2450 | 3 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |MARKETLANDHEADER | 35886 | 2 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |MUNICIPALITIES | 15 | 9 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |PROPERTYHEADER | 46527 | 10 | 0
> | 0 | 0 | 0 | 0 | 0 | 0 |
> |TAXPASSOFF | 603366 | 78 | 0
> | 4 | 0 | 0 | 0 | 1 | 0 |
> +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Looks again a bit strange when looking at Records Total vs.
Index/Non-Indexed Reads.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.