Hi Thomas,

> From what i can read on the internet TempCacheLimit, is per server
> process, and not per attachment.

In SuperServer architecture, right, but if I remember correctly, you
have mentioned in one of your first posts that you are using
SuperClassic, right?

> http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html

Talking about SuperServer.

> https://www.mail-archive.com/firebird-support@yahoogroups.com/msg10199.html

Talking about SuperClassic with answers that it is per connection.

>
>
> I believe I have found the statements that caused the problem, first one
> was executede about 400/second and contained a union, that was replaced
> by union all in the hope that would eliminate the need for temp cache.

UNION vs. UNION ALL. UNION is removing duplicates, thus some sort of
applying DISTINCT with first sorting to remove duplicates is applied.

DISTINCT, sorting, group by (internally first doing a sort) will
internally produce temporary data.

> The second statement is a really nasty dynamically build common table
> expression containing upwards of 10 union.. Here we are in the process
> of moving this search to Elastisearch.
>
> We just purchased FB TraceManager at our company to weed performance
> issues  - Thanks for a great product : )

Great to hear. Thanks. :-)


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.