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

> <SL> I'm not at my workstation but I'm 99% sure that you don't need the SELECT ... FROM RDB$Database, you should be able to use RDB$Get_Context directly, as in:
>
> where (r."Id" is null
> or u.usercs_name= rdb$get_context('USER_SESSION', 'TMUser'))
>
> <SL> It's a small thing, but every little bit helps...

Yes, thank you