Hi again, Guido!

Seems like Fb 2.0 thinks it is better off to consider TURNOVER_PC before
CUSTOMERS. First, try changing to

select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG
where BLCSSALESREPID=56 and BLCSGPID3=65 and
not exists (select * from TURNOVER_PC_CS join CUSTOMERS on
TO6CSID=CSID where CSUSID1=56 and CSGPID3=65 and BACKLOG.BLPCID=CSID)
group by BLPCID

I hope this doesn't change the plan at all (hopefully, Firebird consider
the queries identical), but I'd like to know whether it does, so please
report back the result.

Then add +0 to see if that fixes the plan (I think it should):

select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG
where BLCSSALESREPID=56 and BLCSGPID3=65 and
not exists (select * from TURNOVER_PC_CS join CUSTOMERS on
TO6CSID=CSID+0 where CSUSID1=56 and CSGPID3=65 and BACKLOG.BLPCID=CSID)
group by BLPCID

I wouldn't bother about the change in the plan for BACKLOG, I think that
is due to Fb 2.0 telling us more about what happens in the background,
something that Fb 1.5 just kept as a secret.

By the way, do you have several indexes that starts with the fields
TURNOVER_PC_CS.TO6CSID or CUSTOMERS.CSID? If so, maybe that could
confuse the optimizer?

HTH,
Set

Guido Klapperich wrote:

> I have two identical databases, one runs under FB 1.5.2 and one under FB
> 2.0. I have the following query
>
> select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
> sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG
> where BLCSSALESREPID=56 and BLCSGPID3=65 and
> not exists (select TO6PCID from TURNOVER_PC_CS join CUSTOMERS on
> TO6CSID=CSID where CSUSID1=56 and CSGPID3=65 and BACKLOG.BLPCID=TO6PCID)
> group by BLPCID
>
> When I run it with FB 1.5.2, I get the plan
> PLAN JOIN (CUSTOMERS INDEX (CUSTOMERS_IDX1),TURNOVER_PC_CS INDEX
> (I_TO6ALL_A_U))
> PLAN (BACKLOG ORDER I_BLPCID_BLMCID_A)
>
> and the reads
> BACKLOG 216 Indexed Reads (IR)
> CUSTOMERS 2290 IR
> TURNOVER_PC_CS 214 IR
>
> When I run the query with FB 2.0, I get the plan
> PLAN JOIN (TURNOVER_PC_CS INDEX (I_TO6PCID_A), CUSTOMERS INDEX
> (RDB$PRIMARY26))
> PLAN (BACKLOG ORDER I_BLPCID_BLMCID_A INDEX
> (I_BLCSGPID3_BLPCPAID_BLPCMPID_A, BACKLOG_IDX1))
>
> and the reads
> BACKLOG 216 IR
> CUSTOMERS 11992 IR
> TURNOVER_PC_CS 11992 IR
>
> I'm a little bit surprised, that the query has a lot more reads with FB
> 2.0. Exists a solution for FB 2.0, so that query runs as fast as with FB
> 1.5.2?
>
> Regards
>
> Guido