I'm currently in the process of migrating a database from FB 1.5 to FB
2.0.1. I have done a backup of the DB under FB 1.5 and have restored it
under FB 2, so the ODS is now 11. Additionally I have a copy of the DB
running under FB 1.5. In the following I will call the DB running under
FB 1.5 DB15 and the DB running under FB 2.0.1 DB2.
Now I compare all SQL Statements and Stored Procedures concerning
Performance. I have come to a analysis, which calls a SP several times,
every time for a different week. The SP selects only the data for the
specific week, there are no complex calculations. Under FB 2 the
analysis takes 38 seconds and under the FB 1.5 16 seconds. Here FB 2 is
about factor 2 slower, the databases are the same only the ODS differs.

The SP does only one SQL Statement

for select GDTitle,GDEAN,GDPUBLISHER,GDPCID
from GFK_DATA join GFK_PRODUCTAREAS on GDGAID=GAID
where GAIsHardware=:Hardware and GDTitleDistinct=GAID and

((:PCTitleList containing ';'||F_LRTrim(GDTitle)||';') or
(:PCTitleList is NULL)) and

((:PCEANList containing ';'||F_LRTrim(GDEAN)||';') or (:PCEANList
is NULL)) and

((:PCGTIDList containing ';'||GDGTID||';') or (:PCGTIDList is NULL))
and

((:PublisherList containing ';'||F_LRTrim(GDPublisher)||';') or
(:PublisherList is NULL)) and

((exists (select PCID from PRODUCTS where :PO500IDList containing
';'||PCPOID500||';' and PCID=GFK_DATA.GDPCID)) or (:PO500IDList is NULL))

order by GDTitle collate de_de, GDEAN
into GDTitle, GDEAN, GDPUBLISHER, GDPCID do
begin

select GDPiecesPanel, GDValuePanel, GDPiecesMarket, GDValueMarket from
GFK_DATA
where GDTitle=:GDTitle and GDEAN=:GDEAN
and GDCalendarWeek=:CalendarWeek and GDYear=:CalendarYear
into :GDPiecesPanel_WeekOfTheYear, :GDValuePanel_WeekOfTheYear,
:GDPiecesMarket_WeekOfTheYear, :GDValueMarket_WeekOfTheYear;

end

Now I run the SP only once with IBExpert and here are the performance
analyses:

DB2
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 484,00 ms
Avg fetch time: 23,05 ms

Memory
------------------------------------------------
Current: 16.078.184
Max : 16.147.348
Buffers: 60.000

Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 65.891


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed |
Updates | Deletes | Inserts |
| | Total | reads | reads |
| | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| BUNDLES| 0 | 19 | 0 |
0 | 0 | 0 |
| GFK_DATA| 0 | 13343 | 0 |
0 | 0 | 0 |
| GFK_PRODUCTAREAS| 0 | 16 | 3 |
0 | 0 | 0 |
| PRODUCTS| 0 | 490 | 0 |
0 | 0 | 0 |
| SETTINGS| 0 | 0 | 108 |
0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+


DB15
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 250,00 ms
Avg fetch time: 11,90 ms

Memory
------------------------------------------------
Current: 18.406.992
Max : 18.459.516
Buffers: 60.000

Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 65.891


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed |
Updates | Deletes | Inserts |
| | Total | reads | reads |
| | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| BUNDLES| 0 | 19 | 0 |
0 | 0 | 0 |
| GFK_DATA| 0 | 13343 | 0 |
0 | 0 | 0 |
| GFK_PRODUCTAREAS| 0 | 16 | 3 |
0 | 0 | 0 |
| PRODUCTS| 0 | 490 | 0 |
0 | 0 | 0 |
| SETTINGS| 0 | 0 | 108 |
0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+


Now I need the help of the DB Gurus :-) What can I do or where should I
search to find the problem. I know from the release notes, that I
shouldn't use the 'not in' in FB 2, but that's not the case. Any other
points I have to be aware of in FB 2?

Regards

Guido