--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> ...... A select count has to fetch all records to count them.

This is the answer!
Attention to the developers!
MSSQL can do this very well ;-)

>
> 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.
>