--- In firebird-support@yahoogroups.com, "karolbieniaszewski" <liviuslivius@...> wrote:

>
>
> --- In firebird-support@yahoogroups.com, "Jack Mills" <millsjack14@> wrote:
> >
> >
> > --- In firebird-support@yahoogroups.com, "karolbieniaszewski" <liviuslivius@> wrote:
> > >
> > >
> > > --- In firebird-support@yahoogroups.com, "Jack Mills" <millsjack14@> wrote:
> > > >
> > > >
> > > > --- In firebird-support@yahoogroups.com, "karolbieniaszewski" <liviuslivius@> wrote:
> > > > >
> > > > >
> > > > > --- In firebird-support@yahoogroups.com, "millsjack14" <millsjack14@> wrote:
> > > > > >
> > > > > > Hi
> > > > > >
> > > > > > I have identical tables in separate databases. when I run an update query on the table in one DB it takes 1 sec, on the other DB it takes 90 sec.
> > > > > >
> > > > > > Query
> > > > > > update disc set area_code = LTrim(area_code)
> > > > > >
> > > > > > Table disc has approx 62000 records
> > > > > > using firebird 1.5.6 on Windows XP
> > > > > >
> > > > > > Used IBExpert to check table data are identical & Indices are identical.
> > > > > >
> > > > > > Has anyone any idea why there is such a time difference .
> > > > > >
> > > > > > Thanks for any help
> > > > > >
> > > > > > Jack
> > > > > >
> > > > >
> > > > > 1. record versions - use sweep on both and update index statistics
> > > > > the best is do backup and restore and then check if time is the same
> > > > >
> > > > > 2. fragmentation on hard disk
> > > > >
> > > > > Karol Bieniaszewski
> > > > >
> > > >
> > > > Hi Karol
> > > >
> > > > Thanks for the response.
> > > > 1. Did backup & restore, updated index statistics no change still approx 90 sec to update.
> > > >
> > > > 2. Defragged the hard drive & update increased to approx 200 sec.
> > > >
> > > > seems to be associated with hard disk access?
> > > >
> > > > Jack
> > > >
> > >
> > > To test this try create empty partition and there put this restored databases
> > >
> > > How long now?
> > > If long
> > > Copy once again databases after restore process
> > > and try deactivate all indexes on this tables
> > > how long now?
> > >
> > > Karol Bieniaszewski
> > >
> > Hi Karol
> >
> > I copied the DB to another drive, update time 98 sec.
> > I deleted the copy & restored the DB to same second drive, update time 75 sec.
> > Dropped all indexes, update time 125 sec.
> > I restored the "Fast" DB to the second drive, update time < 1 sec.
> >
> > Jack
> >
>
> put here structure of your table with index definition
> and your update statement and plan returned for it
>
>
>
> Karol Bieniaszewski
>

Hi Karol

For infomation.
I tried another test, I dropped the disc table in the bad DB & copied the database object "disc" (using IBExpert) from the good DB but still has long update time.

Hopefully this is what you requested.

Jack

The update statement is "update disc set area_code = ltrim(area_code)"
The returned Plan "PLAN (DISC NATURAL)"

Table structure

SET SQL DIALECT 3;



/******************************************************************************/
/**** Tables ****/
/******************************************************************************/


CREATE GENERATOR DISCRETE_GEN;
CREATE GENERATOR DO_LAP_GEN;

CREATE TABLE DISC (
TAG_NO INTEGER NOT NULL,
AREA_CODE DNGRPS /* DNGRPS = VARCHAR(5) DEFAULT null */,
USER_CODE DNGRPS /* DNGRPS = VARCHAR(5) DEFAULT null */,
USER_SUB DNGRPS /* DNGRPS = VARCHAR(5) DEFAULT null */,
FILE1 DNINT /* DNINT = INTEGER DEFAULT null */,
BYTE DNINT /* DNINT = INTEGER DEFAULT null */,
BIT DNSINT /* DNSINT = SMALLINT DEFAULT null */,
S5DW DNINT /* DNINT = INTEGER DEFAULT null */,
S5BIT DNSINT /* DNSINT = SMALLINT DEFAULT null */,
NAME DNNAMES /* DNNAMES = VARCHAR(35) DEFAULT null */,
TAG DNNAMES /* DNNAMES = VARCHAR(35) DEFAULT null */,
TMR_NO DNINT /* DNINT = INTEGER DEFAULT null */,
EM_CONFIGRD DNBOOL NOT NULL /* DNBOOL = VARCHAR(1) DEFAULT 'F' NOT NULL CHECK(value in ('F', 'T')) */,
EM_NO DNSINT /* DNSINT = SMALLINT DEFAULT null */,
CM_NO DNSINT /* DNSINT = SMALLINT DEFAULT null */,
EM_CM_COM DNGRPS /* DNGRPS = VARCHAR(5) DEFAULT null */,
IO_ADDR DNVAR10 /* DNVAR10 = VARCHAR(10) DEFAULT null */,
MAGUS_TAG DNBOOL /* DNBOOL = VARCHAR(1) DEFAULT 'F' NOT NULL CHECK(value in ('F', 'T')) */,
RECCO_TAG DNBOOL /* DNBOOL = VARCHAR(1) DEFAULT 'F' NOT NULL CHECK(value in ('F', 'T')) */,
INITIAL_STATE DNBOOL /* DNBOOL = VARCHAR(1) DEFAULT 'F' NOT NULL CHECK(value in ('F', 'T')) */,
NOTES DNDESC /* DNDESC = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */,
INIT_RQD DNBOOL NOT NULL /* DNBOOL = VARCHAR(1) DEFAULT 'F' NOT NULL CHECK(value in ('F', 'T')) */
);




/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/

ALTER TABLE DISC ADD CONSTRAINT PK_DISC_1 PRIMARY KEY (TAG_NO);


/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE INDEX DISC_IDX1 ON DISC (AREA_CODE, NAME);
CREATE INDEX DISC_IDX3 ON DISC (USER_CODE, NAME);
CREATE INDEX DISC_IDX4 ON DISC (IO_ADDR, BIT, BYTE, FILE1);


/******************************************************************************/
/**** Triggers ****/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/



/* Trigger: "SET_OL/USR_DEFINED_D" */
CREATE OR ALTER TRIGGER "SET_OL/USR_DEFINED_D" FOR DISC
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.TAG_NO IS NULL)
THEN BEGIN
IF (NEW.AREA_CODE = 'USR')
THEN BEGIN
NEW.TAG_NO = GEN_ID(discrete_gen,1);
NEW.name = 'USR Defined Tag ' ||GEN_ID(discrete_gen,0);
END
IF (NEW.AREA_CODE = 'OL')
THEN BEGIN
NEW.TAG_NO = GEN_ID(do_lap_gen,1);
NEW.name = 'OverLap Tag ' ||GEN_ID(do_lap_gen,0);
NEW.notes = 'A Tag That References the Analog Table';
END
END
END
^


SET TERM ; ^