Subject Re: [ib-support] Utility to find slow queries?
Author Mark Patterson
Helen Borrie wrote:
> At 03:59 PM 20/02/2003 +1000, you wrote:
>>We used Firebird workbench and found that if we data-pumped the whole lot out
>>and back in it ran much faster. What had been taking 9s was taking <2s.
>>So, explain that! Especially if it helps us to tweak our DB.
> Oh, it helps. Your indexes got rebuilt in the process of emptying and
> refilling....It seems as if you need to pay close attention to the indexes
> your database inherited from its Paradoxical past, or you will be right
> back there quite soon. Go back to Svein's suggestions, and to my warning
> about duplicate indexes, and start pruning and grafting.

The indexes we have were all added after the conversion from Access (FB is the
3rd attempt to get this working). I tried dropping and recreating relevant
indexes, including the style suggested by Svein. The result was not the dramatic
improvement we had, only a few seconds were taken of one of the threads. However
the comparison is not entirely valid, since I was testing the indexes on a
much bigger table than the one my colleague had the dramatic results on. He took
the bigger one (~200MB) home last night since it was taking ages to datapump.

> A relational DBMS it won't perform well if you retain the hierarchical key
> structures that were needed for Paradox to implement dependencies. Look
> there, too, because those indexes form long chains of duplicates which will
> furball your queries to a standstill.

Well, that's not what we are doing, but you may be onto it. Is there some
explanation of this on the web that you could point me to?