Does

WITH MyCount(TotalOrders) as
(SELECT COUNT(*) FROM Orders)

SELECT NAME, TotalOrders
FROM Customers
CROSS JOIN MyCount

help anything?

If not, then try

EXECUTE BLOCK
returns (name VarChar(128), TotalOrders integer) as
begin
TotalOrders = (select COUNT(*) FROM Orders);
For SELECT Name FROM Customers into :name do suspend;
end

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of kokok_kokok
Sent: 18. februar 2011 10:34
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Non-correlated subquery is really slow

How can I rewrite a non-correlated subquery to improve the performance?

For example:

SELECT
(SELECT COUNT(*) FROM Orders) AS TotalOrders,
Name
FROM Customers


Firebird reevaluates the subquery for each customer record. Note that the subquery is not correlated, so there is not point to read all records of subquery again. It could be executed only once for all Customer records.

How can I improve the performance?

Thank you