This is the general sql for vSupplyGRP:

select sup.*
from "tSupplyGRP" sup
join "vOwner" own on (own."Id" = sup."OwnerId")
join "tOperationType" otp on (otp."Id" = sup."OpType")
join "vPaymentType" ptp join "tPaymentTypeMain" tptm on tptm."Id"=ptp."AccountMethod" on (ptp."Id" = sup."PaymentTypeId")
left join "vPartner" tp1 on (tp1."Id" = sup."PartnerId")
left join "vPartner" tp2 on (tp2."Id" = sup."PartnerRecvGoods_Id")
left join "vDocumentType" dtp on (dtp."Id" = sup."DokTypeId")
left join "vStorage" sto on (sto."Id" = sup."StorageId")
where
tp1."Id" is not null and
dtp."Id" is not null and
sto."Id" is not null
;

<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


only vParner has many records many (1000-5000) as resultset. the others result in in 2 - 20 records.

All linked views are carrying for the user rights at "record level". Here is the sample for vDocumentType, the others are based on the same logic:

select t.*
from "tDocumentType" t
left join "tDocTypeUserRights" r
join ucs_users u on u.user_id=r."UserId"
on ( r."DokTypeId"=t."Id" )

where (r."Id" is null
or u.usercs_name=(select rdb$get_context('USER_SESSION', 'TMUser') from rdb$database))
;

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

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Logically, your query is an inner join, not a left join. Using left join are sometimes useful as a last step to gain performance, but generally I always do such optimization after finishing all the other parts of the query.
>
> Your query (with left join) may be the best you can get, but without knowing the definition of your view, it is hard to guess. Can you post the definition of the view to this list? Also, please tell us if some of the tables involved are a lot bigger than the others and whether indexes used are selective or not.
>
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Kok_BG
> Sent: 4. september 2009 23:55
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: SQL question - getting "last" record
>
> --- In firebird-support@yahoogroups.com, "Ann W. Harrison" <aharrison@> wrote:
> >
> > Kok_BG wrote:
> > > select first 1 skip 0
> > >
> > > (tsd."Price" - (tsd."Price"* tsd."Discount"/100))
> > > from "tSupplyDET" tsd left join "vSupplyGRP" tsg
> > > on tsd."GRPId"=tsg."Id"
> > > left join "tOperationType" top
> > > on tsg."OpType"= top."Id"
> > > where tsd."ArticleId" = 31 and
> > > (top."Pay"='Y' or top."Profit"='Y')
> > > order by tsg."DocumentDate" desc, tsd."Id" desc
> > >
> > > As you see, i load all records, sort it by date and fetch only
> > > the first one, which seems very unfriendly for the server.
> > >
> > > Actually I need only some fields ("Price" and "Discount") from
> > > the "last" record, based on the criteria shown in the query.
> >
> > The first thing I'd do is change the "left join" to "join" or
> > "inner join." You don't want any records that don't have values
> > for tOperationType or vSupplyGRP. Outer joins are harder to
> > optimize and generally slower than inner joins.
> >
> >
> > You might also look at the definition of vSupplyGRP which is
> > likely to be a view (see rant below on overly structured naming
> > conventions) that has many more tables in it than you need for
> > this query. Here's a formatted (more or less) version of the
> > plan....
> >
> > PLAN (TSG TP1 RDB$DATABASE NATURAL)
> > PLAN (TSG TP2 RDB$DATABASE NATURAL)
> > PLAN (TSG DTP RDB$DATABASE NATURAL)
> > PLAN (TSG STO RDB$DATABASE NATURAL)
> >
> > PLAN SORT (
> > JOIN (TSD INDEX (INTEG_488),
> > JOIN (
> > JOIN (
> > JOIN (
> > JOIN (
> > JOIN (
> > JOIN (
> > JOIN (
> > TSG SUP INDEX (INTEG_387),
> > TSG PTP tPaymentType INDEX (PK_tPaymentType),
> > TSG OTP INDEX (INTEG_362),
> > TSG OWN tOwner INDEX (INTEG_363)),
> > TSG TPTM INDEX (PK_tPaymentTypeMain)),
> > JOIN (
> > TSG TP1 P INDEX (INTEG_365),
> > JOIN (
> > JOIN (
> > TSG TP1 K INDEX (PK_tKey),
> > TSG TP1 KU INDEX (FK_tKeyUser_Key)),
> > TSG TP1 U INDEX (UNQ1_UCS_USERS)))),
> > JOIN (
> > TSG TP2 P INDEX (INTEG_365),
> > JOIN (
> > JOIN (
> > TSG TP2 K INDEX (PK_tKey),
> > TSG TP2 KU INDEX (FK_tKeyUser_Key)),
> > TSG TP2 U INDEX (UNQ1_UCS_USERS)))),
> > JOIN (
> > TSG DTP T INDEX (INTEG_313),
> > JOIN (
> > TSG DTP R INDEX (FK_tDocTypeUserRightsDok),
> > TSG DTP U INDEX (UNQ1_UCS_USERS)))),
> > JOIN (
> > TSG STO T INDEX (INTEG_382),
> > JOIN (
> > TSG STO R INDEX (FK_tStorageUserRightsStor),
> > TSG STO U INDEX (UNQ1_UCS_USERS)))),
> > TOP INDEX (INTEG_362))))
> >
> >
> > It's amazing that the query ever finished, let alone got a correct
> > answer. If there's a simpler way to get the DocumentDate field,
> > that would save you a lot of time.
> >
> >
> > Good luck,
> >
> > Ann
> >
> > <rant>
> > One of the strengths of relational databases is their ability to
> > separate the logical schema from the physical storage. One example
> > of that is views, which can be substituted for tables so as design
> > requirements change, what had been a single table can be split or
> > what had been two tables can be merged without changing the applications
> > that use them. If you insist on naming your tables t<whatever> and
> > views v<whatever> you eliminate that flexibility for no gain at all,
> > except, as in this case, leaving a clue as to why one records source
> > seems so much slower than others.
> > </rant>
> >
>
> Thanks Ann,
> unfortunately vSupplyGRP cannot be skipped, as it is part of the application user rules. Making the join inner instead of left make the things worst. The reads are raising from 3000 to 70000.
>
> If there is no other way, i will redesign the application so the needed value will be written during insert/update in a new field.
> But i think that there should be simple solution like select max() or LAST() or TOP() or something else.
>




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links



m/info/terms/