Hi Rafael!

I am wondering about the TBLCUSTOMER_ACTIVITY_LOG table. What kind of
indexes do you have defined here, and is there anything in the WHERE or
JOIN clause that could happen to use an index in IB and not Fb or vice versa?

Basically I am just guessing, you may have to supply us with some
information about your select and the fields of this table for more useful
comments.

Set

--- In firebird-support@yahoogroups.com, Rafael Szuminski wrote:
> Hi All,
>
> while trying to tune a complex query I run into a 'abnormality'. In
> this instance it seems like FB 1.5 is actually slower then IB 6.01.
> This is a complex query hitting multiple stored procedures,views and
> tables. When you look at the performance analysis one figure stands
> out: 1 million indexed reads on IB vs 20 million indexed reads on
> FB. The query was 2-8 seconds faster with FB, but FB is running on a
> 1.1 GhZ Athlon Server with 512 MB, where as IB is running on my 1
> GhZ PIII Laptop with 512MB. So, this is the strangest thing....
>
> The reason I am posting it here is, since we are moving to FB, it
> would be nice to get the speed benefit while we are at it. And I
> think if the amount of reads could be reduced from 20 mil to 1 mil
> the query would finish much faster.
>
> I can't really post the plan here because like I said the query runs
> through multiple stored procedures which obstruct the 'real' plan.
>
> Oh, and of course I used the same gdb file on both computers ;-)
>
> Any ideas?
>
> Raf
>
> Interbase 6.01 - Performance Analysis
>
> Query Time
> ------------------------------------------------
> Execute : 120,874.00 ms
> Avg fetch time: 13,430.44 ms
>
> Operations
> ------------------------------------------------
> Read : 461,352
> Writes : 0
> Fetches: 4,718,222
>
> Enchanced Info:
>
> | Table Name | Index | Non-Index | Updates |
> | TBLCUSTOMER_ACTIVITY_LOG| 1019888 | 0 | 0 |
>
> Firebird 1.5 RC4 - Performance Analysis
>
> Query Time
> ------------------------------------------------
> Execute : 114,054.00 ms
> Avg fetch time: 4,224.22 ms
>
> Operations
> ------------------------------------------------
> Read : 2,905,192
> Writes : 0
> Fetches: 41,042,838
>
> Enchanced Info:
>
> | Table Name | Index | Non-Index | Updates |
> | TBLCUSTOMER_ACTIVITY_LOG| 20309000 | 0 | 0 |