Re: [firebird-support] An SQL query perform badly after firebird database file size grow beyond 8GB
>Query: > >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 > >Good plan: >PLAN JOIN (B INDEX (SL_CSDTL_ITEMCODE), A INDEX (RDB$PRIMARY212)) > >Bad plan: >PLAN JOIN (A INDEX (SL_CS_CODE), B INDEX (SL_CSDTL_DOCKEY, SL_CSDTL_ITEMCODE)) > >Is this an issue related to Windows file system? Firebird service or my query? > >It is strange why the firebird choose wtong plan when database file size grow beyond 8GB. I thought it should be consistent for same query. I'd say this is an issue mainly related to your query and the Firebird optimizer, though I doubt it has anything to do with the file size. I am also very happy that the plan changes depending on index selectivity and not static. Think of a table PERSON that only contains a pair of twins: PK Gender BirthDate 1 Female 15.1.1980 2 Male15.1.1980 In this particular case, an index on GENDER would be equally selective as PK and more selective than BIRTHDATE. Add 10 other persons, and in most cases BIRTHDATE would become more selective than GENDER. My guess is that the rows you're added reduces the selectivity of SL_CSDTL_ITEMCODE and that this makes Firebird (incorrectly) believe that the bad plan becomes better than the good plan. You can prevent Firebird from using the bad plan by changing the query a bit (I've also changed it to using explicit joins, that is never worse and sometimes better than using implicit joins: SELECT MAX(A.PostDate) MaxPostDate FROM SL_CS A JOIN SL_CSDTL B ON A.DocKey=B.DocKey||'' --replace +'' with +0 if DocKey is a number WHERE A.Code='300-10001' AND B.ItemCode='OCC' AND B.UOM='UNIT' AND A.Cancelled='F' AND B.UnitPrice<>0 HTH, Set
Re: [firebird-support] An SQL query perform badly after firebird database file size grow beyond 8GB
It is strange why the firebird choose wtong plan when database file size grow beyond 8GB. I thought it should be consistent for same query.
RE: [firebird-support] An SQL query perform badly after firebird database file size grow beyond 8GB
> My guess is that the rows you're added reduces the selectivity of > SL_CSDTL_ITEMCODE and that this makes Firebird (incorrectly) believe that > the bad plan becomes better than the good plan. You can prevent Firebird > from using the bad plan by changing the query a bit (I've also changed it to > using explicit joins, that is never worse and sometimes better than using > implicit joins: > > SELECT MAX(A.PostDate) MaxPostDate > FROM SL_CS A > JOIN SL_CSDTL B ON A.DocKey=B.DocKey||'' --replace +'' with +0 if DocKey is > a number WHERE A.Code='300-10001' >AND B.ItemCode='OCC' >AND B.UOM='UNIT' >AND A.Cancelled='F' >AND B.UnitPrice<>0 As usual SET has provided very good advice. I would just add a couple of things. 1- Have you tested the query after a backup/restore? This would result in the index selectivity being recalculated, and thus the engine might choose a different plan. 2- Look at creating compound indexes on A.Code+A.Cancelled and/or B.DocKey+B.ItemCode+B.UOM -- where the order of the fields in the index would be based on either (a) the fields which have most unique values to those that are more common, (b) the reserve order from (a), or (c) the field which is most commonly queried, followed by the field which is often used in queries with the first field.
Re: [firebird-support] An SQL query perform badly after firebird database file size grow beyond 8GB
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?
[firebird-support] An SQL query perform badly after firebird database file size grow beyond 8GB
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?
