Re: Performance on NT: Oracle vs. SQLServer?

From: Andrew Protasov <protasov_at_percombank.kiev.ua>
Date: 1997/04/18
Message-ID: <AAIbm6tOxR@percombank.kiev.ua>

>
> The performance numbers speak for theselves, but the locking bit
> below is a red herring. Beware the uninformed...
>
> Barry Schader wrote:
> >
> > Gene Petryk wrote:
> > >
> > > On 28 Mar 1997 14:28:17 GMT, "Tim Schwallie" <schwatw_at_hpd.abbott.com>
> > > wrote:
> > >
> > > >> We're trying to decide between Oracle and SQL server, for a DSS database
 with
> > > >> about 200 Meg of data now, but much larger soon (eg 1 Gig). We've got a
 dual
> > > >> processor with 128 Meg (but could increase this), running NT 4.0.
> > > >>
> > > >> I've heard that Oracle doesn't run as fast on NT, as on UNIX. Have any of
 you
> > > >> had experience with either/both, or know of performance benchmarks of
 Oracle
> > > >> vs. NT??
> > >
> > > We have been using Oracle for our DSS style application, but its
> > > update/insert (data aggregation) performance for larger tables is
> > > pathetic. (And it's not a question of memory or disk load) We are
> > > looking at SQL Server right now, it's _way_ faster for these
> > > operations but it has its problems also.
> > >
> > > You can see for yourself:
> > > create a simple table
> > > create table test (fldchar char(30), fldint integer)
> > >
> > > then insert a row into that table, and run
> > > insert into test select * from test
> > > a couple of times.
> > > I created 16K records, then run a simple update query, say:
> > > update test set fldint = 30
> > >
> > > Oracle v.7.1: 1min 30sec., v.7.3: 45sec.
> > > SQL Server: 3sec.
> >
> > Beware the simplistic benchmark. I ran the above test on my
> > Oracle 7.2 / NT 3.51 system and it took less than 9 seconds.
> > Also, the COMMIT afterwards is instantaneous. It isn't
> > quite in the claimed ballpark for SQL Server, but it's a
> > lot better than 45 seconds. (I have to admit that I'm on
> > a 233MHz Alpha workstation, but it's not likely to be five
> > times faster.)
> >
> > Although I've had some difficulties with Oracle performance
> > for querying, I understand the guts of the system and you
> > aren't likely to get a whole lot faster than writing to the
> > end of a sequential file, which is all that Oracle does to
> > execute and commit a transaction. (Writes to the database
> > itself are trickled out later.)
> >
> > I have no experience with MS SQL Server.
> >
> > While I have no doubt that MS SQL Server is somewhat faster
> > on NT, I have a feeling that the magnitude of the cited performance
> > difference may have more to do with differences in configuration
> > (recoverable vs. unrecoverable, for example), and perhaps
> > the tester's experience, in the two databases.
> >
> > A couple of consideration for DSS applications:
> >
> > They tend to be CPU hogs. Be careful about locking yourself into
> > a platform (NT) that doesn't scale well above 4 processors (yet).
> >
> > Oracle provides read-consistency during updates. You can overlap
> > long-running updates and long-running queries in Oracle. They
> > will not block each other and the result of each query will reflect
> > the state of all committed transactions as of the moment when the
> > query started (a consistent snapshot). Last I heard, SQL Server
> > could not do this -- you'd basically have to lock the whole table
> > at the start of a long-running query to guarantee consistency.
> > This doesn't do a whole lot for concurrency. Of course, if all
> > of your updates occur at off-hours then this isn't a big deal.
> >
> > Barry Schader
>

I executed the described benchmark on Pentium/120 with 40 Meg RAM and Western Digital IDE drive WDC AC2850F (850 Meg) with OS/2 3.0 Warp and Oracle 7.3.2.2. The update occupies near 20 sec. You can compare with other results by yourself. Here is the log:

>set timing on
>create table test(fldchar char(30), fldint integer)

Statement processed.
Parse             0.21 (Elapsed)     0.00 (CPU)
Execute/Fetch     0.00 (Elapsed)     0.00 (CPU)
Total             0.21               0.00

>begin

insert into test values('012345678901234567890123456789',1); for i in 1..14 loop
insert into test select * from test;
end loop;
commit;
end;

Statement processed.
Parse             0.13 (Elapsed)     0.00 (CPU)
Execute/Fetch     3.56 (Elapsed)     0.00 (CPU)
Total             3.69               0.00

>select count(*) from test

COUNT(*)

---

     16384
1 row selected.

Parse             0.02 (Elapsed)     0.00 (CPU)
Execute/Fetch     2.99 (Elapsed)     0.00 (CPU)
Total             3.01               0.00

>update test set fldint = 30

16384 rows processed.

Parse             0.01 (Elapsed)     0.00 (CPU)
Execute/Fetch    19.62 (Elapsed)     0.00 (CPU)
Total            19.63               0.00

>commit

 Statement processed.
>drop table test

Statement processed.
Parse             5.36 (Elapsed)     0.00 (CPU)
Execute/Fetch     0.16 (Elapsed)     0.00 (CPU)
Total             5.52               0.00

>spool off

                            Andrew Protasov
                            protasov_at_percombank.kiev.ua

Received on Fri Apr 18 1997 - 00:00:00 CDT