| Subject | Re: [firebird-support] Software speed issue |
|---|---|
| Author | Mark Rotteveel |
| Post date | 2013-07-18T15:13:39Z |
On Wed, 17 Jul 2013 16:32:19 -0400, "Dom Kasony" <DomK@...> wrote:
caching options to solve performance options. These settings are not a
panacea that will simply fix performance problems. Caching only helps if
the data has been requested before and has not been evicted from the cache
before it is needed again. You are talking about generating reports, which
are usually on previous time periods, large aggregates than normal
application use or data not (very recently) requested. I'd suspect the
cache is not the bottleneck, so tweaking the cache won't help (much) with
loading quicker. And if a report is very complex to query, sort and/or
build, that can be a performance bottleneck on its own.
I would seriously consider looking at other factors like the queries used
to produce the reports (eg use the trace facility to get more information
on the queries used, execution plans and their times and IO stats). Also
check if the current indexes are sufficient for these queries. Another
thing to look at is if the way the report is generated is actually
efficient. For example at my current job an in-house app using SQL Server
took 3 minutes to generate a report, simply because a new employee fresh
out of college iterated over the result of a query to fire additional
queries for each row in that query, replacing that query + loop with
queries by a single (slightly) more complex query brought the run time down
to 15 seconds (which is still relatively slow due to other inefficiencies
and missing indexes, but it is fast enough for our in-house use).
Mark
> We are running Firebird 2.5.2 and the software we use that utilizesonly
> firebird
> is very slow in some areas such as compiling reports. Do to caching and
> speed, they suggested that we increase the page size and percent of RAM
> that
> can be used. We did this and saw little to no increase in speed. Our
> database is 120 GIG and we have 96 gig of RAM available which we are
> using 5 gig of that 96 gig. Can anyone suggest settings that can bevalues
> changed/activated (they originally had all of the commands in the
> firebird.config file commented out so they were not active) and what
> we can try to increase the speed? We want their software to be muchfaster
> than it is and they have no other ideas. Thank youTake a step back and ask yourself if you should looking at the low level
caching options to solve performance options. These settings are not a
panacea that will simply fix performance problems. Caching only helps if
the data has been requested before and has not been evicted from the cache
before it is needed again. You are talking about generating reports, which
are usually on previous time periods, large aggregates than normal
application use or data not (very recently) requested. I'd suspect the
cache is not the bottleneck, so tweaking the cache won't help (much) with
loading quicker. And if a report is very complex to query, sort and/or
build, that can be a performance bottleneck on its own.
I would seriously consider looking at other factors like the queries used
to produce the reports (eg use the trace facility to get more information
on the queries used, execution plans and their times and IO stats). Also
check if the current indexes are sufficient for these queries. Another
thing to look at is if the way the report is generated is actually
efficient. For example at my current job an in-house app using SQL Server
took 3 minutes to generate a report, simply because a new employee fresh
out of college iterated over the result of a query to fire additional
queries for each row in that query, replacing that query + loop with
queries by a single (slightly) more complex query brought the run time down
to 15 seconds (which is still relatively slow due to other inefficiencies
and missing indexes, but it is fast enough for our in-house use).
Mark