| Subject | Query Performance Problem after Upgrading from Firebird 2.1 to 2.5 |
|---|---|
| Author | |
| Post date | 2013-10-24T19:45:45Z |
I am having a query performance problem
after upgrading from Firebird version 2.1 to 2.5.2.26540 64-bit on Windows.
When the query is run the first time on each server, the run time
is close to the same on 2.1 and 2.5, about 0.25 seconds. However,
each additional time the query is run on 2.5, the query keeps getting slower.
After running the query several times, it may start taking several
seconds to run. On version 2.1, the time it takes the query to run
is consistent across multiple runs (~ 0.25 seconds). I am seeing
the problem using Delphi XE2 with both the UIB and ZeosLib components and
I am also having the same problem when using Flame Robin 0.9.3 with
Firebird 2.5. The only way I have found to get the query to run again
in the expected amount of time on 2.5 is to disconnect and reconnect to
the database. Then the same pattern starts again - first run goes
as expected and each successive run is slower than the previous run. Disconnecting
and reconnecting is not necessary on version 2.1.
I have tried Firebird 2.5 64-bit on Windows Server 2008 R2 and Windows 7. I have also tried SuperServer, SuperClassic, and Classic. The run time of the query on Firebird 2.5 slows down on all of these. No one else is using the test servers. The query is below. How can I improve the query performance on Firebird 2.5?
First run:
Executing statement...
Statement executed (elapsed time: 0.000s).
64116 fetches, 16 marks, 380 reads, 16 writes.
0 inserts, 0 updates, 0 deletes, 12776 index, 3658 seq.
Delta memory: 18142368 bytes.
Total execution time: 0.292s
Script execution finished.
Run 7:
Executing statement...
Statement executed (elapsed time: 0.000s).
60869 fetches, 0 marks, 241 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 12341 index, 2974 seq.
Delta memory: 3950952 bytes.
Total execution time: 3.042s
Script execution finished.
Starting transaction...
Preparing statement: select distinct tr.tr_approved, tr.tr_approver_user_id, tr.tr_date_end, tr.tr_date_start, tr.tr_datetime_modified, tr.tr_datetime_requested, tr.tr_datetime_status_set,
tr.tr_hours, tr.tr_id, tr.tr_instructor, tr.tr_location, tr.tr_required, tr_status, tr.tr_tc_id, tr.tr_title, tr.tr_title_upper, tr.tr_tn_id, tr.tr_tt_id, tr.tr_user_id,
tc.tc_category, tc.tc_description, tc.tc_total_group_number,tt.tt_type, tt.tt_description, tn.tn_use_tn, us2.us_email_address, us2.us_notify_by_email,
trim(us2.us_last_name_upper) || ' ' || rtrim(us2.us_generation_upper) || ', ' || rtrim(us2.us_first_name_upper) || ' ' || rtrim(us2.us_middle_name_upper) as full_name_upper,
us2.us_user_id, trim(us2.us_last_name) || ' ' || rtrim(us2.us_generation) || ', ' || rtrim(us2.us_first_name) || ' ' || rtrim(us2.us_middle_name) as full_name,
trim(us3.us_last_name) || ' ' || rtrim(us3.us_generation) || ', ' || rtrim(us3.us_first_name) || ' ' || rtrim(us3.us_middle_name) as approver_full_name
from training_requests tr
left outer join users us2 on (tr.tr_user_id = us2.us_user_id)
left outer join users us3 on (tr.tr_approver_user_id = us3.us_user_id)
left outer join training_category tc on (tr.tr_tc_id = tc.tc_id)
left outer join training_type tt on (tr.tr_tt_id = tt.tt_id)
left outer join training tn on (tr.tr_tn_id = tn.tn_id)
order by tr.tr_title_upper, tr.tr_date_end
Statement prepared (elapsed time: 0.011s).
Field #01: TRAINING_REQUESTS.TR_APPROVED Alias:TR_APPROVED Type:STRING(1)
Field #02: TRAINING_REQUESTS.TR_APPROVER_USER_ID Alias:TR_APPROVER_USER_ID Type:BIGINT
Field #03: TRAINING_REQUESTS.TR_DATE_END Alias:TR_DATE_END Type:DATE
Field #04: TRAINING_REQUESTS.TR_DATE_START Alias:TR_DATE_START Type:DATE
Field #05: TRAINING_REQUESTS.TR_DATETIME_MODIFIED Alias:TR_DATETIME_MODIFIED Type:TIMESTAMP
Field #06: TRAINING_REQUESTS.TR_DATETIME_REQUESTED Alias:TR_DATETIME_REQUESTED Type:TIMESTAMP
Field #07: TRAINING_REQUESTS.TR_DATETIME_STATUS_SET Alias:TR_DATETIME_STATUS_SET Type:TIMESTAMP
Field #08: TRAINING_REQUESTS.TR_HOURS Alias:TR_HOURS Type:NUMERIC(18,2)
Field #09: TRAINING_REQUESTS.TR_ID Alias:TR_ID Type:BIGINT
Field #10: TRAINING_REQUESTS.TR_INSTRUCTOR Alias:TR_INSTRUCTOR Type:STRING(80)
Field #11: TRAINING_REQUESTS.TR_LOCATION Alias:TR_LOCATION Type:STRING(80)
Field #12: TRAINING_REQUESTS.TR_REQUIRED Alias:TR_REQUIRED Type:STRING(1)
Field #13: TRAINING_REQUESTS.TR_STATUS Alias:TR_STATUS Type:STRING(24)
Field #14: TRAINING_REQUESTS.TR_TC_ID Alias:TR_TC_ID Type:BIGINT
Field #15: TRAINING_REQUESTS.TR_TITLE Alias:TR_TITLE Type:STRING(100)
Field #16: TRAINING_REQUESTS.TR_TITLE_UPPER Alias:TR_TITLE_UPPER Type:STRING(100)
Field #17: TRAINING_REQUESTS.TR_TN_ID Alias:TR_TN_ID Type:BIGINT
Field #18: TRAINING_REQUESTS.TR_TT_ID Alias:TR_TT_ID Type:BIGINT
Field #19: TRAINING_REQUESTS.TR_USER_ID Alias:TR_USER_ID Type:BIGINT
Field #20: TRAINING_CATEGORY.TC_CATEGORY Alias:TC_CATEGORY Type:STRING(60)
Field #21: TRAINING_CATEGORY.TC_DESCRIPTION Alias:TC_DESCRIPTION Type:STRING(100)
Field #22: TRAINING_CATEGORY.TC_TOTAL_GROUP_NUMBER Alias:TC_TOTAL_GROUP_NUMBER Type:INTEGER
Field #23: TRAINING_TYPE.TT_TYPE Alias:TT_TYPE Type:STRING(60)
Field #24: TRAINING_TYPE.TT_DESCRIPTION Alias:TT_DESCRIPTION Type:STRING(100)
Field #25: TRAINING.TN_USE_TN Alias:TN_USE_TN Type:STRING(1)
Field #26: USERS.US_EMAIL_ADDRESS Alias:US_EMAIL_ADDRESS Type:STRING(255)
Field #27: USERS.US_NOTIFY_BY_EMAIL Alias:US_NOTIFY_BY_EMAIL Type:STRING(1)
Field #28: . Alias:FULL_NAME_UPPER Type:STRING(799)
Field #29: USERS.US_USER_ID Alias:US_USER_ID Type:BIGINT
Field #30: . Alias:FULL_NAME Type:STRING(799)
Field #31: . Alias:APPROVER_FULL_NAME Type:STRING(799)
PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (TR NATURAL, US2 INDEX (RDB$PRIMARY12)), US3 INDEX (RDB$PRIMARY12)), TC INDEX (RDB$PRIMARY8)), TT INDEX (RDB$PRIMARY11)), TN INDEX (RDB$PRIMARY7))))
Executing statement...
Statement executed (elapsed time: 0.000s).
64116 fetches, 16 marks, 380 reads, 16 writes.
0 inserts, 0 updates, 0 deletes, 12776 index, 3658 seq.
Delta memory: 18142368 bytes.
Total execution time: 0.292s
Script execution finished.
Thank you,
Paul
---------------------------------------------------------------------------------
Confidentiality Notice: This message is the property of the United States Bankruptcy Court for the Western District of Kentucky. It may be legally privileged and/or confidential and is intended solely for the use of the addressee. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized disclosure, dissemination, distribution, copying or taking any action in reliance on the information contained herein is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this message.
I have tried Firebird 2.5 64-bit on Windows Server 2008 R2 and Windows 7. I have also tried SuperServer, SuperClassic, and Classic. The run time of the query on Firebird 2.5 slows down on all of these. No one else is using the test servers. The query is below. How can I improve the query performance on Firebird 2.5?
First run:
Executing statement...
Statement executed (elapsed time: 0.000s).
64116 fetches, 16 marks, 380 reads, 16 writes.
0 inserts, 0 updates, 0 deletes, 12776 index, 3658 seq.
Delta memory: 18142368 bytes.
Total execution time: 0.292s
Script execution finished.
Run 7:
Executing statement...
Statement executed (elapsed time: 0.000s).
60869 fetches, 0 marks, 241 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 12341 index, 2974 seq.
Delta memory: 3950952 bytes.
Total execution time: 3.042s
Script execution finished.
Starting transaction...
Preparing statement: select distinct tr.tr_approved, tr.tr_approver_user_id, tr.tr_date_end, tr.tr_date_start, tr.tr_datetime_modified, tr.tr_datetime_requested, tr.tr_datetime_status_set,
tr.tr_hours, tr.tr_id, tr.tr_instructor, tr.tr_location, tr.tr_required, tr_status, tr.tr_tc_id, tr.tr_title, tr.tr_title_upper, tr.tr_tn_id, tr.tr_tt_id, tr.tr_user_id,
tc.tc_category, tc.tc_description, tc.tc_total_group_number,tt.tt_type, tt.tt_description, tn.tn_use_tn, us2.us_email_address, us2.us_notify_by_email,
trim(us2.us_last_name_upper) || ' ' || rtrim(us2.us_generation_upper) || ', ' || rtrim(us2.us_first_name_upper) || ' ' || rtrim(us2.us_middle_name_upper) as full_name_upper,
us2.us_user_id, trim(us2.us_last_name) || ' ' || rtrim(us2.us_generation) || ', ' || rtrim(us2.us_first_name) || ' ' || rtrim(us2.us_middle_name) as full_name,
trim(us3.us_last_name) || ' ' || rtrim(us3.us_generation) || ', ' || rtrim(us3.us_first_name) || ' ' || rtrim(us3.us_middle_name) as approver_full_name
from training_requests tr
left outer join users us2 on (tr.tr_user_id = us2.us_user_id)
left outer join users us3 on (tr.tr_approver_user_id = us3.us_user_id)
left outer join training_category tc on (tr.tr_tc_id = tc.tc_id)
left outer join training_type tt on (tr.tr_tt_id = tt.tt_id)
left outer join training tn on (tr.tr_tn_id = tn.tn_id)
order by tr.tr_title_upper, tr.tr_date_end
Statement prepared (elapsed time: 0.011s).
Field #01: TRAINING_REQUESTS.TR_APPROVED Alias:TR_APPROVED Type:STRING(1)
Field #02: TRAINING_REQUESTS.TR_APPROVER_USER_ID Alias:TR_APPROVER_USER_ID Type:BIGINT
Field #03: TRAINING_REQUESTS.TR_DATE_END Alias:TR_DATE_END Type:DATE
Field #04: TRAINING_REQUESTS.TR_DATE_START Alias:TR_DATE_START Type:DATE
Field #05: TRAINING_REQUESTS.TR_DATETIME_MODIFIED Alias:TR_DATETIME_MODIFIED Type:TIMESTAMP
Field #06: TRAINING_REQUESTS.TR_DATETIME_REQUESTED Alias:TR_DATETIME_REQUESTED Type:TIMESTAMP
Field #07: TRAINING_REQUESTS.TR_DATETIME_STATUS_SET Alias:TR_DATETIME_STATUS_SET Type:TIMESTAMP
Field #08: TRAINING_REQUESTS.TR_HOURS Alias:TR_HOURS Type:NUMERIC(18,2)
Field #09: TRAINING_REQUESTS.TR_ID Alias:TR_ID Type:BIGINT
Field #10: TRAINING_REQUESTS.TR_INSTRUCTOR Alias:TR_INSTRUCTOR Type:STRING(80)
Field #11: TRAINING_REQUESTS.TR_LOCATION Alias:TR_LOCATION Type:STRING(80)
Field #12: TRAINING_REQUESTS.TR_REQUIRED Alias:TR_REQUIRED Type:STRING(1)
Field #13: TRAINING_REQUESTS.TR_STATUS Alias:TR_STATUS Type:STRING(24)
Field #14: TRAINING_REQUESTS.TR_TC_ID Alias:TR_TC_ID Type:BIGINT
Field #15: TRAINING_REQUESTS.TR_TITLE Alias:TR_TITLE Type:STRING(100)
Field #16: TRAINING_REQUESTS.TR_TITLE_UPPER Alias:TR_TITLE_UPPER Type:STRING(100)
Field #17: TRAINING_REQUESTS.TR_TN_ID Alias:TR_TN_ID Type:BIGINT
Field #18: TRAINING_REQUESTS.TR_TT_ID Alias:TR_TT_ID Type:BIGINT
Field #19: TRAINING_REQUESTS.TR_USER_ID Alias:TR_USER_ID Type:BIGINT
Field #20: TRAINING_CATEGORY.TC_CATEGORY Alias:TC_CATEGORY Type:STRING(60)
Field #21: TRAINING_CATEGORY.TC_DESCRIPTION Alias:TC_DESCRIPTION Type:STRING(100)
Field #22: TRAINING_CATEGORY.TC_TOTAL_GROUP_NUMBER Alias:TC_TOTAL_GROUP_NUMBER Type:INTEGER
Field #23: TRAINING_TYPE.TT_TYPE Alias:TT_TYPE Type:STRING(60)
Field #24: TRAINING_TYPE.TT_DESCRIPTION Alias:TT_DESCRIPTION Type:STRING(100)
Field #25: TRAINING.TN_USE_TN Alias:TN_USE_TN Type:STRING(1)
Field #26: USERS.US_EMAIL_ADDRESS Alias:US_EMAIL_ADDRESS Type:STRING(255)
Field #27: USERS.US_NOTIFY_BY_EMAIL Alias:US_NOTIFY_BY_EMAIL Type:STRING(1)
Field #28: . Alias:FULL_NAME_UPPER Type:STRING(799)
Field #29: USERS.US_USER_ID Alias:US_USER_ID Type:BIGINT
Field #30: . Alias:FULL_NAME Type:STRING(799)
Field #31: . Alias:APPROVER_FULL_NAME Type:STRING(799)
PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (TR NATURAL, US2 INDEX (RDB$PRIMARY12)), US3 INDEX (RDB$PRIMARY12)), TC INDEX (RDB$PRIMARY8)), TT INDEX (RDB$PRIMARY11)), TN INDEX (RDB$PRIMARY7))))
Executing statement...
Statement executed (elapsed time: 0.000s).
64116 fetches, 16 marks, 380 reads, 16 writes.
0 inserts, 0 updates, 0 deletes, 12776 index, 3658 seq.
Delta memory: 18142368 bytes.
Total execution time: 0.292s
Script execution finished.
Thank you,
Paul
---------------------------------------------------------------------------------
Confidentiality Notice: This message is the property of the United States Bankruptcy Court for the Western District of Kentucky. It may be legally privileged and/or confidential and is intended solely for the use of the addressee. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized disclosure, dissemination, distribution, copying or taking any action in reliance on the information contained herein is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this message.