asked on

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

Nick Upson

please post the query and plan

tvrdak

ASKER

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

ASKER CERTIFIED SOLUTION

Nick Upson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nick Upson

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

Computer101

Forced accept.

Computer101
EE Admin