Firebird Slow performance when ORDER BY

Question

I have a view called VW_PURCHASE_ORDER_LIST. It has about 200,000 records total.

It takes 16ms to run the query:

select first 128 * from VW_PURCHASE_ORDER_LIST 

However when i use the order by statement it takes much longer...about 9s

select first 128 * from VW_PURCHASE_ORDER_LIST
order by id asc

Plan
PLAN SORT (JOIN (VW_PURCHASE_ORDER_LIST LEVY BT NATURAL, VW_PURCHASE_ORDER_LIST M INDEX (PK_MATTER), VW_PURCHASE_ORDER_LIST LEVY F INDEX (PK_B_BUDGET_LEVY_FREQUENCY), VW_PURCHASE_ORDER_LIST BT_FHC INDEX (PK_BT_FINANCIAL_HEALTH_CHECK), VW_PURCHASE_ORDER_LIST BD INDEX (PK_BT_BUILDING_DETAILS), VW_PURCHASE_ORDER_LIST USR_STRATAMANAGER INDEX (PK_USER_DETAIL), VW_PURCHASE_ORDER_LIST POH INDEX (FK_PURCHASE_ORDER_HEADER_1), VW_PURCHASE_ORDER_LIST PS INDEX (PK_LK_PO_PAID_STATUS), VW_PURCHASE_ORDER_LIST POS INDEX (PK_LK_PURCHASE_ORDER_STATUS), VW_PURCHASE_ORDER_LIST LM INDEX (UNQ1_P_ORDER_MODIFIED_DATE), VW_PURCHASE_ORDER_LIST SUPPLIER INDEX (PK_CONTACT), VW_PURCHASE_ORDER_LIST USR_CREATED INDEX (PK_USER_DETAIL), VW_PURCHASE_ORDER_LIST USR_MODIFIED INDEX (PK_USER_DETAIL), VW_PURCHASE_ORDER_LIST USR_APPROVED INDEX (PK_USER_DETAIL)))

------ Performance info ------
Prepare time = 32ms
Execute time = 8s 80ms
Avg fetch time = 237.65 ms
Current memory = 98,671,784
Max memory = 99,258,368
Memory buffers = 2,048
Reads from disk to cache = 230,443
Writes from cache to disk = 0
Fetches from cache = 6,253,743

Is there a way to speed this up? The issue is that our database is growing in size and there are now many large tables. Our app used to load the whole table into a grid but because we have so much data now this is taking minutes to load and maxing out the client pc's RAM.

As a solution we are using DevExpress ServerMode XtraGrid. Loading and scrolling on the grid is slow because it is sending an initial select count(*) from x_table_or_view_name to get total records, then when scrolling it sends a select first x skip y from x_table_or_view_name order by id....it's this order by that slows the query right down and makes the grid unusable

I'm not sure where to go from here as I'm not a database expert, if anyone could assist with some suggestions it would be greatly appreciated.

Update with no order by:

select first 128 * from VW_PURCHASE_ORDER_LIST

Plan
PLAN JOIN (VW_PURCHASE_ORDER_LIST LEVY BT NATURAL, VW_PURCHASE_ORDER_LIST M INDEX (PK_MATTER), VW_PURCHASE_ORDER_LIST LEVY F INDEX (PK_B_BUDGET_LEVY_FREQUENCY), VW_PURCHASE_ORDER_LIST BT_FHC INDEX (PK_BT_FINANCIAL_HEALTH_CHECK), VW_PURCHASE_ORDER_LIST BD INDEX (PK_BT_BUILDING_DETAILS), VW_PURCHASE_ORDER_LIST USR_STRATAMANAGER INDEX (PK_USER_DETAIL), VW_PURCHASE_ORDER_LIST POH INDEX (FK_PURCHASE_ORDER_HEADER_1), VW_PURCHASE_ORDER_LIST PS INDEX (PK_LK_PO_PAID_STATUS), VW_PURCHASE_ORDER_LIST POS INDEX (PK_LK_PURCHASE_ORDER_STATUS), VW_PURCHASE_ORDER_LIST LM INDEX (UNQ1_P_ORDER_MODIFIED_DATE), VW_PURCHASE_ORDER_LIST SUPPLIER INDEX (PK_CONTACT), VW_PURCHASE_ORDER_LIST USR_CREATED INDEX (PK_USER_DETAIL), VW_PURCHASE_ORDER_LIST USR_MODIFIED INDEX (PK_USER_DETAIL), VW_PURCHASE_ORDER_LIST USR_APPROVED INDEX (PK_USER_DETAIL))

------ Performance info ------
Prepare time = 16ms
Execute time = 15ms
Avg fetch time = 0.44 ms
Current memory = 36,398,384
Max memory = 0
Memory buffers = 2,048
Reads from disk to cache = 172
Writes from cache to disk = 0
Fetches from cache = 2,654

This is the actual view below. I can post the DDL for the tables in the join if this helps?

CREATE VIEW VW_PURCHASE_ORDER_LIST(
    ID,
    ID_BUILDING,
    ID_SUPPLIER,
    ID_STATUS,
    ID_USER_CREATED,
    ID_USER_MODIFIED,
    DATE_ORDER,
    DATE_CREATED,
    DATE_MODIFIED,
    DATE_PAYMENT_COMMITMENT,
    ISARCHIVED,
    JOURNAL,
    NOTES,
    PO_NUMBER,
    SUPPLIER_INVOICE_NUMBER,
    TERMS_IN_DAYS,
    STRATA_PLAN_NUMBER,
    BUILDING_DISPLAY,
    SUPPLIER_DISPLAY,
    USERNAME_CREATED,
    USERNAME_MODIFIED,
    STATUS_DESCRIPTION,
    ID_USER_APPROVED,
    USERNAME_APPROVED,
    ID_STRATA_MANAGER,
    USERNAME_STRATAMANAGER,
    DATE_LATEST_PAYMENT_BANKED,
    INSURANCE_PREMIUM,
    NEXT_DATE_RENEWAL,
    COUNT_UNALLOCATED_BANK_TRANS,
    SUM_UNALLOCATED_BANK_TRANS,
    LIST_PAYMENT_TRANS_TYPE,
    LIST_PAYMENT_ACCOUNT,
    LIST_PAYMENT_NUMBER,
    ID_PAID_STATUS,
    PAID_STATUS_DESC,
    LIST_DEBIT_ACCOUNTS,
    LIST_DETAIL_AMOUNT,
    ADMIN_LEVY_YEAR_END_DATE,
    LEVY_ARREARS,
    NEXT_ADMIN_LEVY_DATE,
    ADMIN_LEVY_FREQUENCY,
    PAYABLE_NOTES)
AS
select
  poh.id,
  poh.id_building,
  poh.id_supplier,
  poh.id_status,
  poh.id_user_created,
  poh.id_user_modified,
  poh.date_order,
  poh.date_created,
  MAXVALUE(lm.date_modified, levy.modified_date, bd.modified_date),
  poh.date_payment_commitment,
  poh.isarchived,
  poh.journal,
  poh.notes,
  poh.order_number,
  poh.supplier_invoice_number,
  poh.terms_in_days,
  m.matter_code,
  m.matter_display,
  supplier.contact_display,
  usr_created.user_name,
  usr_modified.user_name,
  pos.description,
  poh.id_user_approved,
  usr_approved.user_name,
  bd.id_strata_manager,
  usr_stratamanager.user_name,
  poh.date_latest_payment_banked,
  m.sum_insurance_premium,
  m.next_date_renewal,
  bt_fhc.count_unallocated_bank_trans,
  bt_fhc.sum_unallocated_bank_trans,
  lm.list_payment_trans_type,
  lm.list_payment_account,
  lm.list_payment_number,
  poh.id_paid_status,
  ps.description,
  lm.list_debit_accounts,
  lm.list_detail_amount,
  levy.admin_levy_year_end_date,
  levy.levy_arrears,
  levy.next_admin_levy_date,
  levy.admin_frequency,
  bd.payable_notes
from purchase_order_header poh
join purchase_order_modified_date lm on lm.id_purchase_order = poh.id
join lk_purchase_order_status pos on pos.id = poh.id_status
join matter m on m.matter_id = poh.id_building
join contact supplier on supplier.contact_id = poh.id_supplier
join user_detail usr_created on usr_created.user_id = poh.id_user_created
join user_detail usr_modified on usr_modified.user_id = poh.id_user_modified
join user_detail usr_approved on usr_approved.user_id = poh.id_user_approved
join bt_building_details bd on bd.id = m.matter_id
join user_detail usr_stratamanager on usr_stratamanager.user_id = bd.id_strata_manager
join BT_FINANCIAL_HEALTH_CHECK bt_fhc on bt_fhc.id = poh.id_building
join lk_po_paid_status ps on ps.id = poh.id_paid_status
join VW_BT_LEVY levy on levy.Id = poh.id_building
where poh.id <> 0
;

Answer 1

I know its a very old question but since it have many upvotes, I believe its a common problem. I also experienced that problem just after migrating from 2.5 to 3.0, and the solution I found is realy very simple. In your case:

order by  id || '' asc

or, in case id is numeric:

order by id + 0 asc

Possible explanation:

Firebird 3 tries to use an existing index whenever possible to produce the ordering. When your resultset is a small fraction of the database, it is detrimental. The expressions presented don't change order key value but "fools" the database optimizer and the index is not used.