--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

> Try replacing the following line:
>
> > INNER JOIN TITLE ON PRODUCT.TITLEID = TITLE.TITLEID
>
> With:
>
> INNER JOIN TITLE ON PRODUCT.TITLEID+0 = TITLE.TITLEID

i did that but it didn't make a difference. the performance analysis
(in ems sqlmanager) shows the following...

Query Time
------------------------------------------------
Prepare : 0
Execute : 46,265
Avg fetch time: 1,051.48 ms

Memory
------------------------------------------------
Current: 1,039,804
Max : 1,051,340
Buffers: 2,048

Operations
------------------------------------------------
Read : 229,497
Writes : 0
Fetches: 2,421,913

Plan:
------------------------------------------------
PLAN SORT (JOIN (JOIN (JOIN (PRODUCT NATURAL, TITLE INDEX (PK_TITLE)),
PRODUCT_PARTICIPANT INDEX (PK_PRODUCT_PARTICIPANT)), PARTICIPANT_NAME
INDEX (PK_PARTICIPANT_NAME)))

Enchanced Info:
| Table Name | Index reads | Non-Index reads |

+--------------------------+-------+-----------+
| RDB$FIELDS| 26 | 0 |
| RDB$RELATION_FIELDS| 26 | 0 |
| RDB$RELATIONS| 6 | 0 |
| PRODUCT| 0 | 112,933 |
| PRODUCT_PARTICIPANT| 112,9 | 0 |
| TITLE| 112,9 | 0 |
| PARTICIPANT_NAME| 112,9 | 0 |
+--------------------------+-------+-----------+

when i run the second query from my preivous message (the quick one)
there's 71 indexed reads for PRODUCT, PRODUCT_PARTICIPANT and
PARTICIPANT_NAME tables and 19 index reads for RDB$RELATION_FIELDS and
RDB$FIELDS.

any other ideas?

TIA