Solved

# Optimization problem on FB when use more indexes to retrieve data

Posted on 2007-08-12
435 Views
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
Question by:tvrdak

LVL 19

Expert Comment

please post the query and plan
0

Author Comment

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

LVL 19

Accepted Solution

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

LVL 19

Expert Comment

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

LVL 1

Expert Comment

Forced accept.

Computer101
0

## Featured Post

### Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…