Thanks for the information, it is very helpful,
but i don't think 966.0 ms is acceptable for selecting only 2000
records.
I've tried the data and query to M$Sql, and it took only 4 ms to
exactly the same query, and it took 2 ms without the coalesce.

Is there any work around for this problem? Thanks for your help

GB4EVER

--- In firebird-support@yahoogroups.com, Daniel Rail <daniel@a...>
wrote:

> Hi,
>
> At June 22, 2004, 03:30, gig_boy2001 wrote:
>
> > I've uploaded my sample database and query, please examine the

query,

> > n many thanks to help me find out the workaround.
>
> Coalesce is not an issue here. The differences between query 1 and
> query 2 in your examples confirms it, since query 2 doesn't have
> coalesce and has the same performance as query 1.
>
> What I was looking at was the query plans:
> Query 1:
> PLAN JOIN (JOIN (BVW ATBD NATURAL,BVW ATBH INDEX (PK_ATB)),
> JOIN (AVW ATBD NATURAL,AVW ATBH INDEX (PK_ATB)))
>
> Query 2:
> PLAN JOIN (JOIN (BVW ATBD NATURAL,BVW ATBH INDEX (PK_ATB)),
> JOIN (AVW ATBD NATURAL,AVW ATBH INDEX (PK_ATB)))
>
> Query 3:
> PLAN SORT (JOIN (B ATBD NATURAL,B ATBH INDEX (PK_ATB))
> PLAN JOIN (B ATBD NATURAL,B ATBH INDEX (PK_ATB)))
> PLAN SORT (JOIN (B ATBD NATURAL,B ATBH INDEX (PK_ATB))
> PLAN JOIN (B ATBD NATURAL,B ATBH INDEX (PK_ATB)))
> PLAN JOIN (A ATBD NATURAL,A ATBH INDEX (PK_ATB))
> PLAN JOIN (A ATBD NATURAL,A ATBH INDEX (PK_ATB))
>
> Place an index on ATBD.FK_ATB, and that will improve the

performance.

>
> But, with Query 3, I do notice that there seems to be a problem when
> using a subquery within COALESCE. the subquery seems to be executed
> twice.
>
> Here's the performance analysis of query 3 without COALESCE:
> --Query 3
> SELECT
> ID,
> CODE,
> DT,
> STATUS,
> FK_ITM,
> (
> SELECT SUM(B.QTY) FROM CVW B
> WHERE B.FK_ITM = A.FK_ITM
> GROUP BY B.FK_ITM
> ) QTY
> FROM CVW A
>
> Query Time
> ------------------------------------------------
> Prepare : 10
> Execute : 30,915
> Avg fetch time: 966.09 ms
>
> Memory
> ------------------------------------------------
> Current: 1,520,896
> Max : 1,577,388
> Buffers: 5,000
>
> Operations
> ------------------------------------------------
> Read : 54
> Writes : 5
> Fetches: 6,138,919
>
> Plan:
> ------------------------------------------------
> PLAN SORT (JOIN (B ATBH NATURAL,B ATBD INDEX (IDX_ATBD))
> PLAN JOIN (B ATBH NATURAL,B ATBD INDEX (IDX_ATBD)))
> PLAN JOIN (A ATBH NATURAL,A ATBD INDEX (IDX_ATBD))
> PLAN JOIN (A ATBH NATURAL,A ATBD INDEX (IDX_ATBD))
>
> Enchanced Info:
> +--------------------------+-----------+-----------+
> | Table Name | Index | Non-Index |
> | | reads | reads |
> +--------------------------+-----------+-----------+
> | RDB$FIELDS| 6 | 0 |
> | RDB$RELATION_FIELDS| 6 | 0 |
> | RDB$RELATIONS| 2 | 0 |
> | ATBD| 1,349,952 | 0 |
> | ATBH| 0 | 1,010,567 |
> +--------------------------+-----------+-----------+
>
>
> Here's the performance analysis for the same query with COALESCE:
> --Query 3
> SELECT
> ID,
> CODE,
> DT,
> STATUS,
> FK_ITM,
> COALESCE(
> (
> SELECT SUM(B.QTY) FROM CVW B
> WHERE B.FK_ITM = A.FK_ITM
> GROUP BY B.FK_ITM
> ),0) QTY
> FROM CVW A
>
> Query Time
> ------------------------------------------------
> Prepare : 0
> Execute : 62,269
> Avg fetch time: 1,945.91 ms
>
> Memory
> ------------------------------------------------
> Current: 1,546,272
> Max : 1,623,328
> Buffers: 5,000
>
> Operations
> ------------------------------------------------
> Read : 0
> Writes : 4
> Fetches: 1.227351E7
>
> Plan:
> ------------------------------------------------
> PLAN SORT (JOIN (B ATBH NATURAL,B ATBD INDEX (IDX_ATBD))
> PLAN JOIN (B ATBH NATURAL,B ATBD INDEX (IDX_ATBD)))
> PLAN SORT (JOIN (B ATBH NATURAL,B ATBD INDEX (IDX_ATBD))
> PLAN JOIN (B ATBH NATURAL,B ATBD INDEX (IDX_ATBD)))
> PLAN JOIN (A ATBH NATURAL,A ATBD INDEX (IDX_ATBD))
> PLAN JOIN (A ATBH NATURAL,A ATBD INDEX (IDX_ATBD))
>
> Enchanced Info:
> +--------------------------+-----------+-----------+
> | Table Name | Index | Non-Index |
> | | reads | reads |
> +--------------------------+-----------+-----------+
> | RDB$FIELDS| 6 | 0 |
> | RDB$RELATION_FIELDS| 6 | 0 |
> | RDB$RELATIONS| 2 | 0 |
> | ATBD| 2,699,192 | 0 |
> | ATBH| 0 | 2,020,183 |
> +--------------------------+-----------+-----------+
>
>
> And, when using the aggregate SUM, you can safely disregard using
> COALESCE, since SUM should never return NULL, at the least it should
> return a value of zero. The same applies to the aggregates COUNT and
> AVG.
>
> BTW, these performance numbers were taken with QuickDesk.
>
> --
> Best regards,
> Daniel Rail
> Senior System Engineer
> ACCRA Group Inc. (www.accra.ca)
> ACCRA Med Software Inc. (www.filopto.com)