I am using Firebird 2.5.5 x64 on Windows 8.1 x64. I wrote an application access a Firebird database. So far so good.
One day, I notice a function running query accessing the database become extremely slow. After I debug, I found the query is the cause: SELECT MAX(A.PostDate) MaxPostDate FROM SL_CS A, SL_CSDTL B WHERE A.DocKey=B.DocKey AND A.Code='300-10001' AND B.ItemCode='OCC' AND B.UOM='UNIT' AND A.Cancelled='F' AND B.UnitPrice<>0 Further investigation and I found an interesting problem. If the database file size is below 8GB, the query works normal. Once the database file size grow beyond 8GB, the query become very slow. Here is the plan before 8GB: PLAN JOIN (B INDEX (SL_CSDTL_ITEMCODE), A INDEX (RDB$PRIMARY212)) Adapted Plan PLAN JOIN (B INDEX (SL_CSDTL_ITEMCODE), A INDEX (INTEG_2872)) ------ Performance info ------ Prepare time = 0ms Execute time = 312ms Avg fetch time = 312.00 ms Current memory = 14,858,352 Max memory = 14,972,720 Memory buffers = 2,048 Reads from disk to cache = 42,429 Writes from cache to disk = 0 Fetches from cache = 360,726 And the plan after grow to 8GB: PLAN JOIN (A INDEX (SL_CS_CODE), B INDEX (SL_CSDTL_DOCKEY, SL_CSDTL_ITEMCODE)) ------ Performance info ------ Prepare time = 0ms Execute time = 2m 0s 875ms Avg fetch time = 120,875.00 ms Current memory = 14,943,040 Max memory = 15,005,768 Memory buffers = 2,048 Reads from disk to cache = 44,818 Writes from cache to disk = 0 Fetches from cache = 11,454,178 Both plan works on same tables with same row count. Increasing of database file size is due to insertion on other tables. I believe the performance issue was due to "Fetches from cache" increasing dramatically on 2nd plan. Is this an issue related to Windows file system? Firebird service or my query?
