ADO.NET and SQL, Oracle

[Chakra]

Are there any benchmarks for the performance of SQL versus Oracle using the
SQLclient provider and the Oracle provider respectively ?

We find that in our application, that SQL outperforms Oracle by 100%. Is
this the usual pattern ?

 

[W.G. Ryan MVP]

From what I've heard, SqlClient/Sql Server is the fastest combination - but
if you're uisng OracleClient - I really doubt that it's 100% slower. If
you're using OleDb or ODBC then it may well be (although that still seems
pretty dramatic) but then the comparison is apples to oranges. Are you
referring to Oracle w/ OracleClient?

 

[Cor Ligthert [MVP]]

Bill,

When it is 100% slower it is not running.

))

Cor

 

[Sahil Malik [MVP]]

Chakra,

I recently voiced my views about SQL Server vs. Oracle over here
http://codebetter.com/blogs/sahil.malik/archive/2005/08/12/130633.aspx

IMO for a fresh project, you should go with SQL Server unless you have very
specific needs. And performance isn't the only dang thing out there. ODP.NET
is FULL of bugs, it is AWFUL (see this -
http://codebetter.com/blogs/sahil.malik/archive/2005/07/20/129504.aspx ).

I think overall your blood pressure will be a lot lower if you stick with a
SQL Server and .NET combination.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Robbe Morris [C# MVP]]

Classic...

--
Robbe Morris - 2004/2005 Microsoft MVP C#

Earn money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp

 

[Frans Bouma [C# MVP]]

Are there any benchmarks for the performance of SQL versus Oracle
using the SQLclient provider and the Oracle provider respectively ?

We find that in our application, that SQL outperforms Oracle by 100%.
Is this the usual pattern ?

Did you disable connection pooling on oracle? the oracleclient is a
bit slow to connect at first, but with pooling you shouldn't find a
problem with performance on Oracle from .NET.

That said, it of course depends on what client you're using: ODP.NET,
MS OracleClient or oledb/odbc.

Frans


--

 

[Frans Bouma [C# MVP]]

Chakra,

I recently voiced my views about SQL Server vs. Oracle over here
http://codebetter.com/blogs/sahil.malik/archive/2005/08/12/130633.aspx

IMO for a fresh project, you should go with SQL Server unless you
have very specific needs. And performance isn't the only dang thing
out there. ODP.NET is FULL of bugs, it is AWFUL (see this -
http://codebetter.com/blogs/sahil.malik/archive/2005/07/20/129504.aspx
).

I think overall your blood pressure will be a lot lower if you stick
with a SQL Server and .NET combination.

ODP.NET isn't that buggy. Just download an update. At least they're
releasing updates for their provider unlike .... Also, awful? How? It
provides the same interfaces as SqlClient!. Furthermore, it already
supports MARS and full Xml functionality, compared to SqlClient. (oh,
don't start with mentioning beta stuff please)

I also think that deciding which database to use (!) based on the .NET
client is the most unwisest thing you can possibly do. Oracle supports
at the moment a large pack of feature which are absent in SqlServer
2000 (oh, of course they're coming in the next version).

Don't get me wrong, I like SqlServer too, but for databases with
terabytes of data and large tables (millions of rows) with a lot of
users, it's key you have a feature like MVCC or isolatable transactions
which are not blocking readers and readers who aren't blocking writers
(so the only option you have is NOLOCK hints... )

Personally I never had that much problems with ODP.NET. It has its
quircks, but so does SqlClient if I may add. Also, SqlServer isn't
bugfree either.

Your posting sounds a bit like an nVidia fanboy slamming ATi
videocards. Please stick to the facts.

FB

--

 

[Miha Markic [MVP C#]]

Hi Frans,

I also think that deciding which database to use (!) based on the .NET
client is the most unwisest thing you can possibly do.

Dito.

Oracle supports

at the moment a large pack of feature which are absent in SqlServer
2000 (oh, of course they're coming in the next version).

Don't get me wrong, I like SqlServer too, but for databases with
terabytes of data and large tables (millions of rows) with a lot of
users, it's key you have a feature like MVCC or isolatable transactions
which are not blocking readers and readers who aren't blocking writers
(so the only option you have is NOLOCK hints... )

Personally I never had that much problems with ODP.NET. It has its
quircks, but so does SqlClient if I may add. Also, SqlServer isn't
bugfree either.

I like Sql server more than Oracle becuase:
- better tools (SqlPlus anybody? )
- way easier to manage and install
- install size is smaller

Perhaps Oracle is a better database (not saying that it actually is) but
only if there are very skilled administrators and programers that take care
of the huge monster.
And perhaps it is just me but the ease of development with Sql server is
a huge point in favour of it.

 

[WJ]

Are there any benchmarks for the performance of SQL versus Oracle using
the
SQLclient provider and the Oracle provider respectively ?

This cannot be done or asked because the 2 systems are distinct. One is
native (SQL) the other (ORA) is universal.

We find that in our application, that SQL outperforms Oracle by 100%. Is
this the usual pattern ?

Of course it is and it is obvious because SQL is fine tuned to Windows
platform, .NetFW. On top of this, it only runs on Windows OS. While Oracle
RDBMS supports multiple platforms such as Windows, Unix, Linux and IBM...

In short, if you limit your skill to only Microsoft products, then it is
wise to go with MS/SQL Server and .Net. Otherwise, Oracle and Java are just
as good as MS stuffs on other OSes than Windows.

John Webb

 

[W.G. Ryan MVP]

Frans - I'm going OT here but check this out if you don't mind
http://msmvps.com/williamryan/archive/2005/08/13/62577.aspx

Or check the "SQL or Access DBd" thread in the ..dotnet.languages.vb
newsgroup on 8/13.

And since I'm in a joking mood....

Well all I know is that Stored Procedures are precompiled in Sql Server 2000
so that's why I use both Sql Server and Stored Procs. And don't give me any
of that "No they aren't - it even says so in books online"

 

[Sahil Malik [MVP]]

Well, we both have our own views on this. But as I said in that mp3
download, unless you have very specific situations, you are better off using
SQL Server instead. Don't forget licensing cost either. I still feel that in
a .NET world, it makes sense to stick with SQL Server for a fresh
development. Not enough reason to migrate your company's database from
Oracle to SQL Server as that may be a huge undertaking, but if you are
starting afresh, and you are choosing Oracle over SQL Server, you need to
have a very good explanation of "why".

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Frans Bouma [C# MVP]]

Well, we both have our own views on this. But as I said in that mp3
download, unless you have very specific situations, you are better
off using SQL Server instead. Don't forget licensing cost either. I

Licensing costs are not an issue with enterprise databases. What's far
more an issue is that you have to take down the db to make a live
backup or that you have to enable dirty reads to avoid blocking.

still feel that in a .NET world, it makes sense to stick with SQL
Server for a fresh development.

Why? What's the core reason for that? Better .net client? Easier
development? I don't think it's the case. I've done a lot of
development on both, and I can't find much difference in ease of use
from a .NET POV. I DO find differences in feature set, especially on
the DB side. And No offence but that truly matters: first the DB, then
the layer you use to talk to it.

Not enough reason to migrate your
company's database from Oracle to SQL Server as that may be a huge
undertaking, but if you are starting afresh, and you are choosing
Oracle over SQL Server, you need to have a very good explanation of
"why".

I haven't heard much reasons from you why Sqlserver is better than
Oracle in this , other than ODP.NET is awful (why?, it's a huge
download, but it contains the client as well).

I mean: array parameters to send value sets to the DB for easy
inserts/updates, multiple ref cursor returns to support multiple active
resultsets, VERY EASY paging code etc.

Oracle tools are sometimes a pain, but so is QA sometimes. I use
SQLTools 1.4 now for oracle, and I haven't felt any problem with it.
SQLServer is becoming easier than oracle in the next release, but
that's not really reality yet.

For small projects, don't bother, go for the cheapest, which is likely
an open source db like Firebird or postgresql (and not sqlserver!). For
larger projects, there are other things which count than .NET
development ease.

FB

stick >> with a SQL Server and .NET combination.

--

 

[Frans Bouma [C# MVP]]

Hi Frans,


Dito.

Oracle supports

I like Sql server more than Oracle becuase:
- better tools (SqlPlus anybody? )

haha yeah, that's pretty retarted indeed . I use SQLTools 1.4 now
for oracle. There are other tools which cost a small fee which are good
as well and offer more features than for example QA.

- way easier to manage and install

Installation is indeed easier, manage... I'm not sure. People often
forget that Sqlserver, when used in a large application, needs proper
tweaking and tuning as well.

- install size is smaller
true.

Perhaps Oracle is a better database (not saying that it actually is)
but only if there are very skilled administrators and programers that
take care of the huge monster.
And perhaps it is just me but the ease of development with Sql
server is a huge point in favour of it.

Isn't this a myth more or less? What's so horrible on developing
against an Oracle db, from .NET's POV? You use the same interfaces, and
have even more functionality at hand...

Frans

--

 

[Sahil Malik [MVP]]

Costs *ARE* a big important facet for enterprise databases (and every other
db for that matter). I have worked with a client that used to pay over 2
million dollars a year to Oracle for licensing costs, just by gradually
switching to SQL Server over a period of 5-6 years, they ended up saving 2
million $ a year (Well SQL Server licensing wasn't as much atleast). 2
million $ is a lot. If you had a stack of $100 bills worth a 2,000,000 and
you set fire to that, the fire would burn for a month LOL. Trust me, they
were extremely happy to save that much every year. And Microsoft.com has
zero downtime, are you telling me that they are running on Oracle (Or Db2)?

Costs truly are a big factor. Nobody has money to burn, irrespective of your
size.

BTW, I am curious why you say Paging code in Oracle is easier. I think we
discussed this sometime back, but can you refresh my memory on this? BTW, we
are comparing SQL2k5 with Oracle here. And Oracle tools, you call SQL+ a
"tool"? Honestly I wish SQL Server had a lightweight client like SQL+
(Sorry osql doesn't qualify), but I wouldn't trade SS Management Studio for
SQL+.

I'm not saying either database is inferior to the other, but when viewed in
a 100% microsoft shop, you'd better have a darn good reason to go with
oracle instead of SQL Server. I'm not saying reasons don't exist, but they
are very few and highly specialized.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Miha Markic [MVP C#]]

haha yeah, that's pretty retarted indeed . I use SQLTools 1.4 now
for oracle. There are other tools which cost a small fee which are good
as well and offer more features than for example QA.

I was afraid you'll mention TOAD

Installation is indeed easier, manage... I'm not sure. People often
forget that Sqlserver, when used in a large application, needs proper
tweaking and tuning as well.
Right.


Isn't this a myth more or less? What's so horrible on developing
against an Oracle db, from .NET's POV? You use the same interfaces, and
have even more functionality at hand...

Yes, from .net perspective is more or less the same. I was talking in terms
of overall experience.

 

[Frans Bouma [C# MVP]]

Costs ARE a big important facet for enterprise databases (and every
other db for that matter). I have worked with a client that used to
pay over 2 million dollars a year to Oracle for licensing costs, just
by gradually switching to SQL Server over a period of 5-6 years, they
ended up saving 2 million $ a year (Well SQL Server licensing wasn't
as much atleast). 2 million $ is a lot. If you had a stack of $100
bills worth a 2,000,000 and you set fire to that, the fire would burn
for a month LOL. Trust me, they were extremely happy to save that
much every year.

Though there are other costs, besides the license of the db. The same
math is used by Microsoft to calculate the TOC of windows compared to
linux: linux doesn't have a license fee yet comes out more expensive


And Microsoft.com has zero downtime, are you telling

me that they are running on Oracle (Or Db2)?

What does microsoft.com have to do with this? That site has 0 downtime
because you don't notice downtime. That doesn't mean there isn't a
problem with live backups.

BTW, I am curious why you say Paging code in Oracle is easier. I
think we discussed this sometime back, but can you refresh my memory
on this? BTW, we are comparing SQL2k5 with Oracle here. And Oracle
tools, you call SQL+ a "tool"? Honestly I wish SQL Server had a
lightweight client like SQL+ (Sorry osql doesn't qualify), but I
wouldn't trade SS Management Studio for SQL+.

I'm not talking about sqlserver 2005, which is still in beta. Not a
lot of enterprise apps will be build on a first release of sqlserver
2005. So doing paging code on oracle is extremely easy, doing it on
sqlserver is hard. (it was just an example)

YOu also should read more what I wrote: I said I used SQLtools, not
SQL+.

And what's SS Management studio, that beta toolkit? What's so special
about that, besides it's running at the same speed as Oracle's 9i tools?

I'm not saying either database is inferior to the other, but when
viewed in a 100% microsoft shop, you'd better have a darn good reason
to go with oracle instead of SQL Server. I'm not saying reasons don't
exist, but they are very few and highly specialized.

Well, I think differently, though that was not hte point of my post.
My point was that you provided no reasons why people should stick with
sqlserver besides the .NET client (irrelevant for a large db) and now
pure license costs (which are just a part of the puzzle).

The last thing we need is that people start to think that MVP==fanboy,
as there are already enough MS fanboys around.

Frans

unless you >> >> have very specific needs. And performance isn't the
only dang thing >> >> out there. ODP.NET is FULL of bugs, it is AWFUL
(see this - >> >>they're >> > releasing updates for their provider unlike .... Also,
awful? How? >> > It provides the same interfaces as SqlClient!.
Furthermore, it >> > already supports MARS and full Xml
functionality, compared to >> > SqlClient. (oh, don't start with
mentioning beta stuff please) >> >Oracle >> > supports at the moment a large pack of feature which are
absent in >> > SqlServer 2000 (oh, of course they're coming in the
next version). >> >aren't >> > blocking writers (so the only option you have is NOLOCK
hints... ) >> >its >> > quircks, but so does SqlClient if I may add. Also, SqlServer
isn't >> > bugfree either.by >> >> > 100%. Is this the usual pattern ?

--

 

[Frans Bouma [C# MVP]]

I was afraid you'll mention TOAD

Heh, no not from me. TOAD is pretty weird and breaths 'I'm DBA since
1879' in every feature.

Btw, no-one mentions DB2, not a small player in DB world, and less
expensive than oracle (and has the same features, more or less)

Frans

--

 

[Sahil Malik [MVP]]

I'm not an MS Fanboy, I criticize when they deserve it (SPOT watches suck,
so does that stupid dog in search on windows xp). Well when I compare SQL
Server and Oracle, I tend to think in terms of SQL2k5. Anyway ... so we hold
our views here and thats cool.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Sahil Malik [MVP]]

BTW Check this out Frans -
http://www.sdtimes.com/article/story-20050815-26.html

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Yuri Nogin]

Alright,
I see it’s a hot discussion.
I’d like to give my 2 cents on this subject. I’ll give you some insight
on my career first so you would know that it’s coming from my personal
experience.
I’m currently working as a DB2 and SQL Server 2000 administrator. In the
past I was a BI architect working with both Oracle and SQL Server
databases primarily. I’m an MCP and OCP.
When comparing feature for feature Oracle 10g, DB2 8.2, and SQL Server
2000. Oracle is the richest database for development and maintains hands
down. DB2 is only working to introduce a true partitioning in the next
version (range) while Oracle has 3 types of it and supports
sub-partitioning as well. Oracle had range partitioning since version 8
(this is more than 8 years ago). This is just an example. As a developer
you have tons of features that you can’t find in any other database.
Now, SQL Server 2005 comes close (still behind) but a lot closer than
SQL 2K. Also Oracle is the only database that has a different rollback
mechanism vs. all other databases (SQL Server, Sybase, DB2). It’s “very
hard” to get a ”dead lock” situation in Oracle. Of course from your
experience you probably know it’s very easy to catch it in SQL Server or
DB2. I can go on and on here. PL/SQL is by far superior to TSQL. Now,
said this I still like SQL Server (for small and “quick and dirty”
projects). If you get to the Enterprise Multi Terabyte database systems
you may want to wait for SQL Server 2005, or use Oracle. BTW I have
experience using SQL Server 2000 with 2 TB of data. It was not easy at
all.

Thanks,

(\\\|///)
(\ - - /)
@( @ @ )@
---oOOo-(_)-oOOo---------------
Yuri Nogin, MCP, OCP
Currently DB2 administrator
-------------Oooo--------------
oooO ( )
( ) ) /
\ ( (_/
\_)