--- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@...> wrote:

> <SL> The WHERE clause conditions point to the fact that the LEFT JOIN's should really be inner JOINs, so the correct SQL would be:
>
> select sup.*
> from "tSupplyGRP" sup
> join "vOwner" own on (own."Id" = sup."OwnerId")
> join "tOperationType" otp on (otp."Id" = sup."OpType")
> join "vPaymentType" ptp on (ptp."Id" = sup."PaymentTypeId")
> join "tPaymentTypeMain" tptm on (tptm."Id"=ptp."AccountMethod")
> join "vPartner" tp1 on (tp1."Id" = sup."PartnerId")
> join "vDocumentType" dtp on (dtp."Id" = sup."DokTypeId")
> join "vStorage" sto on (sto."Id" = sup."StorageId")
> left join "vPartner" tp2 on (tp2."Id" = sup."PartnerRecvGoods_Id")
>
> <SL> This will improve the View performance

Tested. I know it is not very logically, but this change increases the speed of view query from 0 to 80ms. The indexed reads are decreased from 721 to 600, but the non indexed reads increases from 1 to 27. I made a lot of tests when i was creating the view, this is the best way. In many cases Firebird handles better left joins (than inner), even it is strange.

<SL2> What is the PLAN which is generated by your revised view query?


<SL2> 80ms is not long, have you tested the 2 views from a fresh start?

It is possible that the performance numbers are being skewed by file/data caching...


<SL2> Have you tested "my view" with the original query?

It is possible that "my view" could have been slower on it's own but when used in the query, "my view" could be faster, by allowing the optimizer to consider the inner joins, instead of the left joins in your "faster" view.