At 11:54 AM 28/03/2009, you wrote:

>We have a strange scenario at one of our sites where one query slows down dramatically after we call SET INDEX STATISTICS.
>Unfortunately this is used a lot so it causing performance issues throughout the system.
>
>The basic query is:
>
> SELECT DISTINCT BR.BASE_SN, R.TITLE
> FROM RES R
> JOIN BASERES BR ON BR.BASE_SN = R.BASE_SN
> WHERE R.STATUS=1 AND BR.STATUS=1 AND
> BR.BASE_SN IN (<insert list of primary keys>)

Try this:
SELECT DISTINCT BR.BASE_SN, R.TITLE
FROM RES R
JOIN BASERES BR ON BR.BASE_SN = R.BASE_SN
AND BR.BASE_SN IN (<insert list of primary keys>)
AND BR.STATUS=1

WHERE R.STATUS=1

and give the plans for both versions.

>
>Both the BASERES and RES tables have about 25000 records. There is a FK constraint R.BASE_SN -> BR.BASE_SN and BR.BASE_SN is the primary key of the BASERES table.
>The status columns are not indexed as there are only 2 distinct values. However, I did try adding an index to see if that makes any difference and it doesn't.

No, it would make things worse in v.1.5.x.

>For all our other sites this is not a problem and this site runs ok after exporting and importing the data. Then, if we update the index statistics then the performance drops.
>For them, the query take 90secs but after export/import, the query takes about 6secs. This returns just 334 records.
>
>If we change the WHERE to R.BASE_SN IN (<insert list of primary keys>) then the query is again fast. Unfortunately, for all other DBs tested from other sites,
>the performance is worse when we change the alias.
>
>Using the clients database, it is easy to replicate.
>
>1. Load IBExpert and run query (fast)
>2. Recalculate index statistics in IBExpert
>3. Run query again - now slow.

It's more than likely something to do with the caching. Test whether running the same query a second time is just as slow.

For your own edification, you could get some clues about the usefulness (or not) of various approaches if you studied the plans used. You might get more direct info about what the *optimizer* is doing by using isql for those tests. AFAIK, IBExpert fiddles about with plans according to its own rules and could well defeat better choices made by the optimizer.

>Basically fbserver gets locked at 100% CPU and noone else can use the database.
>This is using FB SS 1.5.5 on Windows and Linux.
>
>Any thoughts as how we can resolve this issue?

For one thing, I don't think doing SET STATISTICS on the fly is necessarily doing you any favours since it necessarily blows away any benefits you have accumulated from the cache.

For another, SELECT DISTINCT is a performance killer (esp. in v.1.5 and below) because it can't use an index at sort time. You might like to try out GROUP BY for the sort instead and see if it improves things, viz.

Try this:
SELECT BR.BASE_SN, R.TITLE
FROM RES R
JOIN BASERES BR ON BR.BASE_SN = R.BASE_SN
AND BR.BASE_SN IN (<insert list of primary keys>)
AND BR.STATUS=1
WHERE R.STATUS=1
GROUP BY 1,2

And, finally, for performance testing I'd stay away from elaborate tools that do fancy things with your query specs. Get familiar with isql for this kind of thing: you probably shun it because it is so "bare-bones", but when "bare-bones" is what you really need, it's worth the effort !

./heLen