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
------------------------------------------------
Prepare : 0.00 ms
Execute : 120,874.00 ms
Avg fetch time: 13,430.44 ms

Memory
------------------------------------------------
Current: 4,290,664
Max : 4,418,768
Buffers: 2,048

Operations
------------------------------------------------
Read : 461,352
Writes : 0
Fetches: 4,718,222


Enchanced Info:
+--------------------------+---------+-----------+---------+---------+---------+
| Table Name | Index | Non-Index | Updates | Deletes | Inserts |
| | reads | reads | | | |
+--------------------------+---------+-----------+---------+---------+---------+
| TBLCUSTOMER_ACTIVITY_LOG| 1019888 | 0 | 0 | 0 | 0 |
| TBLWISH_LIST| 4664 | 0 | 0 | 0 | 0 |
| TBLACTIVITIES| 0 | 40320 | 0 | 0 | 0 |
| TBLAPPOINTMENTS| 0 | 1728 | 0 | 0 | 0 |
| TBLSALES_AGENTS| 0 | 181 | 0 | 0 | 0 |
+--------------------------+---------+-----------+---------+---------+---------+






**************************************************************************************

Firebird 1.5 RC4 - Performance Analysis



Query Time
------------------------------------------------
Prepare : 0.00 ms
Execute : 114,054.00 ms
Avg fetch time: 4,224.22 ms

Memory
------------------------------------------------
Current: 1,269,968
Max : 1,782,592
Buffers: 2,048

Operations
------------------------------------------------
Read : 2,905,192
Writes : 0
Fetches: 41,042,838


Enchanced Info:
+--------------------------+----------+-----------+---------+---------+---------+
| Table Name | Index | Non-Index | Updates | Deletes | Inserts |
| | reads | reads | | | |
+--------------------------+----------+-----------+---------+---------+---------+
| TBLSALES_AGENTS| 0 | 181 | 0 | 0 | 0 |
| TBLCUSTOMER_ACTIVITY_LOG| 20309000 | 0 | 0 | 0 | 0 |
| TBLWISH_LIST| 4664 | 0 | 0 | 0 | 0 |
| TBLACTIVITIES| 0 | 40320 | 0 | 0 | 0 |
| TBLAPPOINTMENTS| 0 | 1728 | 0 | 0 | 0 |
+--------------------------+----------+-----------+---------+---------+---------+