Reading my answer, I think I should clarify a bit. Firebird by no means have to move the records to the client, what it has to do is to inspect every record (on the server) to see whether it should be included in the count or not. This is due to Firebirds architecture, where several versions of a record can be stored. Upon counting the records, Firebird first needs to find the record version that is applicable to the current transaction and then see whether it should be included in the count.

MS SQL might use a very different architecture, unfortunately I do not know.

HTH,
Set

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

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