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?

>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 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.

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.

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

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
_______________________________________________________