Hi all,

I have a bde application that I have finally gotten ported to ibo (using the
bde emulation components). I need to know what I need to look for in the case
of performance issues.

I have noticed that queries such as those used to create reports can take say
240 seconds on ibo, whereas they took maybe 5 seconds on the bde. I quickly
re-did the report on ibexpress just to see, and its just as fast as the
bde...so something is definetly amiss. Its almost as if ibo is not using the
indexes maybe?

This is an extreme case but I have many like it. Note that I am using c++
builder 6, quick reports pro 4.06, firebird 1.0 in dialect 1, and ibo 4.7.16:

In this example the query is this:

FairRepData->ConDueDriverQ1->Close();
FairRepData->ContDueQ1->Close();
FairRepData->ConDueDriverQ1->Open();
FairRepData->ContDueQ1->Open();
ContractForm1->ContractRep1->PrinterSettings->PrinterIndex = -1;
ContractForm1->ContractRep1->Print();
FairRepData->ContDueQ1->Close();
FairRepData->ConDueDriverQ1->Close();

The sql I am using is this:


--------------------------------------------------------------

ConDueDriver for the detail (about 150 drivers):

SELECT Contractor_due.DRIVER_NO, (Drivers.FNAME || ' ') || Drivers.LNAME as
FULLNAME, Drivers.FNAME, Drivers.LNAME, Drivers.HIRE_DATE
FROM CONTRACTOR_DUE Contractor_due
INNER JOIN DRIVERS Drivers
ON (Contractor_due.DRIVER_NO = Drivers.DRIVER_NO)
ORDER BY Drivers.DRIVER_NO

Drivers is a table...driver_no is the primary key

Contractor_due is a view...

The view contains simply:

CREATE VIEW CONTRACTOR_DUE(
DRIVER_NO)
AS
select distinct driver_no from deducts

WHERE
(BALANCE_DUE > 0)

Deducts is a table...and driver_no is indexed, balance_due is indexed and
defaults to 0.

--------------------------------------------------------
ContDueQ1 for the sub detail for each driver (usually returns an average of 5
items):

Deducts is a table...and driver_no is indexed, balance_due is indexed and
defaults to 0.

SELECT DESCRIPTION, INVOICE_NO, TX_DATE, BALANCE_DUE
FROM DEDUCTS Deducts
Where
(BALANCE_DUE > 0)
AND (DRIVER_NO = :DRIVER_NO)
ORDER BY TX_DATE, DESCRIPTION, INVOICE_NO, BALANCE_DUE


=========================================================
Also on regular updates, I see little or no difference over the lan, but in my
remote site over the T1, they say single can take 2-3 seconds to update on ibo,
whereas it appeared instant to them on bde...so there is definetly a general
performance issue as apposed to a quickreports one.



thanks for any ideas,

Randal


The table create statement (again this is dialect 1...so date=timestamp).


CREATE TABLE DRIVERS (
DRIVER_NO SMALLINT NOT NULL,
NAME VARCHAR(50),
NAME2 VARCHAR(50),
DOB DATE,
SSN CHAR(11),
HIRE_DATE DATE,
EQUIP_NO SMALLINT,
SECONDARY CHAR(1),
SKEY CHAR(1),
DRIVERS_LIC VARCHAR(20),
LIC_ST CHAR(2),
LIC_EXP_DATE DATE,
ADDRESS1 VARCHAR(30),
ADDRESS2 VARCHAR(30),
CITY VARCHAR(25),
STATE CHAR(2),
ZIP VARCHAR(10),
PHONE1 VARCHAR(12),
PHONE2 VARCHAR(12),
CELLULAR VARCHAR(12),
DOCTOR VARCHAR(35),
PHYS_DATE DATE,
PHYS_EXP_DATE DATE,
CORR_LENSES CHAR(1),
LOAD_NO INTEGER,
DELIVERY_DATE DATE,
PU_AREA SMALLINT,
PU_CITY VARCHAR(30),
PU_STATE VARCHAR(2),
DE_AREA SMALLINT,
DE_CITY VARCHAR(30),
DE_STATE VARCHAR(2),
COMMENT VARCHAR(80),
UNLOADED_TIME DATE,
CALLED_IN_LAST DATE,
CALLED_IN_LAST_TIME DATE,
CALLED_IN_FROM VARCHAR(45),
WHERES VARCHAR(80),
MESSAGES VARCHAR(240),
DAYS_AVAILABLE SMALLINT,
DAYS_LOADED SMALLINT,
DAYS_HOME SMALLINT,
DAYS_OFF SMALLINT,
DAYS_IN_SHOP SMALLINT,
MARITAL_STATUS VARCHAR(20),
SPOUSE_NAME VARCHAR(40),
DRIVERS_HANDLE VARCHAR(35),
RELATIVE1 VARCHAR(40),
RELATIVE2 VARCHAR(40),
RELATIVE3 VARCHAR(40),
RELATIVE4 VARCHAR(40),
RELATIVE5 VARCHAR(40),
COMMENTS VARCHAR(250),
DIMAGE BLOB SUB_TYPE 0 SEGMENT SIZE 80,
FNAME VARCHAR(25),
LNAME VARCHAR(25),
FNAME2 VARCHAR(25),
LNAME2 VARCHAR(25),
AUTHFLAG CHAR(1),
QUIT_TYPE VARCHAR(11),
QUIT_DATE DATE,
QUIT_COMMENTS VARCHAR(160),
MILLION_MILE DATE
);


-----------------------------------------------------------------------


CREATE TABLE DEDUCTS (
DRIVER_NO SMALLINT NOT NULL,
DESCRIPTION VARCHAR(50),
INVOICE_NO VARCHAR(30),
AMOUNT NUMERIC(9,2),
FEE NUMERIC(9,2),
TX_DATE DATE,
BALANCE_DUE NUMERIC(9,2),
PAYMENT NUMERIC(9,2),
THIS_WEEK CHAR(1),
RECEIPT CHAR(1),
DATE_PAID_OUT DATE,
DUCTKEY INTEGER NOT NULL
);