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>