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

>
> I've been staring at this problem for some time, but can't seem to
> make any progress. I'm using FB2. I have this select:
>
> Select S1."Acc ID", S1."Serv ID", S1."Last Performed", X.LP FROM
> sched s1
> inner join
> SCHED S on S."Acc ID" = S1."Acc ID"
> and S."Serv ID" = S1."Serv ID"
> inner join
> (select A."Acc ID" as ACCID, A."Serv ID" as SERVID,
> max(A."Date") as LP
> from artrans a
> where A."Code" in (0, 1, 2, 10)
> group by A."Acc ID", A."Serv ID") X
> on S."Acc ID" = X.ACCID and S."Serv ID" = X.SERVID
> where S."Last Performed" <> X.LP
>
> What I really would like to do is this:
>
> update SCHED Set "Last Performed" = (X.LP from the select above)
>
> I tried this:
>
> update sched s1 set s1."Last Performed" = (select X.LP
> from SCHED S
> inner join
> (select A."Acc ID" as ACCID, A."Serv ID" as SERVID,
> max(A."Date") as LP
> from artrans a
> where A."Code" in (0, 1, 2, 10)
> group by A."Acc ID", A."Serv ID") X
> on S."Acc ID" = X.ACCID and S."Serv ID" = X.SERVID
> where S."Last Performed" <> X.LP
> and S."Acc ID" = S1."Acc ID"
> and S."Serv ID" = S1."Serv ID" )
>
> But it doesn't seem to do what I want because it updates all of

the

> rows, in apparent violation of the condition S."Last Performed" <>
> X.LP which is, at this instant, always false, so I'd expect it to
> update 0 rows until I change something.
>
> In addition, while both SCHED and ARTRANS are smallish (5000 rows
> and 60,000 rows respectively), the original SELECT takes a second

to

> run, so I'm thinking it's going to take 60,000 seconds to do this
> update even if it was working, which is clearly too long.
>
> In that regard, ARTRANS has an index on "Acc ID" (the first part

of

> the PK), but none on "Serv ID". The plan is this:
>
> PLAN JOIN (SORT (X A NATURAL), S INDEX (PK_SCHED))
> PLAN (S1 NATURAL)
>
> So I guess PK_ARTRANS isn't useful here.
>
>
> Any advice?
>
> Michael D. Spence
> Mockingbird Data Systems, Inc.
>

I added a view


CREATE VIEW FOO (ACCID, SERVID, LP)AS
Select S."Acc ID", S."Serv ID", X.LP FROM sched s
inner join
(select A."Acc ID" as ACCID, A."Serv ID" as SERVID,
max(A."Date") as LP
from artrans a
where A."Code" in (0, 1, 2, 10)
group by A."Acc ID", A."Serv ID") X
on S."Acc ID" = X.ACCID and S."Serv ID" = X.SERVID
;




and another index, so now I have this:

update sched s set s."Last Performed" = (select f.LP
from foo f
where s."Acc ID" = f.accid and s."Serv ID" = f.SERVID)

And a plan like this:

PLAN JOIN (SORT (F X A INDEX (Cust_Artrans, SERVID_Atrans)), F S
INDEX (PK_SCHED))
PLAN (S NATURAL)

Which executes in 4 seconds. Evidently, without the view the
optimizer won't use the additional indices.

Also, I gave up on trying to skip the update if "Last Performed" is
already correct because I think it's probably a waste of time, i.e.,
the server's already had to fetch everything in order to make the
decision.

So my questions are now these:

1) Is there a way I can avoid creating the View and still use the
indices.
2) Is it safe to drop and create Views or are they subject to the
same Table ID restrictions as a real table?

Michael D. Spence
Mockingbird Data Systems, Inc.