---In firebird-support@yahoogroups.com, <svein.erling.tysvaer@...> wrote:

>I have a simple query:
>
>SELECT first 1 ITEM_DATE FROM HISTORY
>WHERE ITEM_ID = 'ITEM_ID_123456'
>and ITEM_TYPE = 'TYPE1'
>AND ITEM_STATUS > 200 and ITEM_STATUS <> 310
>ORDER BY ITEM_DATE ASC
>
>Which runs in about 1.5 seconds. If I remove the Order by it's less than 100ms. There are only three records in the result set.
>There is an ascending index on ITEM_DATE and the statistics are up to date. 
>
>Does anyone know why there would be such a difference in performance when using\not using the order by? What is the
>engine ordering? It can't be the three records to take 1.5 seconds, surely?

Probably the PLAN is different, I assume you have an index on ITEM_DATE and Firebird wrongly believes it would could speed up the query by using it (it doesn't know that only three rows will be returned, I guess ITEM_ID isn't a very selective field). Change your query to:

SELECT first 1 ITEM_DATE+0 FROM HISTORY
WHERE ITEM_ID = 'ITEM_ID_123456'
and ITEM_TYPE = 'TYPE1'
AND ITEM_STATUS > 200 and ITEM_STATUS <> 310
ORDER BY 1 ASC

and the speed should be closer to 100ms again.

HTH,
Set