Hi...

I sent out the message, below, earlier today...but I have not seen it
on the list yet...and it has been more than an hour...

Does anyone know how long it takes for a sent message to be replicated
by the list server..?

Either way, if you already received the message, below, please ignore
this message - especially if you already replied to it...

Yours sincerely

Werner

-----Original Message-----
From: Werner Cloete
Sent: 08 April 2004 09:25
To: 'firebird-support@yahoogroups.com'
Subject: FireBird : Table Setup, Views and Slow Queries



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

I have the following table setups (Irrelevant rows have been removed)
:

<-- START CODE -->

CREATE TABLE COMPANIES (
ID INTEGER NOT NULL, /*Primary Key*/
...
COMPANY_NAME VARCHAR(200) NOT NULL, /*Indexed for
Searches*/
...
ELECTRICAL_INSPECTION_COMM FLOAT
);

CREATE TABLE PERSONS (
ID INTEGER NOT NULL, /*Primary Key*/
...
INITIALS VARCHAR(10) NOT NULL,
NAME VARCHAR(50),
SURNAME VARCHAR(100) NOT NULL, /*Indexed for
Searches*/
...
);

CREATE TABLE ENTITIES ( /*This table brings together all the
Companies and Persons who, at the time, have roles assigned to them*/
ID INTEGER NOT NULL, /*Primary Key*/
"TYPE" INTEGER NOT NULL,
"ROLE" INTEGER NOT NULL,
COMPANY INTEGER, /*Indexed for Searches*/
PERSON INTEGER /*Indexed for Searches*/
);

<-- END CODE -->

I have 7 views, all with the same code as below, for the 7 roles in my
system :

<-- START CODE -->

CREATE VIEW AGENTS(
ENTITY_ID,
"TYPE",
PERSON_ID,
SURNAME,
INITIALS,
NAME,
COMPANY_ID,
COMPANY_NAME)
AS
select ENTITIES.ID as ENTITY_ID, ENTITY_TYPES.DESCRIPTION as "TYPE",
PERSONS.ID as PERSON_ID, PERSONS.SURNAME, PERSONS.INITIALS,
PERSONS.NAME,
COMPANIES.ID as COMPANY_ID, COMPANIES.COMPANY_NAME
from ENTITIES
left join ENTITY_TYPES on ENTITY_TYPES.ID = ENTITIES."TYPE"
left join PERSONS on PERSONS.ID = ENTITIES.PERSON
left join COMPANIES on COMPANIES.ID = ENTITIES.COMPANY
join ENTITY_ROLES on ENTITY_ROLES.ID = ENTITIES."ROLE" and
ENTITY_ROLES.DESCRIPTION = 'Agent'; /*The other views
just have another role descriptions here*/

<-- END CODE -->

The other tables, not listed here, are small lookup tables that are
used to supply type ('Company', 'Person') information, as well as role
('Agent', 'Attorney', etc.) information...

I have a major (report) query that makes use of a couple of my views
(the one below only runs for 1 inspection) :

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

<-- END CODE -->

Other information that may be required :

1) In the view, the ENTITY_TYPE and ENTITY_ROLE information must be
present.
2) In the view, either the Company or the Person information must be
present, depending on which entity has the role assigned.
3) The system allows for both Companies and Persons to perform any of
the 7 roles in the system...
4) A single Company/Person may perform more than one role in the
system...
5) PROPERTY_OWNERS (and the other, similarly named, tables) all have
a "PROPERTY_ID" and a "ENTITY_ID" field...
6) The system allows for a property to have more than one owner, etc.
over a period of time...
7) The PROPERTY_OWNERS (and the other, similarly name, tables) do not
have any indexes on them at this time...

My questions are as follows :

1) How can I improve my views' queries to be optimal..? At this
time, I can't change the table structures...so how can I make my views
faster..?
2) How can I improve my (long) query to be optimal..? If it is not
possible, I will have to run everything in smaller batches...
3) Do you have any other suggestions that would help to make my
database access quicker..?

I really appreciate your help with this...

Yours sincerely

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