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)