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

From: L. Tseng <lesliet_at_u.washington.edu>
Date: 1997/09/09
Message-ID: <5v3smt$ltc_at_nntp6.u.washington.edu>#1/1


Thanks for all the responses and helpful suggestions. For this specific example, thanks to Thomas Kyte from Oracle, I was able to outperform (24 sec. vs. 58 sec.) MS SQL by simply adding more log space to the Oracle instance. The default was two 200K log files. I added another one for 5MB more. It's that simple.

However, during another testing which actually prompted the original question, I found select count(*) from a dynamically growing table does a lot physical io which causes the performace a great deal. For exmaple, I need to get a total employee count every time I insert a new emplyee record (this requirement cant not be changed), i.e.

     select count(*) into emp_cnt from employee;
     if emp_cnt <= 10000 then
        insert into employee values (......);
     end if;

How can you optimize this?
Thomas Kyte_at_Oracle suggested create a emp_cnt table and use trigger to update the table whenever employee count changed. This worked much better, of course. Besides letting netters know what I have gotten, I would very much appreacite any new insight on how to optimize it. Thanks you all, specially, Thomas. With all your help, I feel much better standing face to face with MS SQL coworkers now.

In article <5unu32$koo_at_nntp6.u.washington.edu>, L. Tseng <lesliet_at_u.washington.edu> 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 Tue Sep 09 1997 - 00:00:00 CEST

Original text of this message