Some tools have the ability to fetch rows as needed. I'd be surprised if the first 'execute time' included the time needed to fetch all records and not just the first few (if I'm right, adding "ORDER BY TBL.LASTNAME||''" would increase the time from 16ms). A select count has to fetch all records to count them.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of kadriizer
Sent: 3. desember 2009 11:52
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Query with aggregation is slow!

Hi All,

Below, I have a query and performance information ...

SELECT * FROM (
SELECT
L.ID,
O.ORDERDATE,
T.TXT CATEGORY,
P.TITLE,
P.PRICE UNITPRICE,
L.QUANTITY,
C.FIRSTNAME, C.LASTNAME,
C.CITY,
O.NETAMOUNT, O.TAX, O.TOTALAMOUNT
FROM ORDERLINE L /* has 60222 records */
LEFT JOIN ORDERS O ON L.ORDERS_ID = O.ID /* has 20000 records */
LEFT JOIN PRODUCT P ON L.PRODUCT_ID = P.ID /* has 10000 records */
LEFT JOIN CATEGORY T ON P.CATEGORY_ID = T.ID /* has 16 records */
LEFT JOIN CUSTOMER C ON O.CUSTOMER_ID = C.ID /* has 10000 records */
WHERE L.ID > 0
) AS TBL

------ Performance info ------
Prepare time = 0ms
Execute time = 16ms

This query, "select * from" portion, as "select count (*) from" is changed, the performance information is as follows ...

------ Performance info ------
Prepare time = 0ms
Execute time = 1s 904ms

First query is faster than second query! Why?

Kadri.