Hi all,

I have a pretty complicated SELECT statement which behaves somewhat
odd with respect to performance.

The following statement
-------------------------
SELECT
Sample.SampleNo,
SampleType.sampleTypeNo,
AssayCategory.name,
Assay.assayNo,
Assay.name,
AssayCategory.displayPos,
ProfileAssay.displayPos,
Result.resultID,
Result.val,
Result.displayVal,
Unit.unitNo,
Result.unitID,
Assay.assayID,
SampleType.sampleTypeID,
Test.instrumentID
FROM
Profile
JOIN ProfileAssay ON ProfileAssay.ProfileID=profile.profileID
LEFT OUTER JOIN AssayCategory ON
AssayCategory.assayCategoryID=ProfileAssay.assayCategoryID
JOIN Assay ON ProfileAssay.assayID=Assay.assayID
LEFT OUTER JOIN SampleType ON
ProfileAssay.sampleTypeID=sampleType.sampleTypeID
LEFT OUTER JOIN
(
SampleGroup
JOIN Sample ON SampleGroup.sampleGroupID=Sample.sampleGroupID AND
SampleGroup.sampleGroupID=18077
LEFT OUTER JOIN SampleOrder ON SampleOrder.sampleID=Sample.sampleID
LEFT OUTER JOIN TestRequest ON
TestRequest.sampleorderID=SampleOrder.sampleOrderID
LEFT OUTER JOIN Test ON TestRequest.testID=Test.testID
LEFT OUTER JOIN Result ON
Result.testRequestID=TestRequest.testRequestID
LEFT OUTER JOIN Unit ON Result.unitID=Unit.unitID
)
ON SampleOrder.assayID=ProfileAssay.assayID AND
Sample.sampleTypeID=ProfileAssay.sampleTypeID
WHERE
Profile.profileNo='CSF'
----------------------
executed with IBConsole takes 0.0290 seconds to return 386 rows.
Fine!

Now replace the field list with
------------------------
SELECT
count(*)
FROM
...
------------------------
This execution time is ... 5.0278 seconds. This is more than 150
times slower!!!

Also, (keep the field list) add another condition to the WHERE, more
rows are filtered out:
-----------------------------
SELECT
...
WHERE
...
AND
AssayCategory.name='Proteins'
-------------------------------------
which takes 5.0288 seconds to return 12 rows!

This is in contrast to everything I know about client server
optimization and SQL performance. Adding another condition to the
where should at worst result in the same exec time, never be slower.
Counting rows should be faster than actually fetching them.

The exec plan for the queries is always the same
-----------------------------
PLAN JOIN (JOIN (MERGE (SORT (ASSAY NATURAL),SORT (JOIN (JOIN
(PROFILE INDEX (XAK1PROFILE),PROFILEASSAY INDEX
(XAK1PROFILEASSAY)),ASSAYCATEGORY INDEX (RDB$PRIMARY2)))),SAMPLETYPE
INDEX (RDB$PRIMARY23)),JOIN (JOIN (JOIN (JOIN (JOIN (JOIN
(SAMPLEGROUP INDEX (RDB$PRIMARY20),SAMPLE INDEX
(RDB$FOREIGN47)),SAMPLEORDER INDEX (XAK1SAMPLEORDER)),TESTREQUEST
INDEX (XIE1TESTREQUEST)),TEST INDEX (RDB$PRIMARY24)),RESULT INDEX
(XAK1RESULT)),UNIT INDEX (RDB$PRIMARY26)))
-----------------------------

I really have no idea what's wrong here. Does anybody have any hints?

Mirco