I have many queries that are showing huge differences in how the same
query is exectued between the same version but running as embedded
and as superserver.

I was using 1.5.2 super server and finding certain select queries
were very slow and showed very weird stats such as a huge number of
non-indexed reads even though the columns in question are indexed.

I read that this was an issue that was resolved in v2 and backported
to 1.5.3 so I tried it out with the embedded server 1.5.3 and sure
enough there is an incredible positive difference with 1.5.3.

I upgraded our superserver and was surprised to still see the old
performance and non-indexed read problem. I've backed up and
restored the database just to ensure the indexes are refreshed etc,
no difference.

I took one representative but relatively smaller query as a test and
here are the critical stats using EMS IBManager on the same exact
query between 1.5.3 superserver and 1.5.3 running embedded:

**** Superserver 1.5.3 AND 1.5.2 (same)****
Operations
------------------------------------------------
Read : 0
Writes : 28
Fetches: 1,116,485
Plan:
------------------------------------------------
PLAN JOIN (AWORKORDERITEMSCHEDULEDUSER NATURAL,JOIN (JOIN (JOIN (JOIN
(JOIN (JOIN (JOIN (JOIN (AWORKORDERSERVICE NATURAL,AWORKORDER INDEX
(PK__AWORKORDER__587208C1)),AWORKORDERITEM INDEX
(FK_AWORKORDERITEM_AWORKORDER)),JOIN (AUNIT NATURAL,JOIN (JOIN
(AUNITMODEL NATURAL,AVENDOR INDEX
(PK__AVENDOR__577DE488)),AUNITMODELCATEGORY INDEX
(PK__AUNITMODELCATEGO__4EE89E87)))),AWORKORDERCATEGORY INDEX
(PK__AWORKORDERCATEGO__59662CFA)),APROJECT INDEX
(PK__APROJECT__39ED81A1)),JOIN (ACLIENT NATURAL,ADISPATCHZONE INDEX
(PK__ADISPATCHZONE__2C938683))),APRIORITY INDEX
(PK__APRIORITY__38F95D68)),AWORKORDERSTATUS INDEX
(PK__AWORKORDERSTATUS__66C02818)))



*** Embedded 1.5.3 ****
Operations
------------------------------------------------
Read : 33
Writes : 29
Fetches: 2,263
Plan:
------------------------------------------------
PLAN JOIN (AWORKORDERITEMSCHEDULEDUSER NATURAL,JOIN (JOIN (JOIN (JOIN
(JOIN (JOIN (JOIN (JOIN (AWORKORDERSERVICE NATURAL,AWORKORDER INDEX
(PK__AWORKORDER__587208C1)),AWORKORDERITEM INDEX
(FK_AWORKORDERITEM_AWORKORDER)),JOIN (AUNIT NATURAL,JOIN (JOIN
(AUNITMODEL NATURAL,AVENDOR INDEX
(PK__AVENDOR__577DE488)),AUNITMODELCATEGORY INDEX
(PK__AUNITMODELCATEGO__4EE89E87)))),AWORKORDERCATEGORY INDEX
(PK__AWORKORDERCATEGO__59662CFA)),APROJECT INDEX
(PK__APROJECT__39ED81A1)),JOIN (ACLIENT NATURAL,ADISPATCHZONE INDEX
(PK__ADISPATCHZONE__2C938683))),APRIORITY INDEX
(PK__APRIORITY__38F95D68)),AWORKORDERSTATUS INDEX
(PK__AWORKORDERSTATUS__66C02818)))


The execution plans look identical unless I'm missing something
reading it, the difference seems to be in how the query is actually
processed by the query engine.

Is this a bug or an inherent difference that I should expect with
superserver vs embedded or....?

- John