[thelist] MySQL vs MS SQL

Ken Schaefer ken at adOpenStatic.com
Wed Feb 11 18:55:51 CST 2004


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jonathan Lambert" <j at firebright.com>
Subject: Re: [thelist] MySQL vs MS SQL


: > I have a client with a completely Perl-based, somewhat
: > compute-intensive website. It was built initially to use the
: > MS Access Jet DB Server and today still runs on that server.
: >
: Wow, Jet! ;-)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Lots of sites run on Access .mdb files. If it's running on an Access file at
the moment, I don't think it's a very high-scale site IMHO. Obviously, that
may change in the future.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > The new server will be physically just that--a new dedicated
: > Windows Server 2003 machine. The client was recommended to
: > use MySQL or MS SQL Server and one party even recommended
: > using two machines, one for the web server and
: > one for the DB server.
: >
: I think this is a possibility.  Let's consider the parameters required
: for this particular situation.  You have 1000 simaltaneous users.  Let's
: say that on your pages, for the sake of argument, you had 5 database
: calls per page (a decently complicated website, built from different
: components but all hosted on the same server), and that you had on
: average a call every 10 seconds per client, as intensive pages are
: averaged out with less intensive pages.
:
: So, on average, 1000 simaltaneous clients would generate
: 30000 database calls a minute.  This is complete conjecture,
: but you get the point. That's 1.8 million db calls an hour, a fairly
: reasonable number, but it would most certainly put load on your
: server.  If you had a traffic crunch, how would you scale?  You
: wouldn't.  You'd get killed if you had it all on one server.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There are a number of things to consider here. Running everything on one
server allows for much faster communication between process rather than
having to marshal information across additional boundaries when
communicating across a network. You can get away with stuff on a
single-machine design that will start to cause problems if you put a network
in the middle.

That said, in general, I would agree that multi-box solution is the
generally accepted way to scale a web application.

If you design a web application to be modular, then it becomes a simple
matter of changing a few connection strings when the RDBMS software is moved
off the local machine to a remote machine.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > The Perl uses DBI and so potentially a switch to MySQL
: > could be done fairly painlessly and without a large investment
: > (which is always an issue with this particular client) in man-hours
: > nor software. I understand, however, that with sprocs, which are
: > compiled in a MS SQL DB, that the computational load on the CPU
: > should be reduced significantly and thus speed up the
: > system. I am also told that there are other advantages to MS SQL.
: >
:
: Stored Proceedures is the best (IMHO) way to build massively scalable
: web applications, and the performance benefits, especially in high
: traffic environments, cannot be overemphasized.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The performance difference between stored procedures and dynamic SQL isn't
all that great in SQL Server 2000, because SQL Server 2000 also caches the
execution plans of dynamic queries.

However sprocs do give you:
a) security (you are pretty much immune to SQL Injection attacks etc as long
as you don't execute SQL inside your sproc)
b) cleaner code, in that you don't have to concatenate a whole bunch of SQL
strings together in your code

However:
a) you can start ending up with a lot of business logic in your database
rather than in your business layer. This can make it difficult to debug
things (a lot of debuggers don't reach into a database for example). SQL
itself is also suited to set-based manipulation of data, which isn't what a
lot of business rules are about (however, many tier 1 vendors are addressing
that issue - SQL Server Yukon will alway you to use .Net based languages
inside SQL Server to get around the limitations of SQL itself)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: MS SQL has support for transactions, built in load balancing
: solutions in the enterprise product line, and a number of
: advanced features which might make scaling
: the business a lot easier.  ODBC overhead can become a
: factor in high traffic environments.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This is where I agree totally with Jonathon. SQL Server (and DB2, Oracle
etc) are true enterprise RDBMSes, and provide the feature set that will (a)
keep your DB Server up and running (provided you have a competant DBA) and
(b) scale to anything you throw at it.

I understand that you application is built using Perl, and if you do use
ODBC to connect, then it can become a bit chatty as ODBC is COM-based. If
you migrate your application to .Net, then you can take advantage of the
native providers in .Net which are much more perfomant (there are providers
for a few different DBMSes available).

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > PS: I sincerely doubt the client would be interested in a Linux
: > solution. He is one of these corporate-type Windoze fans.
: > I myself hate Windows. :)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

That's not a good basis to start upon recommending a solution. :-) There are
plenty of people running all sorts of large scale applications on all sorts
of platforms. Any of the enterprise platforms will work provided you have
the expertise to make it work.

Cheers
Ken



More information about the thelist mailing list