| Subject | Odp: [firebird-support] Firebird migration to in crease perfomances |
|---|---|
| Author | liviuslivius@poczta.onet.pl |
| Post date | 2015-06-22T14:22:14Z |
1. Increase your db cache in db or firebird config as i see reads from disc instead of cache
2. Put sql in mesage body
Regards,
Karol Bieniaszewski
Od: "'Stef' stef@... [firebird-support]" <firebird-support@yahoogroups.com>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Firebird migration to increase perfomances
Data: pon., cze 22, 2015 12:55
Hi All
I am not a SQL master but do know the basics.
I am have an issue with sub select queries and setting conditions.
Attached is the query I have a problem with, see my comments on the last condition.
Please assist me if you can.
Stef van der Merwe
P.S see below is my plan I am sure the query could be done better for faster performance any advise would be greatly appreciated !!
Plan
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (STAFF INDEX (STAFF_IDX1))
PLAN (CUSTOMER INDEX (CUSTOMER_IDX1))
PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN SORT (JOIN (J INDEX (JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX (INVOICES_IDX5)))
Adapted Plan
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (STAFF INDEX (STAFF_IDX1)) PLAN (CUSTOMER INDEX (CUSTOMER_IDX1)) PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN SORT (JOIN (J INDEX (JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX (INVOICES_IDX5)))
------ Performance info ------
Prepare time = 16ms
Execute time = 15s 828ms
Avg fetch time = 465.53 ms
Current memory = 74 375 188
Max memory = 74 391 660
Memory buffers = 8 192
Reads from disk to cache = 253
Writes from cache to disk = 0
Fetches from cache = 730 569
[Non-text portions of this message have been removed]