Hi,
There should a reason why optimizer chooses the wrong plan.
Try to recalculate indices statistics.
Also, for general performance, use optimized Firebird configuration:
http://ib-aid.com/en/optimized-firebird-configuration/
Regards,
Alexey Kovyazin
IBSurgeon HQbird
www.ib-aid.com
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?
