At 12:26 PM 01-02-02 +0000, zinfabbe wrote:

>Hi,
>
>I've a table with all zipcodes and the name of the place. (primary
>key on zipcode)
>
>I've a persons table which contains the a field with the zipcode.
>(foreign key on zipcode)
>
>I've a view which joins both tables. This works fine. -> VIEW_PERSONS
>
>Now, I've another table which contains appointments of the persons.
>(which has field of personid)
>(contains foreign key to personId)
>
>I've a view which joins the appointments with the persons table. This
>works fine.
>
>But, I want to have the citynames as well. So I made a join of the
>appointments with the VIEW_Persons view. This takes ages... I guess
>it's normal that it takes longer, but this takes more than 10
>minutes, while the join with the Persons table takes a few seconds...
>
>What am I doing wrong? Database design? More indexes?

You have a view joining Persons and Zipcodes
then you join that view back to a dependent of the Persons table
and join again into the Zipcodes table...

I should think the optimizer gave up and went to the pub.

Is there any reason why your view can't join all three tables, or indeed why you can't just get this dataset with a plain SQL statement, something like

select p.personid, a.something, ... z.cityname, z.zipcode
from appointments a
join persons p on a.personid = p.personid
join zipcodes z on p.zipcode=z.zipcode
where <identified column> = ....

H.


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________