Hello Aage

Here is the updated query...

<- START CODE ->

select PROPERTIES.AGENT_REFERENCE,
OWNERS.SURNAME as OWNER_SURNAME, OWNERS.INITIALS as
OWNER_INITIALS, OWNERS.NAME as OWNER_NAME, OWNERS.COMPANY_NAME as
OWNER_COMPANY_NAME,
BUYERS.SURNAME as BUYER_SURNAME, BUYERS.INITIALS as
BUYER_INITIALS, BUYERS.NAME as BUYER_NAME, BUYERS.COMPANY_NAME as
BUYER_COMPANY_NAME,
PROPERTIES.ADDRESS_ROAD,
'xxxxx' as OUR_REFERENCE /*Calculated via another process*/,
INSPECTIONS.DATE_INSPECTED,
COMPANIES.ELECTRICAL_INSPECTION_COMM as COMMISSION
from INSPECTIONS, PROPERTIES,
PROPERTY_OWNERS
left join OWNERS on (OWNERS.ENTITY_ID = PROPERTY_OWNERS.ENTITY_ID),
PROPERTY_BUYERS
left join BUYERS on (BUYERS.ENTITY_ID = PROPERTY_BUYERS.ENTITY_ID),
PROPERTY_AGENTS
left join AGENTS on (AGENTS.ENTITY_ID = PROPERTY_AGENTS.ENTITY_ID)
left join COMPANIES on (COMPANIES.ID = AGENTS.COMPANY_ID)
where INSPECTIONS.INSPECTION_ID = 23 and
INSPECTIONS.SERVICE_TYPE = 1 and
PROPERTIES.ID = INSPECTIONS.PROPERTY_ID and
PROPERTY_OWNERS.PROPERTY_ID = PROPERTIES.ID and
PROPERTY_BUYERS.PROPERTY_ID = PROPERTIES.ID and
PROPERTY_AGENTS.PROPERTY_ID = PROPERTIES.ID

<- END CODE ->

...and here is the query plan...

<- START CODE ->

Plan
PLAN JOIN (MERGE (SORT (JOIN (JOIN (PROPERTY_AGENTS INDEX
(PROPERTY_AGENTS_IDX1),JOIN (JOIN (JOIN (JOIN (AGENTS ENTITIES INDEX
(RDB$PRIMARY1),AGENTS ENTITY_TYPES INDEX (RDB$PRIMARY2)),AGENTS PERSONS
INDEX (RDB$PRIMARY4)),AGENTS COMPANIES INDEX (RDB$PRIMARY5)),AGENTS
ENTITY_ROLES INDEX (RDB$PRIMARY3))),COMPANIES INDEX
(RDB$PRIMARY5))),SORT (JOIN (PROPERTY_BUYERS INDEX
(PROPERTY_BUYERS_IDX1),JOIN (JOIN (JOIN (JOIN (BUYERS ENTITIES INDEX
(RDB$PRIMARY1),BUYERS ENTITY_TYPES INDEX (RDB$PRIMARY2)),BUYERS PERSONS
INDEX (RDB$PRIMARY4)),BUYERS COMPANIES INDEX (RDB$PRIMARY5)),BUYERS
ENTITY_ROLES INDEX (RDB$PRIMARY3)))),SORT (JOIN (PROPERTY_OWNERS
NATURAL,JOIN (JOIN (JOIN (JOIN (OWNERS ENTITIES INDEX
(RDB$PRIMARY1),OWNERS ENTITY_TYPES INDEX (RDB$PRIMARY2)),OWNERS PERSONS
INDEX (RDB$PRIMARY4)),OWNERS COMPANIES INDEX (RDB$PRIMARY5)),OWNERS
ENTITY_ROLES INDEX (RDB$PRIMARY3))))),JOIN (INSPECTIONS INDEX
(INSPECTIONS_IDX2),PROPERTIES INDEX (FK_PROPERTIES)))

Adapted Plan
PLAN JOIN (MERGE (SORT (JOIN (JOIN (PROPERTY_AGENTS INDEX
(PROPERTY_AGENTS_IDX1),JOIN (JOIN (JOIN (JOIN (AGENTS ENTITIES INDEX
(FK_ENTITIES),AGENTS ENTITY_TYPES INDEX (FK_ENTITY_TYPES)),AGENTS
PERSONS INDEX (FK_PERSONS)),AGENTS COMPANIES INDEX
(FK_COMPANIES)),AGENTS ENTITY_ROLES INDEX (FK_ENTITY_ROLES))),COMPANIES
INDEX (FK_COMPANIES))),SORT (JOIN (PROPERTY_BUYERS INDEX
(PROPERTY_BUYERS_IDX1),JOIN (JOIN (JOIN (JOIN (BUYERS ENTITIES INDEX
(FK_ENTITIES),BUYERS ENTITY_TYPES INDEX (FK_ENTITY_TYPES)),BUYERS
PERSONS INDEX (FK_PERSONS)),BUYERS COMPANIES INDEX
(FK_COMPANIES)),BUYERS ENTITY_ROLES INDEX (FK_ENTITY_ROLES)))),SORT
(JOIN (PROPERTY_OWNERS NATURAL,JOIN (JOIN (JOIN (JOIN (OWNERS ENTITIES
INDEX (FK_ENTITIES),OWNERS ENTITY_TYPES INDEX (FK_ENTITY_TYPES)),OWNERS
PERSONS INDEX (FK_PERSONS)),OWNERS COMPANIES INDEX
(FK_COMPANIES)),OWNERS ENTITY_ROLES INDEX (FK_ENTITY_ROLES))))),JOIN
(INSPECTIONS INDEX (INSPECTIONS_IDX2),PROPERTIES INDEX (FK_PROPERTIES)))

<- END CODE ->

-----Original Message-----
From: Aage Johansen [mailto:aagjohan@...]
Sent: 08 April 2004 20:26
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] FireBird : Table Setup, Views and Slow
Queries

On Thu, 8 Apr 2004 17:38:17 +0000 (UTC), Werner Cloete wrote:

>
> Please excuse my ignorance...but do you mean that I should join the
> views into the query..? I'll have a look at that this evening...
>
> I will also try to get the plan for you...but, as mentioned in my
> previous post, it brings my computer to a near-halt for a _very_ long
> time...
>


You will only have to _prepare_ the query to get at the plan.
What tools do you use? It's quite easy to paste a query into the cursor

pane of IB_SQL and click the prepare button - the plan will then appear
in
the lower part.
The plan might suggest where indexes will speed up the query.


About the joins:
> select ...
> from INSPECTIONS, PROPERTIES,
> PROPERTY_OWNERS, OWNERS,
> PROPERTY_BUYERS, BUYERS,
> PROPERTY_AGENTS, AGENTS,
> COMPANIES
> where INSPECTIONS.INSPECTION_ID = 23 and
> PROPERTIES.ID = INSPECTIONS.PROPERTY_ID and
> PROPERTY_OWNERS.PROPERTY_ID = PROPERTIES.ID and
> OWNERS.ENTITY_ID = PROPERTY_OWNERS.ENTITY_ID and
> PROPERTY_BUYERS.PROPERTY_ID = PROPERTIES.ID and
> BUYERS.ENTITY_ID = PROPERTY_BUYERS.ENTITY_ID and
> PROPERTY_AGENTS.PROPERTY_ID = PROPERTIES.ID and
> AGENTS.ENTITY_ID = PROPERTY_AGENTS.ENTITY_ID and
> COMPANIES.ID = AGENTS.COMPANY_ID
This is were I suggested to use explicit joins, and not having join
criteria (implicit) in the where clause.

Something like (as you did in the view definition):
-------
select ...
from INSPECTIONS join PROPERTIES on PROPERTIES.ID =
INSPECTIONS.PROPERTY_ID
...
where INSPECTIONS.INSPECTION_ID = 23
and ...
-------
Don't know whether it helps, but it can make for easier understanding.


--
Aage J.





Yahoo! Groups Links





This e-mail message is confidential and intended solely for the person to whom or the entity to which it is addressed. All the contents and any attachments remain the property of VR Services (Pty) Ltd unless so stated by contract.
If you are not the intended recipient, you are prohibited from reading, copying, using or disclosing this message to others.
If you received this message in error, please notify the sender immediately by replying to this e-mail or by telephoning +27 21 430 9300 and thereafter delete the message. VR Services (Pty) Ltd does not accept liability for any personal views expressed in this message.