All geek questions in one place

Firebird Slow performance with ORDER BY

I have a view of VW_PURCHASE_ORDER_LIST. It contains about 200,000 entries.

It takes 16 ms to complete the request:

select first 128 * from VW_PURCHASE_ORDER_LIST

However, when I use the order by operator, 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 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 ( 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 ), 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 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 any way to speed this up? The problem is that our database is growing in size, and there are currently many large tables. Our application used to load the entire table into the grid, but since we have so much data, it now takes minutes to load and maximize client PC memory.

As a solution, we use DevExpress ServerMode XtraGrid. Downloading and scrolling through the grid is slow, because it sends the initial select count(*) from x_table_or_view_name to get the total records, and then, when scrolling, sends select first x skip y from x_table_or_view_name order by id .... this is the order thereby slows down the request and makes the grid unusable

I am not sure where to go from here, as I am not a database expert, if someone can help with some suggestions, this will be very helpful.

Update without order:

 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 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) 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 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)) 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. Can I publish DDL for tables in a 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 ;
+5
source share

No one has answered this question yet.

See related questions:

Source: https://habr.com/ru/post/1210339/

More articles:


All Articles