I've been 'fiddling' with it, replaced the multi-selects by
joins, but still ended up with one table no index used.
Still very very slow result. Completely writing it as
a procedure might help, but takes time.

I have no idea's left, but I'm sure others do :)

Hans
------------------------

john@... wrote:

>
> Hans,
>
> There were indexes defined, but Interbase (IB) didn't use them.
>
> I was suprised that the student (stud_no) primary key was not used.
>
> But this is not significant - I didn't think "fiddling at the edges"
> is significant, when SQL_SERVER has no keys and still does the job in
> 2 seconds compared with 2 hours (IB) ie 2 : 60 x 60 x 2 ie 1: 360
> times .... where as IB does and doesn't use them ...
>
> John
>
> --- In ib-support@y..., hans@h... wrote:
> > John,
> >
> > Where there any indexes used?
> > Your example has them all commented out.
> >
> > -----------------------------------
> >
> > john@e... wrote:
> > >
> > > 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?
> > >
> > >
> > > To unsubscribe from this group, send an email to:
> > > ib-support-unsubscribe@egroups.com
> > >
> > >
> > >
> > > Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/