My SQL Server course having finished at last, it was inevitable that I’d start wondering whether it was competitive with Oracle. There are lots of feature differences between the two products, of course, but I just wondered what out-of-the-box performance was like.
So I built a 16GB RAM, 60GB Virtual machine and installed Windows 2008 R2 Standard SP1 on it, plus .NET Framework 3.5 and the guest additions. (I used Windows 2008 R2 simply because it doesn’t automatically try to activate itself: this was a disposable testbed, not a machine I wanted accrued to my activation account!) Once it was built, I took a snapshot.
Next, I installed Oracle 11.2.0.1 64-bit, Enterprise Edition, software only. Once the install was complete, I fired up the Net Configuration Assistant to create a listener and then DBCA to created a new custom database. For the most part, I accepted all defaults (so the database was created in no archivelog mode, for example). However, I de-selected all optional components except Oracle Text and Enterprise Manager. I deliberately didn’t alter any of the automatic memory settings: as far as possible, I took Oracle’s out-of-the-box defaults. I did alter the online redo logs to be 500MB in size each (so 1500MB in all), and the USERS tablespace to be 15000MB (so that the entire database was around 20GB in size, all tablespaces considered). Software installation only took just 3 minutes, but the database creation process took another 28 minutes.
After taking a new snapshot, I reverted back to the original and then installed 64-bit SQL Server 2012 Developer Edition (Database Engine, Full-text search, Integration Services and all the management tools, plus a .NET Framewaork 3.5.1 installation when required). Then I created a new database, 20GB in size and with a 1500MB transaction log, running in simple recovery mode.Total time to install and create the database: 22 minutes.
Now, I know that any SQL Server users out there are going to be wailing and gnashing teeth at this point, because I’ve committed (at least!) two cardinal sins: I’ve plonked the transaction log on the same “spindle” as my data files; and I’ve only created a database with a single, large primary filegroup (you’re supposed to create multiple data files, on different “spindles”, and group them as part of the secondary file group for the database.
Well, tough: Oracle’s not supposed to have all its files on one disk, either, but I installed it that way, too. So, it’s still a like-for-like comparison, as far as I can tell. Besides, I don’t have ‘spindles’” this is using a solid state drive, where matters of rotational latency can be ignored. Yes, it would be nice to write sequentially to a redo or transaction log, and not have that sequential write disturbed by the random access required to a datafile: but on an SSHDD, there are no disk heads to dislodge like that anyway. So I think you can overdo the teeth-gnashing on that score!
Anyway: I took a final snapshot and can therefore now flip back and forth between Oracle 11g on Windows 2008 R2 and SQL Server 2012 on Windows R2 -identical virtual hardware, running on the one physical PC. Time, then, for some comparative tests!
Here’s the code I used for the Oracle database:
drop table testtable purge; create table testtable ( col1 varchar(10), col2 date, col3 date, col4 date, col5 date, col6 date ) tablespace users; set timing on set autotrace trace declare v_rowcount number; v_rowstring varchar2(10); v_random number; v_upper number; v_lower number; v_date date; begin v_lower := -730; v_upper := -1; v_rowcount := 0; while v_rowcount < 5000000 loop v_rowstring := to_char(v_rowcount); select round(((v_upper - v_lower -1) * dbms_random.value + v_lower),0) into v_random from dual; v_date := sysdate+v_random; insert into testtable values ( lpad(v_rowstring,10,'0'), v_date, v_date+1, v_date+2, v_date+3, v_date+4); v_rowcount := v_rowcount+1; end loop; commit; end; / update testtable set col4=col4+4; commit; select * from testtable where col4<sysdate+2 order by col4; create index col4idx on testtable(col4) tablespace users; select * from testtable where col4<sysdate+2 order by col4; select * from testtable where col4<sysdate+2 order by col4 desc; delete from testtable where mod(to_number(col1),3)=0; commit; drop index col4idx; delete from testtable where mod(to_number(col1),2)=0; commit;
And here’s the near-equivalent for SQL Server:
Query -> query options -> Results -> Discard results after execution -> OK
use hjrdb;
go
drop table testtable;
create table testtable
(
col1 varchar(10),
col2 datetime,
col3 datetime,
col4 datetime,
col5 datetime,
col6 datetime
);
declare @RowCount int
declare @RowString varchar(10)
declare @Random int
declare @Upper int
declare @Lower int
declare @InsertDate datetime
set @Lower = -730
set @Upper = -1
set @RowCount = 0
while @RowCount < 5000000
begin
set @RowString = cast(@RowCount as varchar(10))
select @Random = round(((@Upper - @Lower -1) * rand() + @Lower), 0)
set @InsertDate = dateadd(dd, @Random, getdate())
insert into testtable(col1,col2,col3,col4,col5,col6)
values
(replicate('0', 10 - datalength(@RowString)) + @RowString,
@InsertDate,
dateadd(dd, 1, @InsertDate),
dateadd(dd, 2, @InsertDate),
dateadd(dd, 3, @InsertDate),
dateadd(dd, 4, @InsertDate))
SET @RowCount = @RowCount + 1
end;
update testtable set col4=col4+4;
commit;
select * from testtable where col4 >= dateadd(day,+2,getdate()) order by col4;
create index col4idx on testtable(col4);
select * from testtable where col4 >= dateadd(day,+2,getdate()) order by col4;
select * from testtable where col4 >= dateadd(day,+2,getdate()) order by col4 desc;
delete from testtable where cast(col1 as int) % 3=0;
commit;
drop index col4idx on testtable;
delete from testtable where cast(col1 as int) % 2=0;
commit;
As you can see, it’s pretty basic stuff: populate a table with 5 million rows, do some selects with ordering, do some updates, create an index, do some ordering ascending and descending, do some deletes. In all cases, I switch off returning results to the client tool, to rule out network transport and client handling issues. Here are the results:
| Test | Oracle | SQL Server |
| Insert 5 million rows | 297 seconds | 854 seconds |
| update 5 million rows | 26 seconds | 8 seconds |
| select 5 million rows, no ordering | 20 seconds | 2 seconds |
| select, filtering and ordering by column 4 | 0.23 seconds | 1 seconds |
| create an index on column 4 | 3 seconds | 7 seconds |
| select, ordering by column 4 | 0.07 seconds | 0.1 second |
| select, ordering by column 4 descending | 0.06 seconds | 0.1 second |
| delete 1/3rd of the rows | 28 seconds | 7 seconds |
| delete half of the rows in the absence of the index | 13 seconds | 2 seconds |
All tests were performed three times, with the VM being reset to its appropriate snapshot between each run. Above timings are the averages of the three runs.
Make of that what you will: clearly, Oracle beats SQL Server into the dust with an initial load and is pretty fast when selecting the data back out, too. But it struggles on updates and deletes: data maintenance seems curiously easy for SQL Server. My best guess at this stage is that this is what doing multi-versioning read-consistency costs you: SQL Server doesn’t have to worry about letting readers see the old version of data before the new one is committed; Oracle has to… and I think it shows.
Interesting nonetheless: if concurrent access to data is not really an application requirement, you might be forgiven for thinking that Oracle was overkill. Alternatively, if big, bulk loads and massive queries are what you spend all your time doing, you may well conclude that SQL Server isn’t quite up to the job. Horses for courses, in other words. More investigation required, of course…
Note: Because I’m still feeling my way in the way T-SQL works, I took the original script from here, changed it a bit and then modified it for Oracle use.
