Re: Oracle much slower than MS SQL Server on NT?!

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;

> >

> > /