Thanks Helen,

The conclusion is that with correctly written procedures and sql, IB
does well.

The error is in my sql.

Hans has given me some help with the report queries, so I
will "polish" a few reports and get back to you.

Thanks again,

John Newcombe

john@...

--- In ib-support@y..., Helen Borrie <helebor@d...> wrote:
> At 01:41 AM 01-10-01 +0000, you wrote:
> >Thanks Hans and all,
> >
> >My customers databases have grown and as the volume of data grows
and
> >complexity of reports - I quite often get no result - (either
blown
> >temp files or 6+ hrs later the server is still at 100%)
>
> Blown temp files? So - what are the set sizes of your temp files?
>

Why should I have to set temp file sizes?

>
> >In one update, using the query before mentioned, at my customers
site
> >(in Sydney with me in Florida) it took 3 1/2 days ... I told them
to
> >hang in there ... they did ... but I expected less than a day.
>
> That correlated update you posted initially? I have played with
that every which way and I don't believe it is possible...are you
certain that your "so-fast" MSSQL query actually performed anything?
Or did you use some different statement with MSSQL?

I double checked the query on MS SQL.

You are correct not all revenue_no 's were updated on the MS_SQL!!!

>
>
> >I have settled on ditching IB - thanks for all your help - but I
have
> >to give fast results - in minimum time and after 5 years with IB -
> >and all the tools and selling I have done - that's it - no more -
my
> >credibility is shot - my customers blame me/my company for poor db
> >response - not IB or MS SQL.
>
> The sample given is surely not your "decider" on fast results! Are
you saying that you would be updating this ENROL structure in this
way in a production situation? If so, I don't believe you have
resolved the many:many relationship between STUDENT and CRT_NO in a
way which can be stable...your problems are but a symptom of an
incomplete data model. See my comments below. This has the
hallmarks of a spoof demo.

My frustration is that I have found IB to be extremely slow.

Primarily in complex reports.

In order to improve the performance of the report queries, I tried to
de-normalise the table structure.

I added the revenue_no to the enrol table for that purpose.

I was further frustrated/thwarted with IB, when I found the update
query took an immense amount of time.

>
> I don't know how you are populating this ENROL table...Between
STUDENT and REVENUE_FACULTY you have an ambiguous relationship. I
think you set up ENROL to try and resolve the fact that, although a
STUDENT can hold only one value for OS, it can have more than one
value for CRT_NO. Resolution of this can be done by making ENROL an
intersection of REVENUE_FACULTY and STUDENT.
>

Each student must have one or more enrolments. (one to many)

Each student is either local or overseas (os = 0 or 1)

Each enrolment has faculty (crt_no) (ie one to one)

(This was and earlier de-normalisation as for each enrolment there is
a course and each course has the faculty (crt_no) defined.

The Revenue_Faculty table enables each faculty to be combined with
type of student OS (local or overseas) to define what I call -
Revenue_faculty (or revenue streams - ie income for faculty and type
of student local/os ).

So the revenue_faculty is populated with values for each OS an crt_no
so whenever a faculty is added or changed the revenue_faculty table
is repopulated to ensure integrity.

So yes there is a revenue_no for each crt_no and OS value in the
table.

> Now, the current structure aside, you have (from somewhere) a table
(let's call it X) with a structure something like this:
> STUD_NO integer
> CRT_NO integer
>

This is stored in the enrol table - each enrolment identifies the
student and the crt_no

> Because a STUDENT can have multiple CRT_NOs, I assume there is a
unique constraint on STUD_NO and CRT_NO in table X.
>
> Your REVENUE_FACULTY table uniquely identifies each and every
possible combination of OS and CRT_NO. Therefore, your intersection
table needs only the primary key of REVENUE_FACULTY (i.e REVENUE_NO)
to represent each student's OS & CRT_NO combinations. There must be
a unique constraint over STUD_NO and REVENUE_NO to ensure that each
possible combination has but a single instance.
>
> OK, now, first, to build the ENROL table we need to be sure that
every existing STUDENT and X row is represented by a combination in
REVENUE_FACULTY. With Borland InterBase, you need to do this in a
stored procedure (and I'm assuming that you have a triggered
generator for REVENUE_NO):
>
> CREATE PROCEDURE CLEAN_UP_REVENUE_FACULTY
> AS
> DECLARE VARIABLE VSTUD_NO INTEGER;
> DECLARE VARIABLE VOS INTEGER;
> DECLARE VARIABLE VCRT_NO INTEGER;
> BEGIN
> FOR SELECT S.STUD_NO, S.OS
> X.CRT_NO
> FROM STUDENT JOIN X
> ON S.STUD_NO = X.STUD_NO
> INTO :VSTUD_NO, :VOS, :VCRT_NO
> DO BEGIN
> IF ( NOT ( EXISTS (SELECT * FROM REVENUE_FACILITY WHERE OS
= :VOS
> AND CRT_NO = :VCRT_NO))) THEN
> INSERT INTO REVENUE_FACILITY ( OS, CRT_NO)
> VALUES (:VOS, :CRT_NO);
> END
>
> Now, we want to create the rows for the intersection table. Let's
call it ENROL. Here is its structure:
>
> CREATE TABLE ENROL(
> ENROL_ID INTEGER NOT NULL,
> STUD_NO INTEGER NOT NULL,
> REVENUE_NO NOT NULL,
> CONSTRAINT PRIMARY KEY (ENROL_ID),
> CONSTRAINT UNIQUE (STUD_NO, REVENUE_NO)
> );
>
> We have a generator for ENROL_ID and a BI trigger to fire it.
>
> Here's how we populate it:
>
> INSERT INTO ENROL ( STUD_NO, REVENUE_NO )
> SELECT S.STUD_NO, RF.REVENUE_NO
> FROM STUDENT S
> JOIN X X ON S.STUD_NO = X.STUD_NO
> JOIN REVENUE_FACULTY RF
> ON S.OS = RF.OS
> AND X.CRT_NO = RF.CRT_NO;
>
> Now, we drop CRT_NO from table X and OS from STUDENT:
>
> ALTER TABLE X DROP CRT_NO;
> ALTER TABLE STUDENT DROP OS;
>
> From here on, any new rows added to ENROL and REVENUE_FACULTY
should be placed under application control, supported by referential
integrity triggers.
>
> I stress that I'm GUESSING about where you were getting the data
for your ENROL table.
>
> --------------------------------------------------------------------
---------------------------------------------------------
>
> Now, going back to your original requirement - to update the
REVENUE_NO in your ENROL table. I changed some stuff, most notably
the indexing. Notice the two high-selectivity indexes I added.
>
> I ran two different (mutually exclusive) SPs over Carl van Trast's
data in your (supposed) tables. Both took just under 3 seconds to
complete on a 500 MHz P2 on NT4 SP 6, over Firebird 1 Beta 2. I have
two TMP_DIRECTORY files set up in ibconfig, each 6 Mb.
>
> Notice that in each case the procedure updated exactly one-third of
the total 10,000 rows - a reflection of the fact that the
REVENUE_FACULTY table contains only 1 out of the possible 3 sets of
permutations of 3 OS and 10 CRT_NO values.
>
> I used IB_SQL built with IB Objects V.4.2.Eg. I've pasted in the
execution sequences from the trace log.
> --------------------------------------------------------------------
-----------------------------------------
> CREATE TABLE REVENUE_FACULTY (
> REVENUE_NO INTEGER NOT NULL,
> OS INTEGER NOT NULL,
> CRT_NO INTEGER NOT NULL,
> CONSTRAINT U_CRT_NO_OS UNIQUE ( CRT_NO, OS ), /* CONSTRAINT
INTEG_11 */
> PRIMARY KEY ( REVENUE_NO )
> );
>
> CREATE TABLE STUDENT (
> STUD_NO INTEGER NOT NULL,
> OS INTEGER NOT NULL,
> CONSTRAINT PRIMARY KEY ( STUD_NO )
> );
>
> /* This index raises the selectivity on column OS */
> CREATE INDEX IX_OS_STUD_NO ON STUDENT ( OS, STUD_NO );
>
> CREATE TABLE ENROL (
> ENROL_ID INTEGER NOT NULL,
> REVENUE_NO INTEGER,
> STUD_NO INTEGER,
> CRT_NO INTEGER,
> CONSTRAINT PRIMARY KEY ( ENROL_ID )
> );
>
> /* This index raises the selectivity of CRT_NO */
> CREATE INDEX IX_ENROL_CRT_NO_STUD_NO ON ENROL(CRT_NO, STUD_NO);
>
> /* From Carl van Tast, altered to reflect different field order */
> CREATE PROCEDURE REVENUE_FACULTY_FILL AS
> BEGIN
> INSERT INTO revenue_faculty VALUES (123456, 1, 1);
> INSERT INTO revenue_faculty VALUES (123457, 2, 2);
> INSERT INTO revenue_faculty VALUES (123458, 1, 3);
> INSERT INTO revenue_faculty VALUES (123459, 2, 4);
> INSERT INTO revenue_faculty VALUES (123460, 3, 5);
> INSERT INTO revenue_faculty VALUES (123461, 1, 6);
> INSERT INTO revenue_faculty VALUES (123462, 2, 7);
> INSERT INTO revenue_faculty VALUES (123463, 1, 8);
> INSERT INTO revenue_faculty VALUES (123464, 3, 9);
> INSERT INTO revenue_faculty VALUES (123465, 2, 10);
> END
>
> /* from Carl van Tast */
> CREATE PROCEDURE STUDENT_FILL AS
> DECLARE VARIABLE stud INTEGER;
> DECLARE VARIABLE os INTEGER;
> DECLARE VARIABLE cnt INTEGER;
> BEGIN
> stud = 0;
> os = 0;
> cnt = 0;
> WHILE (cnt < 7000) DO BEGIN
> cnt = cnt + 1;
> stud = stud + 1;
> os = os + 1;
> IF (os > 3) THEN BEGIN
> os = 1;
> END
> INSERT INTO student (stud_no, os) VALUES (:stud, :os);
> END
> END
>
> Monitor output:
>
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
>
> SECONDS = 0.010
> ----*/
> /*===
> PROFILE DIFF REPORT -- After execute2 dsql
> *** OVERALL SERVER PROCESS ***
> Current Memory = 1,329,120 (+7,168)
> Num Buffer Reads = 244,332 (+63,872)
> Num Buffer Writes = 66,300 (+21,686)
> Num Page Writes = 1,227 (+224)
> *** INSERT COUNT *** 7,000
> 7,000 STUDENT
> PROFILE DATA MARKED AS START
> ====*/
> /*---
> COMMIT
> TR_HANDLE = 15422752
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After commit
> *** OVERALL SERVER PROCESS ***
> Current Memory = 1,320,928 (-8,192)
> Num Buffer Reads = 244,333 (+1)
> Num Buffer Writes = 66,301 (+1)
> Num Page Reads = 467 (+1)
> Num Page Writes = 1,228 (+1)
> PROFILE DATA MARKED AS START
> ====*/
>
>
> /* from Carl van Tast */
> CREATE PROCEDURE ENROL_FILL AS
> DECLARE VARIABLE stud INTEGER;
> DECLARE VARIABLE crt INTEGER;
> DECLARE VARIABLE cnt INTEGER;
> BEGIN
> stud = 0;
> crt = 0;
> cnt = 0;
> WHILE (cnt < 10000) DO BEGIN
> cnt = cnt + 1;
> stud = stud + 1;
> IF (stud > 7001) THEN BEGIN
> stud = 1;
> END
> crt = crt + 1;
> IF (crt > 10) THEN BEGIN
> crt = 1;
> END
> INSERT INTO enrol (stud_no, crt_no) VALUES (:stud, :crt);
> END
> END
>
> /*---
> EXECUTE2 DSQL
> TR_HANDLE = 15422752
> STMT_HANDLE = 15419512
> PARAMS = [ ]
> FIELDS = [ ]
>
> SECONDS = 1.482
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After execute2 dsql
> *** OVERALL SERVER PROCESS ***
> Current Memory = 1,333,216 (+11,264)
> Num Buffer Reads = 315,555 (+71,221)
> Num Buffer Writes = 97,232 (+30,930)
> Num Pages Allocated = 461 (+162)
> Num Page Reads = 476 (+8)
> Num Page Writes = 1,450 (+221)
> *** INDEXED READS *** 1
> 1 RDB$SECURITY_CLASSES
> *** INSERT COUNT *** 10,000
> 10,000 ENROL
> PROFILE DATA MARKED AS START
> ====*/
> /*---
> COMMIT RETAINING
> TR_HANDLE = 15422752
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After commit retaining
> *** OVERALL SERVER PROCESS ***
> Num Buffer Reads = 315,557 (+2)
> Num Buffer Writes = 97,234 (+2)
> Num Page Reads = 478 (+2)
> Num Page Writes = 1,452 (+2)
> PROFILE DATA MARKED AS START
> ====*/
> /*---
> COMMIT
> TR_HANDLE = 15422752
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After commit
> *** OVERALL SERVER PROCESS ***
> Current Memory = 1,320,928 (-12,288)
> Num Buffer Reads = 315,558 (+1)
> Num Buffer Writes = 97,235 (+1)
> Num Page Writes = 1,453 (+1)
> PROFILE DATA MARKED AS START
> ====*/
>
> /* ** First update procedure ** */
> CREATE PROCEDURE UPDATE_ENROL
> AS
> DECLARE VARIABLE VREVENUE_NO INTEGER;
> DECLARE VARIABLE VCRT_NO INTEGER;
> DECLARE VARIABLE VSTUD_NO INTEGER;
> BEGIN
> FOR SELECT RF.REVENUE_NO,
> RF.CRT_NO,
> S.STUD_NO
> FROM STUDENT S
> JOIN REVENUE_FACULTY RF
> ON S.OS = RF.OS
> INTO :VREVENUE_NO, :VCRT_NO, :VSTUD_NO DO
> BEGIN
> UPDATE ENROL
> SET REVENUE_NO = :VREVENUE_NO
> WHERE STUD_NO = :VSTUD_NO
> AND CRT_NO = :VCRT_NO;
> END
> END
>
> /*---
> EXECUTE2 DSQL
> TR_HANDLE = 15423816
> STMT_HANDLE = 15422808
> PARAMS = [ ]
> FIELDS = [ ]
>
> SECONDS = 2.594
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After execute2 dsql
> *** OVERALL SERVER PROCESS ***
> Current Memory = 1,107,844 (+22,528)
> Num Buffer Reads = 271,625 (+140,403)
> Num Buffer Writes = 9,459 (+6,666)
> Num Page Reads = 4,416 (+2,482)
> Num Page Writes = 1,747 (+1,430)
> *** SEQUENTIAL (NON-INDEXED) READS *** 57
> 47 RDB$INDICES
> 10 REVENUE_FACULTY
> *** INDEXED READS *** 26,668
> 23,334 STUDENT
> 3,333 ENROL
> 1 RDB$SECURITY_CLASSES
> *** UPDATE COUNT *** 3,333
> 3,333 ENROL
> PROFILE DATA MARKED AS START
> ====*/
> /*---
> COMMIT RETAINING
> TR_HANDLE = 15423816
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After commit retaining
> *** OVERALL SERVER PROCESS ***
> Num Buffer Reads = 271,627 (+2)
> Num Buffer Writes = 9,461 (+2)
> Num Page Reads = 4,418 (+2)
> Num Page Writes = 1,749 (+2)
> PROFILE DATA MARKED AS START
> ====*/
> /*---
> COMMIT
> TR_HANDLE = 15423816
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After commit
> *** OVERALL SERVER PROCESS ***
> Current Memory = 1,095,556 (-12,288)
> Num Buffer Reads = 271,628 (+1)
> Num Buffer Writes = 9,462 (+1)
> Num Page Writes = 1,750 (+1)
> PROFILE DATA MARKED AS START
> ====*/
>
>
> /* ** Second update procedure ** */
> CREATE PROCEDURE UPDATE_ENROL_2
> AS
> DECLARE VARIABLE VREVENUE_NO INTEGER;
> DECLARE VARIABLE VCRT_NO INTEGER;
> DECLARE VARIABLE VSTUD_NO INTEGER;
> BEGIN
> FOR SELECT RF.REVENUE_NO,
> RF.CRT_NO,
> S.STUD_NO
> FROM REVENUE_FACULTY RF
> JOIN STUDENT S
> ON S.OS = RF.OS
> INTO :VREVENUE_NO, :VCRT_NO, :VSTUD_NO DO
> BEGIN
> UPDATE ENROL
> SET REVENUE_NO = :VREVENUE_NO
> WHERE STUD_NO = :VSTUD_NO
> AND CRT_NO = :VCRT_NO;
> END
> END
>
> /*---
> EXECUTE2 DSQL
> TR_HANDLE = 15421704
> STMT_HANDLE = 15423216
> PARAMS = [ ]
> FIELDS = [ ]
>
> SECONDS = 2.584
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After execute2 dsql
> *** OVERALL SERVER PROCESS ***
> Current Memory = 1,108,868 (+14,336)
> Num Buffer Reads = 473,081 (+140,105)
> Num Buffer Writes = 22,935 (+6,666)
> Num Page Reads = 7,217 (+2,481)
> Num Page Writes = 3,346 (+1,430)
> *** SEQUENTIAL (NON-INDEXED) READS *** 10
> 10 REVENUE_FACULTY
> *** INDEXED READS *** 26,668
> 23,334 STUDENT
> 3,333 ENROL
> 1 RDB$SECURITY_CLASSES
> *** UPDATE COUNT *** 3,333
> 3,333 ENROL
> PROFILE DATA MARKED AS START
> ====*/
> /*---
> COMMIT RETAINING
> TR_HANDLE = 15421704
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After commit retaining
> *** OVERALL SERVER PROCESS ***
> Num Buffer Reads = 473,083 (+2)
> Num Buffer Writes = 22,937 (+2)
> Num Page Reads = 7,219 (+2)
> Num Page Writes = 3,348 (+2)
> PROFILE DATA MARKED AS START
> ====*/
> /*---
> COMMIT
> TR_HANDLE = 15421704
> ----*/
> /*---
> DATABASE INFO
> DB_HANDLE = 15421200
> ----*/
> /*===
> PROFILE DIFF REPORT -- After commit
> *** OVERALL SERVER PROCESS ***
> Current Memory = 1,095,556 (-13,312)
> Num Buffer Reads = 473,084 (+1)
> Num Buffer Writes = 22,938 (+1)
> Num Page Writes = 3,349 (+1)
> PROFILE DATA MARKED AS START
> ====*/
>
> Good luck with MSSQL and the BDE!
>
> Regards,
> Helen
>
> All for Open and Open for All
> InterBase Developer Initiative · http://www.interbase2000.org
> _______________________________________________________