Re: Oracle much slower than MS SQL Server on NT?!
From: Chrysalis <cellis_at_iol.ie>
Date: 1997/09/08
Message-ID: <34144BF1.639B_at_iol.ie>#1/1
Date: 1997/09/08
Message-ID: <34144BF1.639B_at_iol.ie>#1/1
As a matter if interest, I reproduced the example given, using V7.1.4 of Oracle on a Macintosh PowerBook 5300ce with 32Mb of RAM.
- Using the original code (committing every row), I got a timing of 191 secs.
- By using a commit counter in the procedure and committing every 250 rows, this was reduced to 102 secs i.e. a reduction of 47%.
- Instead of creating indexes prior to executing the procedure, I declared (disabled) primary and unique constraints in the table definition. I also removed all the intermediate commits. The total time, including enabling the constraints after executing the procedure, reduced to 82 secs. This represented a further reduction of 20% (smaller than I expected), but was still a saving of 58% on the original time.
- Finally, I recognised that row-at-a-time triggers are expensive (and for sequence-generation during a bulk load, unnecessary). I therefore replaced the sequence and trigger by an EmployeeID generated in the procedure (using the loop counter already there), and reduced the time to 42 secs. This represented a further reduction of 50%, and a total reduction of 78% from the original time!
Moral: Improvements are always possible. This may even be true with MS SQL, of course :-)
--
Chrysalis
FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"
Protasov Andrew wrote:
>
> My time is 53 sec for 5000 records with commit in loop and
> 35 sec. without it. I have Pentium 120, 40 RAM and old slow
> IDE drive. What is your configuration?
>
> Andrew Protasov
>
> L. Tseng wrote:
> >
> > I recently did a testing to see the performance difference
> > between Oracle 7.3 and MS SQL 6.5.
> >
> > The test simply does 5000 and 10000 rows insert to a table, to my
> > surprise, Oracle is 2-3 times slower and gets even slower when inserting
> > more rows. This seems to contradict to Oracle's claim as the fastest
> > database engine.
> >
> > The environment for testing is on the same NT 4.0 box. Two server were
> > configured to use about same amount of memory. The results are:
> >
> > insert
> > 5000 recs Oracle: 2 min. 20 sec.
> > MS SQL: 58 sec.
> >
> > 10000 recs Oracle: 4 min. 55 sec.
> > MS SQL: 1 min. 42 sec.
> >
> > I was wondering if anyone experience the same thing or
> > anyone know some tips to speed up the Oracle.
> >
> > Thanks for your help in advance.
> >
> > Leslie
> >
> > I here include the table structure below for reference.
> >
> > CREATE TABLE Employee
> > (
> > EmployeeID int NOT NULL ,
> > FirstName VARCHAR2 (50) NULL ,
> > LastName VARCHAR2 (50) NULL ,
> > MiddleInitial char (2) NULL ,
> > DefaultLoginName VARCHAR2 (30) NULL ,
> > DefaultDomainName VARCHAR2 (50) NULL ,
> > EmailAddress VARCHAR2 (75) NULL ,
> > SetupDate DATE DEFAULT SYSDATE ,
> > SetupBy VARCHAR2 (30) DEFAULT USER ,
> > LastModifiedDate DATE NULL ,
> > LastModifiedBy VARCHAR2 (30) NULL ,
> > ServerFlag NUMBER(3) NULL,
> > RecordInputType NUMBER(3) NULL
> > )
> > ;
> >
> > CREATE UNIQUE INDEX PKEmployee ON Employee(EmployeeID)
> > ;
> >
> > CREATE UNIQUE INDEX AK1Employee ON Employee(DefaultLoginName)
> > ;
> >
> > drop sequence emp_seq;
> > create sequence emp_seq;
> >
> > CREATE OR REPLACE TRIGGER EmpID_TR
> > BEFORE INSERT ON employee
> > FOR EACH ROW
> > WHEN (NEW.EmployeeID IS NULL)
> > BEGIN
> > SELECT emp_seq.NEXTVAL INTO :NEW.EmployeeID FROM DUAL;
> > END;
> >
> > /
> >
> > declare
> > y int;
> > begin
> > for y in 1..5000 loop
> > INSERT INTO Employee
> > (
> > FirstName,
> > LastName,
> > MiddleInitial,
> > DefaultLoginName,
> > EmailAddress,
> > ServerFlag,
> > RecordInputType
> > )
> > VALUES
> > (
> > 'User'||y,
> > 'User'||y,
> > null,
> > 'User'||y,
> > null,
> > 1,
> > 2
> > );
> > commit;
> > end loop;
> > commit;
> > end;
> >
> > /
Received on Mon Sep 08 1997 - 00:00:00 CEST