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.