Guido,

> 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

Please update the statement and apply table aliases -- it is very
difficult to see the relationship between the fields in the WHERE clause
and the tables.

Second, what are the PLANs which are generated for the queries?

What indexes are defined on the tables?


The performance analysis shows that the database has a cache size of 60
000 pages, is this correct?

What is the page size of the databases?

It is possible that you don't have enough RAM in the server to support
both databases being open at the same time? (Cache size) So, the
server is performing a lot of paging to disk?


Sean