Slow join on otherwise fast views?

If

select * from vw_fielddata

takes 16ms to return just four rows, why does

select
*
from
vw_fielddata firstname
join
vw_fielddata secondname
on
secondname.AccountID = firstname.AccountID

take over an hour? The views themselves contain joins on tables and further
views, and although AccountID isn't a primary key from the tables it's
gathered from it is a foreign key in them (I assume this makes it indexed?).

Suggestions for diagnosing/optimising? The tables are tiny and the order of
time taken so large, so I don't believe it's an optimisation issue.

Shak

"Shak" <Shak@shak.com> wrote in message
news:g4nu4c$4pq$1@registered.motzarella.org...
> If
>
> select * from vw_fielddata
>
> takes 16ms to return just four rows, why does
>
> select
> *
> from
> vw_fielddata firstname
> join
> vw_fielddata secondname
> on
> secondname.AccountID = firstname.AccountID
>
> take over an hour? The views themselves contain joins on tables and
> further views, and although AccountID isn't a primary key from the tables
> it's gathered from it is a foreign key in them (I assume this makes it
> indexed?).
>
> Suggestions for diagnosing/optimising? The tables are tiny and the order
> of time taken so large, so I don't believe it's an optimisation issue.
>

It seems that my query is stuck in the "statistics" state. Reading up, it
seems that the optimiser may be looking for the best way to do the many
joins within the views. Is there any way of forcing it to just pick one
early?

Shak

On Jul 5, 8:58 am, "Shak" <S...@shak.com> wrote:
> "Shak" <S...@shak.com> wrote in message
>
> news:g4nu4c$4pq$1@registered.motzarella.org...
>
>
>
> > If
>
> > select * from vw_fielddata
>
> > takes 16ms to return just four rows, why does
>
> > select
> > *
> > from
> > vw_fielddata firstname
> > join
> > vw_fielddata secondname
> > on
> > secondname.AccountID = firstname.AccountID
>
> > take over an hour? The views themselves contain joins on tables and
> > further views, and although AccountID isn't a primary key from the tables
> > it's gathered from it is a foreign key in them (I assume this makes it
> > indexed?).
>
> > Suggestions for diagnosing/optimising? The tables are tiny and the order
> > of time taken so large, so I don't believe it's an optimisation issue.
>
> It seems that my query is stuck in the "statistics" state. Reading up, it
> seems that the optimiser may be looking for the best way to do the many
> joins within the views. Is there any way of forcing it to just pick one
> early?
>
> Shak

Try to redesign your queries to skip the views, and go straight to the
base tables. Perhaps you can avoid most of the joins that way, by not
trying to retrieve data in tables that you don't need.

"ThanksButNo" <no.no.thanks@gmail.com> wrote in message
news:f3593dce-4294-4cd3-9aa7-9f88dd90424a@t54g2000hsg.googlegroups.com...
> On Jul 5, 8:58 am, "Shak" <S...@shak.com> wrote:
>> "Shak" <S...@shak.com> wrote in message
>>
>> news:g4nu4c$4pq$1@registered.motzarella.org...
>>
>>
>>
>> > If
>>
>> > select * from vw_fielddata
>>
>> > takes 16ms to return just four rows, why does
>>
>> > select
>> > *
>> > from
>> > vw_fielddata firstname
>> > join
>> > vw_fielddata secondname
>> > on
>> > secondname.AccountID = firstname.AccountID
>>
>> > take over an hour? The views themselves contain joins on tables and
>> > further views, and although AccountID isn't a primary key from the
>> > tables
>> > it's gathered from it is a foreign key in them (I assume this makes it
>> > indexed?).
>>
>> > Suggestions for diagnosing/optimising? The tables are tiny and the
>> > order
>> > of time taken so large, so I don't believe it's an optimisation issue.
>>
>> It seems that my query is stuck in the "statistics" state. Reading up, it
>> seems that the optimiser may be looking for the best way to do the many
>> joins within the views. Is there any way of forcing it to just pick one
>> early?
>>
>> Shak
>
> Try to redesign your queries to skip the views, and go straight to the
> base tables. Perhaps you can avoid most of the joins that way, by not
> trying to retrieve data in tables that you don't need.


But I'm trying to stick to a particular design. I've figured out how to
adjust the optimiser though - setting OPTIMIZER_SEARCH_DEPTH to something a
bit less insane than its default of 62 helped, and the query is now pretty
much instant.

Shak