--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...>
wrote:

>
> > Hi Group,
> >
> > Is it safe to recompute an index through a PSQL 'execute

statement'

> > call? I wish to implement a stored procedure to update all the
> > statistics. I plan to call it periodically (ie when the software

is

> > upgraded), or on demand if customers are experiencing slow

selects to

> > assist Firebird choosing a sensible PLAN.
> >
> > eg:
> >
> > CREATE OR ALTER PROCEDURE SP_CALCSELECTIVITY
> > AS
> > DECLARE VARIABLE IDX VARCHAR(50);
> > DECLARE VARIABLE STMT VARCHAR(75);
> > BEGIN
> > FOR
> > SELECT RDB$INDEX_NAME
> > FROM RDB$INDICES
> > INTO :IDX
> > DO
> > BEGIN
> > STMT = 'SET STATISTICS INDEX ' || :IDX;
> > EXECUTE STATEMENT :STMT;
> > END
> > END
> > ^
> >
> > Thoughts? Comments? Warnings?
> >
> > Adam
>
> I've always done it via separate calls to system tables then calls

to the

> indexes contained therein but I can't see a problem with your

single call.

> I have to confess, though, that I never had a user ring me up and

say

> "hey... what did you do? it's running so much faster now!!". Even

*I*

> couldn't tell the difference - ever.
> I did, however find an index missing once (one I thought should

always have

> been present but wasn't) so I added it and even though users never
> commented, I asked a few and they did sort of say "oh yeh - it is

running a

> bit faster in that browse now isn't it"..

Users are forgetful and not a good indicator of performance. A few
weeks back I used a simple +0 on a join condition in a query being
run by a report. This improved the speed from around 2 minutes to
subsecond. When asked, how the report performed this week, we got
the 'it was still slow' treatment, but they were clearly thinking of
some other time they had run the report (they weren't even logged in
for 2 minutes).

Much better feedback can be had by examining the server resources
during peak times. 1 bad plan on a query can really affect a lot of
people fighting for the same resources.

So I am not doing this as some magic grab for performance. I am doing
it so that Firebird behaves predictably between 'similar' customers.
I usually know the data shape of a given customer and can estimate
the prefered plan (or at least recognise a really bad plan). Just a
few moments ago I noticed a query running a bad plan, so I set
statistics on the relevant index and voila, no code change but
everyone is happy.

Adam