Good to hear, Cameron! Though if it was the query size that was the problem and you continue to add UNIONs to your view, you may get more trouble in a week or two.

One thing that you could consider, is that rather than use:

SELECT a.FieldA, ...
FROM TableA a
JOIN TableB b ...
WHERE a.WhereField = 1
UNION
SELECT a.FieldA*a.FieldB, ...
FROM TableA a
JOIN TableB b ...
WHERE a.WhereField = 2
UNION
SELECT a.FieldA/a.FieldB, ...
FROM TableA a
JOIN TableB b ...
WHERE a.WhereField = 3

you could use

SELECT
CASE
WHEN a.WhereField = 1 THEN a.FieldA
WHEN a.WhereField = 2 THEN a.FieldA*a.FieldB
WHEN a.WhereField = 3 THEN a.FieldA/a.FieldB
END, ...
FROM TableA a
JOIN TableB b ...

(this should at least be possible for the table before and after your first UNION, although your WHEN...THEN clauses will look a bit more complex)

Another thing that I noticed, is that generally when having both LEFT [OUTER] JOIN and [INNER] JOIN, then Firebird 1.5 (I think it also matters for 2.x, but am uncertain) prefers to have the inner joins at the top (the optimizer can choose which of the JOINs that go first in the plan, with LEFT JOIN nothing after the LEFT JOIN is moved above the LEFT JOIN in the plan). This doesn't matter for the result, but may be of importance for the performance.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of auceejay
Sent: 28. januar 2009 22:11
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Crashing Firebird 1.5 Server with a query

Wow thanks so much, just with the better written query, it has made
it all work :) I'll go ahead now and optimise the rest, but thanks a
lot to you both...

2 weeks of query writing is now not a waste of time!


Thanks,
Cameron