I sent this email Friday am... not really sure why it didnt make it up
to Yahoo land !!
Thanks for any further help you can give on this pesky situation.

r

-------- Original Message --------
Subject: Re: [firebird-support] FB 1.5 Slooooo / stalls large insert -
Win2003 server - W DDL Details
Date: Fri, 04 Nov 2005 11:46:17 -0800
From: Rich Pinder <rpinder@...>
To: firebird-support@yahoogroups.com
References: <436B831A.8000305@...>



Thanks Nick and Steve.

Table details inserted at end of email (sorry its long...i've remove all
the Yahooey footers from the thread).
I should mention - although a secondary index on this table is defined
(for 'ID'), it is set to NOT ACTIVE during all these batch 'insert'
attempts.

Triggers - there is only one trigger on the table, and it does indeed
fire 'on insert'. Its the simple 'unique recno' type of logic:

begin
if (new.RECNO is null) then
new.RECNO = gen_id(G_AUTO_INC_RECNO, 1);
end

>From the 'ODS' post from yesterday, I will mention again that I've got

two parallel systems - and I've gone thru to be sure the DDL is
consistent between them both.

I'm even considering THIS idea: Shutdown my local laptop devel machine,
move off its database file somewhere safe, RESTORE onto the laptop from
the SERVER backup that Alexandre's suggested steps yesterday included,
restart the laptop, then TRY the update again from this machine. If it
runs and completes... then I'll know there is something specific about
the FB install, or the hdw, on the server machine.

[re'reading this, it is a bunch of work that may end up provin i'm
barkin up the wrong tree !]

Ok... again... see the end of the email for the full DDL from the
offending system.
And I thank you again for any thoughts. Gonna be a LONG weekend, I can
see now !

Rich

Rich Pinder wrote:

>Hello again,
>
>This is a follow up post after yesterdays 'Understanding ODS' thread.
>
>I have an inefficient, iterative program that inserts data into a table
>(200,000 records + ... committing after EACH one)
>It works fine on my slow XP laptop develop machine.
>After backing up / restoring on the production server (which brought it
>up to ODS 1.1 - Thanks Alexandre), I tried again - and the process kind
>of stalls at some point. Its odd, after running all night, it stalls
>now at a mere 6000 records - yesterday it was kind of slowing at
>around 30,000 or so after hours of running.
>
>This morning I requested the database statistics, and it took
>forever..but finally returned. I have the info saved if anyone suggests
>reviewing a certain portion.
>
>Here are some facts:
>
>- I've tried running the 'app' both across the network and also from the
>server machine itself
>- server running Win2003 server (and is behaving in all other areas I
>can see..nothing suspicious listed in the 'event viewer' etc)
>- FB 1.5 remains running (thankfully) on the production server - I can
>do selects against all the tables (including the one that seems to be
>stalled, btw).
>- memory and disk on server seems fine
>
>Any thoughts of where to look next would be greatly appreciated.
>Thanks
>Rich Pinder
>USC School of Medicine
>
>
>

Metadata:

>> /*******************************************************************************
>> * Selected metadata objects * ------------------------- * Extracted
>> at 11/4/2005 11:41:09 AM
>> ******************************************************************************/
>> /*******************************************************************************
>> * Generators * ---------- * Extracted at 11/4/2005 11:41:09 AM
>> ******************************************************************************/
>> CREATE GENERATOR G_AUTO_INC_BIOGROUP; CREATE GENERATOR G_AUTO_INC_PW;
>> CREATE GENERATOR G_AUTO_INC_RECNO;
>> /*******************************************************************************
>> * Tables * ------ * Extracted at 11/4/2005 11:41:09 AM
>> ******************************************************************************/
>> RECREATE TABLE ADDRESS ( RECNO FLOAT NOT NULL, ID CHAR( 6) NOT NULL
>> COLLATE NONE, ADD1 VARCHAR( 30) COLLATE NONE, ADD2 VARCHAR( 30)
>> COLLATE NONE, CITY VARCHAR( 30) COLLATE NONE, ZIP5 CHAR( 5) COLLATE
>> NONE, ZIP4 CHAR( 4) COLLATE NONE, SOURCE CHAR( 2) COLLATE NONE, STATE
>> CHAR( 2) COLLATE NONE, VALDATE DATE, CHGDATE DATE, OPERATOR CHAR( 2)
>> COLLATE NONE, RECNOOLD FLOAT, CURR CHAR( 1) COLLATE NONE, CONSTRAINT
>> PK_ADDRESS PRIMARY KEY (RECNO) ); update rdb$relations set
>> rdb$description = 'Address - starting 2/05 with Current only adds.
>> for entire cohort. ' where rdb$relation_name = 'ADDRESS'; RECREATE
>> TABLE BIOGROUP ( RECNO FLOAT NOT NULL, ID CHAR( 6) NOT NULL COLLATE
>> NONE, ADD1 CHAR( 30) COLLATE NONE, ADD2 CHAR( 30) COLLATE NONE, CITY
>> CHAR( 30) COLLATE NONE, ZIP5 CHAR( 5) COLLATE NONE, ZIP4 CHAR( 4)
>> COLLATE NONE, SOURCE CHAR( 2) COLLATE NONE, LN CHAR( 30) COLLATE
>> NONE, FN CHAR( 20) COLLATE NONE, MID CHAR( 20) COLLATE NONE,
>> STATE_COD CHAR( 2) COLLATE NONE, VALDATE DATE, CHGDATE DATE, OPERTOR
>> CHAR( 2) COLLATE NONE, CONSTRAINT PK_BIOGROUP PRIMARY KEY (RECNO) );
>> update rdb$relations set rdb$description = 'This should be now
>> description for the RECNO column ' where rdb$relation_name =
>> 'BIOGROUP'; RECREATE TABLE BIOSPEC ( RECNO FLOAT NOT NULL, DATEDRAW
>> DATE, TIMEDRAW TIME, TRACKNUM VARCHAR( 20) COLLATE NONE, TS TIMESTAMP
>> DEFAULT CURRENT_TIMESTAMP, DATESHIP DATE, STUDY CHAR( 3) COLLATE
>> NONE, ID CHAR( 6) NOT NULL COLLATE NONE, NUMTUBES CHAR( 2) COLLATE
>> NONE, SENDTYPE CHAR( 2) COLLATE NONE, LEFTMW CHAR( 2) COLLATE NONE,
>> TIMEATE TIME, UCODE CHAR( 2) COLLATE NONE, SAMPTYPE CHAR( 1) COLLATE
>> NONE, DATEBUCC DATE, DATECONS DATE, DNALIMIT CHAR( 1) COLLATE NONE,
>> CONSTRAINT PK_BIOSPEC PRIMARY KEY (RECNO) ); RECREATE TABLE GETON (
>> RECNO FLOAT NOT NULL, UCODE CHAR( 3) NOT NULL COLLATE NONE, STUDYCODE
>> CHAR( 3) NOT NULL COLLATE NONE, CONSTRAINT PK_GETON PRIMARY KEY
>> (RECNO) ); RECREATE TABLE ID ( ID CHAR( 6) NOT NULL COLLATE NONE,
>> CONSTRAINT PK_ID PRIMARY KEY (ID) ); RECREATE TABLE "NAME" ( RECNO
>> FLOAT NOT NULL, ID CHAR( 6) NOT NULL COLLATE NONE, SOURCE CHAR( 2)
>> COLLATE NONE, LN VARCHAR( 30) COLLATE NONE, FN VARCHAR( 20) COLLATE
>> NONE, MID VARCHAR( 20) COLLATE NONE, VALDATE DATE, CHGDATE DATE,
>> OPERATOR CHAR( 2) COLLATE NONE, RECNOOLD FLOAT, CURR CHAR( 1) COLLATE
>> NONE, CONSTRAINT PK_NAME PRIMARY KEY (RECNO) ); update rdb$relations
>> set rdb$description = 'This should be now description for the RECNO
>> column ' where rdb$relation_name = 'NAME'; RECREATE TABLE PHONE (
>> RECNO FLOAT NOT NULL, ID CHAR( 6) COLLATE NONE, PHONE VARCHAR( 18)
>> COLLATE NONE, SOURCE CHAR( 2) COLLATE NONE, RECNOADD FLOAT, TS
>> TIMESTAMP DEFAULT CURRENT_TIMESTAMP, NAMELIST VARCHAR( 30) COLLATE
>> NONE, CONSTRAINT PK_PHONE PRIMARY KEY (RECNO) ); update rdb$relations
>> set rdb$description = 'Phones from reach411 for study001 (first
>> batch, from 2/05)' where rdb$relation_name = 'PHONE'; RECREATE TABLE
>> PHONEOLD ( RECNO FLOAT NOT NULL, ID CHAR( 6) COLLATE NONE, PHONE
>> VARCHAR( 18) COLLATE NONE, SOURCE CHAR( 2) COLLATE NONE, TS TIMESTAMP
>> DEFAULT CURRENT_TIMESTAMP, CONSTRAINT PK_PHONEOLD PRIMARY KEY (RECNO)
>> ); update rdb$relations set rdb$description = 'Old phone numbers, but
>> ONLY for the Study001 FIRST selection - ie Feb 2005 ' where
>> rdb$relation_name = 'PHONEOLD'; RECREATE TABLE STUDIES ( RECNO FLOAT
>> NOT NULL, STUDYCODE CHAR( 3) NOT NULL COLLATE NONE, STUDYTYPE CHAR(
>> 3) COLLATE NONE, STUDYNAME VARCHAR( 70) COLLATE NONE, STUDYBEGIN
>> DATE, STUDYEND DATE, FUNDINGSRC VARCHAR( 30) COLLATE NONE, FUNDACCT
>> VARCHAR( 20) COLLATE NONE, CONSTRAINT PK_STUDIES PRIMARY KEY (RECNO)
>> ); RECREATE TABLE STUDY001 ( RECNO FLOAT NOT NULL, ID CHAR( 6)
>> COLLATE NONE, BIRTHX DATE, COUNTY VARCHAR( 25) COLLATE NONE,
>> CANCREGTEX VARCHAR( 20) COLLATE NONE, RACEC CHAR( 1) COLLATE NONE,
>> CASECONT CHAR( 1) COLLATE NONE, STUDY CHAR( 3) COLLATE NONE, CENTER
>> CHAR( 4) COLLATE NONE, DTDX DATE, CONSTRAINT PK_STUDY001 PRIMARY KEY
>> (RECNO) ); RECREATE TABLE STUDYTYPE ( RECNO FLOAT NOT NULL, STUDYTYPE
>> CHAR( 3) COLLATE NONE, DESCR VARCHAR( 30) COLLATE NONE, CONSTRAINT
>> PK_STUDYTYPE PRIMARY KEY (RECNO) ); RECREATE TABLE TEMPIDS ( RECNO
>> FLOAT NOT NULL, ID CHAR( 6) COLLATE NONE, CONSTRAINT PK_TEMPIDS
>> PRIMARY KEY (RECNO) ); RECREATE TABLE TEMPIDSSM ( RECNO FLOAT NOT
>> NULL, ID CHAR( 6) COLLATE NONE, CONSTRAINT PK_TEMPIDSSM PRIMARY KEY
>> (RECNO) ); RECREATE TABLE USERCODE ( RECNO FLOAT NOT NULL, FN
>> VARCHAR( 20) COLLATE NONE, UCODE CHAR( 2) COLLATE NONE, SGROUP CHAR(
>> 2) COLLATE NONE, CENTER CHAR( 2) COLLATE NONE, LN VARCHAR( 30)
>> COLLATE NONE, RETIRED CHAR( 1) COLLATE NONE, WPHONE CHAR( 14) COLLATE
>> NONE, EMAIL VARCHAR( 50) COLLATE NONE, CELLPHONE CHAR( 14) COLLATE
>> NONE, PROVIDER VARCHAR( 12) COLLATE NONE, HPHONE CHAR( 14) COLLATE
>> NONE, PW VARCHAR( 20) DEFAULT USER COLLATE NONE, CONSTRAINT
>> PK_USERCODE PRIMARY KEY (RECNO) );
>> /*******************************************************************************
>> * Indices * ------- * Extracted at 11/4/2005 11:41:10 AM
>> ******************************************************************************/
>> CREATE ASC INDEX I_ADDRESS_ID ON ADDRESS (ID); ALTER INDEX
>> I_ADDRESS_ID INACTIVE; CREATE ASC INDEX I_BIOGROUP_ ON BIOGROUP (ID);
>> CREATE ASC INDEX I_BIOSPEC_DATESHIP ON BIOSPEC (DATESHIP); CREATE
>> UNIQUE ASC INDEX I_BIOSPEC_ID ON BIOSPEC (ID); CREATE ASC INDEX
>> I_NAME_ID ON "NAME" (ID); CREATE ASC INDEX I_PHONEOLD_ID ON PHONEOLD
>> (ID); CREATE ASC INDEX I_PHONE_ID ON PHONE (ID); CREATE ASC INDEX
>> I_STUDY001_ID ON STUDY001 (ID); CREATE ASC INDEX I_TEMPIDS_ID ON
>> TEMPIDS (ID); CREATE ASC INDEX I_USERCODE_UCODE ON USERCODE (UCODE);
>> /*******************************************************************************
>> * Triggers * -------- * Extracted at 11/4/2005 11:41:10 AM
>> ******************************************************************************/
>> /*******************************************************************************
>> * Trigger T_ADDRESS_INS_RECNO on table/view ADDRESS
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_ADDRESS_INS_RECNO FOR ADDRESS ACTIVE
>> BEFORE INSERT POSITION 0 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_BIOSPEC_INS_RECNO on table/view BIOSPEC
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_BIOSPEC_INS_RECNO FOR BIOSPEC ACTIVE
>> BEFORE INSERT POSITION 0 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_BIOSPEC_SAMPTYPE on table/view BIOSPEC
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_BIOSPEC_SAMPTYPE FOR BIOSPEC ACTIVE
>> BEFORE UPDATE POSITION 1 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if ((new.SAMPTYPE is null) and
>> (new.UCODE is not NULL) and (new.SENDTYPE is not NULL)) then
>> new.SAMPTYPE = '1'; end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_BIOSPEC_TS on table/view BIOSPEC
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_BIOSPEC_TS FOR BIOSPEC ACTIVE BEFORE
>> UPDATE POSITION 0 AS /* You can change this template in the template
>> editor: File | Preferences | Object Templates Trigger: Author : ,
>> Date : Purpose : */ begin new.TS = current_timestamp; end ^^ SET TERM
>> ; ^^
>> /*******************************************************************************
>> * Trigger T_GETON_INS_RECNO on table/view GETON
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_GETON_INS_RECNO FOR GETON ACTIVE
>> BEFORE INSERT POSITION 0 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_NAME_INS_RECNO on table/view NAME
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_NAME_INS_RECNO FOR "NAME" ACTIVE
>> BEFORE INSERT POSITION 0 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_PHONE_INS_RECNO on table/view PHONE
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_PHONE_INS_RECNO FOR PHONE ACTIVE
>> BEFORE INSERT POSITION 0 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_PHONEOLD_INS_RECNO on table/view PHONEOLD
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_PHONEOLD_INS_RECNO FOR PHONEOLD ACTIVE
>> BEFORE INSERT POSITION 0 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_STUDIES_INS_RECNO on table/view STUDIES
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_STUDIES_INS_RECNO FOR STUDIES ACTIVE
>> BEFORE INSERT POSITION 0 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_STUDY001_INS_RECNO on table/view STUDY001
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_STUDY001_INS_RECNO FOR STUDY001 ACTIVE
>> BEFORE INSERT POSITION 0 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_STUDYTYPE_INS_RECNO on table/view STUDYTYPE
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_STUDYTYPE_INS_RECNO FOR STUDYTYPE
>> ACTIVE BEFORE INSERT POSITION 0 AS /* You can change this template in
>> the template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_TEMPIDS_INS_RECNO on table/view TEMPIDS
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_TEMPIDS_INS_RECNO FOR TEMPIDS ACTIVE
>> BEFORE INSERT POSITION 0 AS /* You can change this template in the
>> template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Trigger T_USERCODE_INS_RECORD on table/view USERCODE
>> ******************************************************************************/
>> SET TERM ^^ ; CREATE TRIGGER T_USERCODE_INS_RECORD FOR USERCODE
>> ACTIVE BEFORE INSERT POSITION 0 AS /* You can change this template in
>> the template editor: File | Preferences | Object Templates Trigger:
>> Author : , Date : Purpose : */ begin if (new.RECNO is null) then
>> new.RECNO = gen_id(G_AUTO_INC_RECNO, 1); end ^^ SET TERM ; ^^
>> /*******************************************************************************
>> * Roles * ----- * Extracted at 11/4/2005 11:41:10 AM
>> ******************************************************************************/
>> GRANT PUBLIC TO ASTABOYS;
>
>
>
>