Hello Alexandre

ThanQ for your very useful information... I tried it this past
weekend, at home, and found that it did run a lot quicker...

There was only one problem, though... I'm currently using IB Expert
and it complained that the cursor was not set on a row...(?)

I'll dig deeper, this evening, in the hopes of resolving this issue...

At this point in time, this is what my query looks like after the
changes (from memory, so this may be incorrect) :

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

I hope that I typed this out correctly...

If anyone have any further suggestions, it will be greatly
appreciated...

Yours sincerely

Werner

-----Original Message-----
From: Alexandre Benson Smith [mailto:iblist@...]
Sent: 08 April 2004 17:22
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] FireBird : Table Setup, Views and Slow
Queries


At 09:25 08/04/2004 +0200, you wrote:

> Hi...
>
> I require some help with a (very hectic..?) problem that I am
>currently trying to deal with... At this point in time, the query (at
>the bottom of this message) runs so long that I have to reboot my
>machine to get any processing power back to do my other work...
>Needless to say, my customers are not going to be impressed if they

have

>to wait ages to get their report - especially if it takes up all the
>possible resources, on the machine, to extract the reports...
>.... snip...

Werner,

Just a general rule that I have followed...

ALWAYS use SQL/92 join sintax:

select
T1.Field1, T1.Fields2, T2.Field3
from
Table1 T1 join
Table2 T2 on (T2.Field1 = T1.Field1)

instead of

select
T1.Field1, T1.Fields2, T2.Field3
from
Table1 T1, Table2 T2
where
T2.Field1 = T1.Field1

Another rule that I follow:

I always put the tables that will be linked with join first and then
after
all joinned tables I put the ones that will be outer joinned. This
increases the performance of some queries a LOT.

But to give you a more precise answer you need to send us the plan for
the
problematic queries.

See you !


Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.654 / Virus Database: 419 - Release Date: 06/04/2004


[Non-text portions of this message have been removed]




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.