Howdy,

Any suggestions for performance improvements will be much appreciated. I
wish ibo would make better plans on its own for instance....I must be
doing something wrong here or ibo really don't make good plans :).

I got one report down to 2.5 seconds now on ibo, but it took some work.

I had to turn on auto fetch all, then I had to specify a plan, and
obviously the bde picked a better plan. Even with that I only got it down
to 22 seconds.

With a query re-write, I get it down to 6 seconds ...that is had to do
away with the way I was doing the primary query. The following query
instead. The following query on the bde gets it down to 3 seconds...so I
thought I still had some problems in my speed optimizing since ibo was now
only half as slow as the bde:


SELECT Drivers.DRIVER_NO, (Drivers.FNAME || ' ') || Drivers.LNAME as
FULLNAME, Drivers.FNAME, Drivers.LNAME, Drivers.HIRE_DATE
FROM Drivers

where exists (select distinct driver_no from deducts
where
(BALANCE_DUE > 0)
AND (DESCRIPTION NOT STARTING WITH 'TRUCK')
AND (DESCRIPTION NOT STARTING WITH 'I R S')
AND (DESCRIPTION NOT STARTING WITH 'BANK')
AND (DESCRIPTION NOT STARTING WITH 'FOR SAVINGS')
AND (DESCRIPTION NOT STARTING WITH 'DONATION')
AND (DESCRIPTION NOT STARTING WITH 'REPAIRS')
AND (DESCRIPTION NOT STARTING WITH 'HWY USE TAX')
and (deducts.driver_no = Drivers.driver_no)
PLAN (DEDUCTS INDEX (DEDUCTSINDEX1))
)

PLAN (DRIVERS ORDER RDB$PRIMARY3)
ORDER BY Drivers.DRIVER_NO


I then specified this on the subquery, and I got it down to 2.5
seconds...just barely edging out the bde...again the bde picked a better
plan on its own no doubt:

I also had to specify this plan on the subquery:

SELECT DESCRIPTION, INVOICE_NO, TX_DATE, BALANCE_DUE
FROM DEDUCTS Deducts
Where
(BALANCE_DUE > 0)
AND (DESCRIPTION NOT STARTING WITH 'TRUCK')
AND (DESCRIPTION NOT STARTING WITH 'I R S')
AND (DESCRIPTION NOT STARTING WITH 'BANK')
AND (DESCRIPTION NOT STARTING WITH 'FOR SAVINGS')
AND (DESCRIPTION NOT STARTING WITH 'DONATION')
AND (DESCRIPTION NOT STARTING WITH 'REPAIRS')
AND (DESCRIPTION NOT STARTING WITH 'HWY USE TAX')
AND (DRIVER_NO = :DRIVER_NO)
PLAN SORT ((DEDUCTS INDEX (DEDUCTSINDEX1)))
ORDER BY TX_DATE, DESCRIPTION, INVOICE_NO, BALANCE_DUE



any ideas, confirmations, advice will be much appreciated.


thanks,
Randal


A couple more questions: With all this re-writing...will going native
instead of using the tdataset compatible stuff where I can do so help even
more? Can I expect the native datasource stuff to be compatible with
quick reports and the like?

On Wed, 27 Jun 2007, Randal W. Carpenter wrote:

> 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
> );
>