Hi, 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 ----- Reply message ----- Od: "'Stef' [email protected] [firebird-support]" <[email protected]> Do: <[email protected]> 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]
