[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Optimization problem on FB when use more indexes to retrieve data

Hi,

I have optimization problem on FB(1.5.2 on win).  I have dynamicly built  query of 2 tables - headers and rows. When optimizer decide to proceed first header then rows, then data from rows are taken via 2 indexes - first is based on foreign key from rows to headers (this is OK) and second is based on foreign key to other table with about 100 valuse. It is added based on another condition in query.
This second index has vary bad optimization influence - instead seconds query will take minutes.
Is there in FB any way  to solve it ? I am meaning to tell fb to use only one index to retrieve data from table.
And in which case use of more indexes for fetching could bring some effect ?

thanks, tvr
0
tvrdak
Asked:
tvrdak
  • 3
1 Solution
 
NickUpsonCommented:
please post the query and plan
0
 
tvrdakAuthor Commented:
Query is (as an example, it is dynamicly builded based on given conditions):
SELECT A.ID
FROM
  StoreDocuments2 A
  JOIN StoreDocuments SD ON SD.ID=A.Parent_ID
  LEFT JOIN StoreCards SC ON SC.ID=A.StoreCard_ID
WHERE
  A.RowType=3
AND
(
(SD.DocQueue_ID IN ('1F00000101','7E10000101','8F00000101','KC00000101','LC00000101','NC00000101') )
AND (SD.DocDate$DATE >= 38808 and SD.DocDate$DATE < 38899 )
AND (A.Store_ID IN ('1000000101','1200000101','2600000101','2700000101','2900000101','3000000101','3100000101','3700000101','3800000101','4000000101','4100000101','4500000101','5000000101','5100000101','5200000101','6000000101','7000000101','7100000101','8000000101','8100000101','9000000101','9100000101','A100000101','B000000101','B100000101','C000000101','C100000101','D000000101','F000000101','F200000101','I100000101','K100000101','L200000101','M000000101','M100000101','M200000101','N200000101','P000000101','R100000101','U100000101','Y100000101','Z100000101'))
)

ORDER BY SC.Code ASC NULLS FIRST,A.StoreCard_ID ASC NULLS FIRST

=============================================================================

Plan is (I removed repeated index items was there due to IN clause)
PLAN SORT (JOIN (JOIN (SD INDEX (STOREDOCUMENTS_DD,STOREDOCUMENTSDOCQUEUEFK),A INDEX (STOREDOCUMENTS2PARENTFK,STOREDOCUMENTS2STOREFK)),SC INDEX (STORECARDSPK)))

------ Performance info ------
Prepare time = 0ms
Execute time = 47m 38s 625ms
Avg fetch time = 37,49 ms
Current memory = 17 036 304
Max memory = 17 417 108
Memory buffers = 2 048
Reads from disk to cache = 31 759 375
Writes from cache to disk = 0
Fetches from cache = 34 056 084

=============================================================================

when PLAN is
PLAN  (SORT  (JOIN  (JOIN  (SD INDEX (STOREDOCUMENTS_DD),A INDEX (STOREDOCUMENTS2PARENTFK)),SC INDEX (STORECARDSPK))))
then result is (this result is just after start FB, otherways it take about 10 seconds):
------ Performance info ------
Prepare time = 0ms
Execute time = 54s 281ms
Avg fetch time = 0,71 ms
Current memory = 1 772 740
Max memory = 1 873 264
Memory buffers = 2 048
Reads from disk to cache = 31 904
Writes from cache to disk = 0
Fetches from cache = 639 412

=============================================================================

So seams to me is very uneffective to use more indexes for fetching records from stream A (there are rows there)
A INDEX (STOREDOCUMENTS2PARENTFK,STOREDOCUMENTS2STOREFK)
but don't know, where to take influence on it if it si even possible
btw - I don't want to discharge condition based on Store_ID from optimization - when there will be other conditions and optimizer will prepare plan starting from stream A, then it could be an advantage to have it in plan

0
 
NickUpsonCommented:
will look at later, can you confirm that statistics are up to date, e.g.

ALTER PROCEDURE PR_SYS_CALCSELECTIVITY
AS
  declare variable IDX VARCHAR(50);
  declare variable STMT VARCHAR(75);
begin

/*
  Purpose  : cause indexes to rebuild their statistics
*/

    for select RDB$INDEX_NAME
    from RDB$INDICES
    into :IDX
    do
    begin
        STMT = 'SET STATISTICS INDEX ' || :IDX;
        execute statement :STMT;
    end
end
0
 
NickUpsonCommented:
if stats are up to date try adding "and 1 = 1" to the join condition you don't want the index to be used.

If upgrading to firebird 2+ is an option this would probably fix it as much work has been done on the optimiser

0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now