At 11:36 PM 2/08/2008, you wrote:

>Hello,
>
>we use Delphi and Bold For Delphi to access a Firebird-DB. Bold is an
>OR-mapper whichs converts our OCL into SQL.
>
>In our program we do a complex search which produces the following
>SQL:

...Lots and lots of NOTs...

It's intrinsically slow because of all of those NOTs. It's slower than it needs to be because, in places, you are using NOT(SELECT COUNT * > 0) as an existence test!! and NOT IN (SELECT...) structures: replace both of these with with NOT EXISTS (SELECT 1....). Another source of slowdown is what appears to be indexes on fields design to be Boolean switches. Remove these, or recreate those indexes to include the primary key value as the right-hand key of a composite index....I do see also some potential for slowdown in your use of COLLATEd index keys - try to do without these if they are not needed.

Can't help thinking that a relational database engine is not a great choice as the data provider for Bold...but then, I do have a bias against object-relational schemes in general - like using a shark as bait for catching goldfish, except that you have to kill the shark and make it into fishburger to get it working in practice...

./heLen