Hello Aage

ThanQ for your help so far...

I use IB Expert at the moment... I clicked on the [Prepare] button
and it gave me a block on information...

I did not have time to send it to work, though, so that does not help
my case much...

I did, however, update my query as Alexandre suggested...and it
already seems better...

I will get the query plan, somehow, from home today and post it
here...

Yours sincerely

Werner

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