> Did you run backup/restore before using in under FB2?

No backup/restore. I ran the DDL scripts to create the domains and
tables, copied over the data, then the scripts to create the
constraints, indexes, views, and stored procedures.

Data may be slightly different as the snapshots are a few days apart.

FB 2.0

RDB$RELATION_NAME RDB$INDEX_NAME RDB$STATISTICS
RDB$FIELD_POSITION RDB$FIELD_NAME RDB$STATISTICS_1
ACT_CHC_PHARM_SUBACCT FK_ACTCHCPHARMSUBACCT_CRMCHC 0.005882353 0
CHC 0.005882353
ACT_CHC_PHARM_SUBACCT FK_ACTCHCPHARMSUBACCT_CRMPHARM 0.0078125 0
PHARMACY 0.0078125
ACT_CHC_PHARM_SUBACCT PK_ACT_CHC_PHARM_SUBACCT 0 0
CHC 0
ACT_CHC_PHARM_SUBACCT PK_ACT_CHC_PHARM_SUBACCT 0 1
PHARMACY 0
ACT_CHC_PHARM_SUBACCT U_ACT_CHC_PHARM_SUBACCT 0.004329004 0
SUBACCT 0.004329004
PBM_CLAIM FK_PBMCLAIM_ACTCHCPHARMSUBACCT 0.005291005 0
ACCOUNTID 0.006849315
PBM_CLAIM FK_PBMCLAIM_ACTCHCPHARMSUBACCT 0.005291005 1
SRVPROVID 0.005291005
PBM_CLAIM FK_PBMCLAIM_RXSCHCFEEGROUPS 0.00172117 0
ACCOUNTID 0.006849315
PBM_CLAIM FK_PBMCLAIM_RXSCHCFEEGROUPS 0.00172117 1
GROUPID 0.00172117
PBM_CLAIM I_PBMCLAIM_DATESBM 0.000931099 0
DATESBM 0.000931099
PBM_CLAIM I_PBMCLAIM_DECIMALQTY 0.001375516 0
DECIMALQTY 0.001375516
PBM_CLAIM I_PBM_CLAIM_DATESBM_DESC 0.000931099 0
DATESBM 0.000931099
PBM_CLAIM I_PBM_CLAIM_NDC 0.000101082 0
PRODUCTID 0.000101082
PBM_CLAIM I_PBM_CLAIM_NDC9 0.000133601 0
NDC9 0.000133601
PBM_CLAIM I_PBM_CLAIM_POSTED 0.000954198 0
POSTED 0.000954198
PBM_CLAIM I_PBM_CLAIM_SRVPROVID 0.008264462 0
SRVPROVID 0.008264462
PBM_CLAIM PK_PBMCLAIM 0 0
ID 0
RXS_CLM_CALC_FEES FK_CCF_GRANTEEINVOICE 0.000546747 0
GRANTEE_INVOICE 0.000546747
RXS_CLM_CALC_FEES FK_CLMCALCFEES_FEESTRUCT 0.000766871 0
FEE_STRUCT 0.000766871
RXS_CLM_CALC_FEES FK_CLMCALCFEES_PHARMBILLGRPINVC 0.000260078 0
INVC_PHARM_BILLGRP_INVC 0.000260078
RXS_CLM_CALC_FEES FK_RXSCLMCALCFEES_PBMCLAIM 1.43698E-06 0
CLAIM_ID 1.43698E-06
RXS_CLM_CALC_FEES FK_RXSCLMCALCFEES_RXSINVOICE 0.000554324 0
RXS_INVOICE 0.000554324
RXS_CLM_CALC_FEES I_RXSCLMCALCFEES_POSTED 6.18965E-05 0
POSTED 6.18965E-05
RXS_CLM_CALC_FEES PK_RXSCLMCALCFEES 0 0
CLAIM_ID 0

FB 1.5

RDB$RELATION_NAME RDB$INDEX_NAME RDB$STATISTICS
RDB$FIELD_POSITION RDB$FIELD_NAME
------------------------------------------------------------------------
---------------------------------------
ACT_CHC_PHARM_SUBACCT FK_ACTCHCPHARMSUBACCT_CRMCHC 0.0058823530562222
0 CHC
ACT_CHC_PHARM_SUBACCT FK_ACTCHCPHARMSUBACCT_CRMPHARM 0.0078125 0
PHARMACY
ACT_CHC_PHARM_SUBACCT PK_ACT_CHC_PHARM_SUBACCT
0.00432900432497263 0 CHC
ACT_CHC_PHARM_SUBACCT PK_ACT_CHC_PHARM_SUBACCT
0.00432900432497263 1 PHARMACY
ACT_CHC_PHARM_SUBACCT U_ACT_CHC_PHARM_SUBACCT
0.00432900432497263 0 SUBACCT
PBM_CLAIM FK_PBMCLAIM_ACTCHCPHARMSUBACCT
0.00529100513085723 0 ACCOUNTID
PBM_CLAIM FK_PBMCLAIM_ACTCHCPHARMSUBACCT
0.00529100513085723 1 SRVPROVID
PBM_CLAIM FK_PBMCLAIM_RXSCHCFEEGROUPS
0.00171821308322251 0 ACCOUNTID
PBM_CLAIM FK_PBMCLAIM_RXSCHCFEEGROUPS
0.00171821308322251 1 GROUPID
PBM_CLAIM I_PBMCLAIM_DATESBM
0.000923361047171056 0 DATESBM
PBM_CLAIM I_PBMCLAIM_DECIMALQTY
0.00136986305005848 0 DECIMALQTY
PBM_CLAIM I_PBM_CLAIM_DATESBM_DESC
0.000923361047171056 0 DATESBM
PBM_CLAIM I_PBM_CLAIM_NDC
0.000100664386991411 0 PRODUCTID
PBM_CLAIM I_PBM_CLAIM_NDC9
0.000133049499709159 0 NDC9
PBM_CLAIM I_PBM_CLAIM_POSTED
0.000946073792874813 0 POSTED
PBM_CLAIM I_PBM_CLAIM_SRVPROVID
0.00826446246355772 0 SRVPROVID
PBM_CLAIM PK_PBMCLAIM
1.42382396006724E-6 0 ID
RXS_CLM_CALC_FEES FK_CCF_GRANTEEINVOICE
0.000546746829058975 0 GRANTEE_INVOICE
RXS_CLM_CALC_FEES FK_CLMCALCFEES_FEESTRUCT
0.000765110948123038 0 FEE_STRUCT
RXS_CLM_CALC_FEES FK_CLMCALCFEES_PHARMBILLGRPINVC
0.00026007802807726 0 INVC_PHARM_BILLGRP_INVC
RXS_CLM_CALC_FEES FK_RXSCLMCALCFEES_PBMCLAIM
1.42393344049196E-6 0 CLAIM_ID
RXS_CLM_CALC_FEES FK_RXSCLMCALCFEES_RXSINVOICE
0.000554323720280081 0 RXS_INVOICE
RXS_CLM_CALC_FEES I_RXSCLMCALCFEES_POSTED
6.03281841904391E-5 0 POSTED
RXS_CLM_CALC_FEES PK_RXSCLMCALCFEES
1.42393344049196E-6 0 CLAIM_ID

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Arno Brinkman
Sent: Thursday, November 30, 2006 4:35 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] FB 2.0 optimizer dogging us

Hi,

> 2.0 takes 8.8 seconds, 1.5 takes 0.11 seconds.
>
> 2.0
> PLAN SORT (JOIN (V_SUBACCT_CLAIMFEES CP NATURAL, V_SUBACCT_CLAIMFEES
> CPR INDEX (FK_PBMCLAIM_ACTCHCPHARMSUBACCT), V_SUBACCT_CLAIMFEES CCF
> INDEX
> (PK_RXSCLMCALCFEES)))
> 1.5
> PLAN SORT (JOIN (V_SUBACCT_CLAIMFEES CCF INDEX
> (FK_CCF_GRANTEEINVOICE),V_SUBACCT_CLAIMFEES CPR INDEX
> (PK_PBMCLAIM),V_SUBACCT_CLAIMFEES CP INDEX
> (PK_ACT_CHC_PHARM_SUBACCT)))

Did you run backup/restore before using in under FB2?
Can you check the statistics on the indexes for those tables?

Run this statement on both databases (1.5 and 2.0) and let know the
result:

SELECT
i.RDB$RELATION_NAME,
i.RDB$INDEX_NAME,
i.RDB$STATISTICS,
ins.RDB$FIELD_POSITION,
ins.RDB$FIELD_NAME,
ins.RDB$STATISTICS
FROM
RDB$INDICES i
JOIN RDB$INDEX_SEGMENTS ins ON (ins.RDB$INDEX_NAME = i.RDB$INDEX_NAME)
WHERE
i.RDB$RELATION_NAME in ('RXS_CLM_CALC_FEES', 'PBM_CLAIM',
'ACT_CHC_PHARM_SUBACCT') ORDER BY
i.RDB$RELATION_NAME, i.RDB$INDEX_NAME, ins.RDB$FIELD_POSITION


Firebird 2.0 should be faster or equal to 1.5 in most cases, but
ofcourse there are always exceptions.


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features
:
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Firebird and Interbase users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info

------------------------------------------------------------------------
--------
Mijn Postvak In wordt beschermd door SPAMfighter
954 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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




Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.