Carl,

Thanks for your rigourous work.

This at least shows me clearly where I have to go, without spending a
lot of time fiddling at the edges.

I used SQL_SERVER 2000, but also loaded the data into MSDE - it took
2 secs (or less) too.

I guess the optimiser in MS databases is fairly good, and using
procedures with MS would give even better results.

Thanks again Carl for investigating this ...

John

--- In ib-support@y..., Carl van Tast <vanTast@a...> wrote:
> Hi John, Helen, Ann et al.
>
> Sorry for the long message.
>
> On Tue, 25 Sep 2001 18:36:01 +0000 (UTC), john@e... wrote:
> >[...]
> >
> >UPDATE ENROL E
> >SET REVENUE_NO = (
> > SELECT REVENUE_NO
> > FROM REVENUE_FACULTY RF, STUDENT S
> > WHERE S.STUD_NO = E.stud_no
> > AND RF.CRT_NO = E.crt_no
> > AND RF.OS = S.OS)
> >[...]
> >This took about 2 hours to run...
> >
> >What am I doing wrong?
>
> I don't know, but out of curiosity I made a few tests. For those
> interested I post my results here. The tests were run with
Interbase
> 6.0 (referred to as IB in this post), PostgreSQL 7.1.3 (PG) and two
> prot^Hper^H^Hrilit^H^H... , well, commercial database servers (M7
and
> O7).
> Hardware and OS:
> P II 300MHz, 128MB, W2K Pro for IB, PG, M7
> AMB K6 180MHZ, 256MB, NT Server for O7
> All database servers installed out of the box, no special tuning
(see
> note at end).
>
> All times are in minutes and seconds, first value without indices,
> second value with indices.
>
> John did not tell us his metadata, so I had to guess:
>
> CREATE TABLE enrol (
> stud_no INTEGER NOT NULL,
> crt_no INTEGER NOT NULL,
> revenue_no INTEGER
> /*,
> UNIQUE (stud_no, crt_no)
> */
> );
>
> CREATE TABLE revenue_faculty (
> revenue_no INTEGER NOT NULL,
> crt_no INTEGER NOT NULL,
> os INTEGER NOT NULL
> /*,
> PRIMARY KEY (revenue_no),
> UNIQUE (crt_no, os)
> */
> );
>
> CREATE TABLE student (
> stud_no INTEGER NOT NULL,
> os INTEGER NOT NULL
> /*,
> PRIMARY KEY (stud_no)
> */
> );
>
> The tables were populated with following stored procedures:
>
> SET TERM | ;
> 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|
>
> 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|
>
> 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, 3, 1);
> INSERT INTO revenue_faculty VALUES (123459, 4, 2);
> INSERT INTO revenue_faculty VALUES (123460, 5, 3);
> INSERT INTO revenue_faculty VALUES (123461, 6, 1);
> INSERT INTO revenue_faculty VALUES (123462, 7, 2);
> INSERT INTO revenue_faculty VALUES (123463, 8, 1);
> INSERT INTO revenue_faculty VALUES (123464, 9, 3);
> INSERT INTO revenue_faculty VALUES (123465, 10, 2);
> END|
> SET TERM ; |
>
> EXECUTE PROCEDURE enrol_fill;
> -- IB: 0:01.0 / 0:02
> -- PG: 0:03.5 / 0:04.7
> -- M7: 0:06 / 0:06
> -- O7: 0:41 / 1:41
>
> EXECUTE PROCEDURE student_fill;
> -- IB: 0:00.9 / 0:01.4
> -- PG: 0:02.5 / 0:03.3
> -- M7: 0:04 / 0:04
> -- O7: 0:36 / 0:57
>
> EXECUTE PROCEDURE revenue_faculty_fill;
> -- IB, PG, M7: instantly
>
> Note: Without explicit BEGIN/COMMIT TRANSACTION times jump up to
1:55
> for enrol_fill resp. 1:20 for student_fill in M7, +/- a few seconds
> for with/without index.
>
> -- test:
> SELECT count(*)
> FROM enrol e, revenue_faculty rf, student s
> WHERE s.stud_no = e.stud_no
> AND rf.crt_no = e.crt_no
> AND rf.os = s.os;
> -- correct answer is 3333
> -- IB: 0:01.6 / 0:01.6
> -- PG: 0:03.1 / 0:01.7
> -- M7: 0:00.9 / 0:01
> -- O7: 0:03.4 / 0:01.8
>
> -- John's update:
> UPDATE enrol e
> SET revenue_no = (
> SELECT revenue_no
> FROM revenue_faculty rf, student s
> WHERE s.stud_no = e.stud_no
> AND rf.crt_no = e.crt_no
> AND rf.os = s.os);
> -- IB: 66:00 / 66:00
> -- PG: 4:40 / 0:05.4
> -- M7: 2:16 / 0:02
> -- O7: >>180 / 0:35
>
> UPDATE enrol
> SET revenue_no = rf.revenue_no
> FROM revenue_faculty rf, student s
> WHERE s.stud_no = enrol.stud_no
> AND rf.crt_no = enrol.crt_no
> AND rf.os = s.os;
> -- IB: doesn't work
> -- PG: 0:03.4 / 0:02.5
> -- M7: 0:01 / 0:01
> -- O7: doesn't work
>
> -- Helen's procedure
> SET TERM | ;
> 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 revenue_faculty rf
> JOIN student s
> ON rf.os = s.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|
> SET TERM ; |
>
> EXECUTE PROCEDURE update_enrol;
> -- IB: 50:00
>
> This procedure has to update 3333 rows, but
> SELECT ... FROM rf JOIN s ...
> selects 23334 rows, so on average each row is updated seven times.
I
> did no more tests with this procedure and used the following
instead:
>
> SET TERM | ;
> CREATE PROCEDURE update_enrol2
> 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 rf.os = s.os
> JOIN enrol e
> ON e.stud_no=s.stud_no AND e.crt_no=rf.crt_no
> 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|
> SET TERM ; |
>
> EXECUTE PROCEDURE update_enrol2;
> -- IB: 7:30 / 0:02.6
> -- PG: 6:25 / 0:07
> -- M7: 8:41 / 0:04
> -- O7: 5:29 / 0:11
>
> Again you have to include BEGIN/COMMIT TRANSACTION for M7.
Execution
> time would be 9:33 / 0:41 without.
>
> Re tuning:
> On Thu, 27 Sep 2001 17:34:41 +0000 (UTC), aharrison@i...
> ("Ann W. Harrison") wrote:
> >> > >DATABASE_CACHE_PAGES 75
> >
> >I'd strongly suggest increasing this to at least 2500 if you're
> >running SS. You may see better performance at 5000.
>
> Well, after setting this parameter to 2500 and restarting IB I
> repeated my tests and I got *exactly* the same times. This can be
> explained by the fact that the machine is almost 100% CPU bound
during
> these tests. Disk IO is not an issue here. Or did I do something
> wrong?
>
> Conclusions:
>
> . Both open source databases offer good performance when used
> properly.
>
> . The more modern commercial product seems to be faster with
> complex SQL statements. (The other one is almost ten years
old.)
>
> . IB and PG have really fast stored procedure execution engines.
>
> . Data design and choice of algorithm have great influence on
> performance. Using the same hardware and software, you can do
the
> same job in 3 seconds or in more than 1 hour.
>
> . UPDATE ... FROM would help a lot.
>
> Please don't flame me for doing my tests on different hardware.
> Correct the results by a factor 2 or 3, if you want. IMHO
conclusions
> are still valid.
>
> If I made a mistake or you think the times measured should be
> interpreted differently, let me know. I'll happily correct this
> document and make it available again, though not necessarily on this
> mailing list; it's really too long for sending it more than once.
>
> >
> >Thanks in advance for any clues.
> >
> >John Newcombe
>
> .02 by
> Carl van Tast
> >
> >
> >PS Sacrilege - I datapumped the entire database into a sql_server
db -
> >and ran the exact same query with no indexes and it took 2 secs!!
>
> Not a sacrileg, just valuable information. But what kind of
> sql_server?