Thank you, Sean.

Your advice was brilliant. Now it runs faster, but not as fast as it was
under 1.5. I'm still waiting investigation results from Dmitry.

Andrew

Leyne, Sean wrote:

> Andrew,
>
>
>> I have migrated existing application from Firebird 1.5.2 to 2.0 RC3 on
>> the same server. The database was restored from backup with new gbak.
>> There is a query that becomes too slow right after migration. Firebird
>> 1.5.2 executed the query in 2-4 seconds, but it takes about 25-40 sec
>> with Firebird 2.0. I have no idea why it happened: the database is
>> small, so number of records involved is not heavy.
>> Could you please clarify the situation?
>>
>> The query is:
>>
>> select c.id, c.name, /*c.email,*/ i.event_date, i.event_time,
>> c.hot_flag, c.no_spam, /*a.name as aname,*/
>> cast(1 as smallint) as atype, t.name as itype,
>> /*a.subject as template,*/ a.id as action_id, t.id as type_id
>> from customers c
>> inner join interactions i on i.customer_id = c.id
>> and c.deleted = 0 and c.no_spam = 0
>> inner join cust_subscr s on s.customer_id = c.id
>> and s.type_id = i.type_id
>> inner join actions a on a.type_id = i.type_id
>> and a.relative = 1 and a.deleted = 0 and a.is_active > 0
>> and current_date-a.day_period >= i.event_date
>> inner join intr_types t on i.type_id = t.id
>> left join last_actions l on l.customer_id = c.id and l.action_id =
>>
> a.id
>
>> /* and l.event_tsc > i.event_date*/
>> where
>> l.event_tsc <
>> i.event_date+coalesce(i.event_time, cast('00:00' as
>>
> time))+a.day_period
>
> v2.0 has a new on-disk structure for indexes, which solves the
> significant performance problem related to indexes with poor
> selectivity.
>
> While that has provided a tremendous improvement in performance, it does
> have a cost -- additional disk pages for indexes, and additional
> overhead when the optimizer needs to construct a bitmap using 2 or more
> indexes.
>
> >From the plan,
>
>
>> Adapted Plan
>> ------------------------------------------------
>> PLAN JOIN (JOIN (A NATURAL, T INDEX (INTEG_34), S INDEX (INTEG_44), C
>> INDEX (INTEG_23), I INDEX (INTEG_39, INTEG_42)), L INDEX (INTEG_200,
>> INTEG_144))
>>
>
> We see that table I and L are trying to use multiple indexes.
>
> To resolve this, I would recommend that you create some compound indexes
> -- an index on Deleted + No_Spam on the Customer and the Type_ID +
> Relative + Deleted on the Actions and the Customer_ID and Action_ID in
> the Last_Actions.
>
>
>
> Sean
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>