whenpenguinsattack.com

Tuesday, January 10, 2006

mysql or microsoft sql?

(originally from mssqlcity.com)

Introduction

Often people in newsgroups ask about some comparison of Microsoft SQL Server and MySQL. In this article, I compare SQL Server 2000 with MySQL version 4.1 regarding price, performance, platforms supported, SQL dialects and products limits.

Platform comparison

SQL Server 2000 only works on Windows-based platforms, including Windows 9x, Windows NT, Windows 2000 and Windows CE.In comparison with SQL Server 2000, MySQL version 4.1 supports all known platforms, including Windows-based platforms, AIX-based systems, HP-UX systems, Linux Intel, Sun Solaris and so on.

Hardware requirements

To install SQL Server 2000, you should have the Intel or compatible platforms and the following hardware:

Processor: Pentium 166 MHz or higher
Memory: 32 MB RAM (minimum for Desktop Engine),64 MB RAM (minimum for all other editions),128 MB RAM or more recommended
Hard disk space:

270 MB (full installation),250 MB (typical),95 MB (minimum),Desktop Engine: 44 MB
Analysis Services: 50 MB minimum and 130 MB typical
English Query: 80 MB MySQL version 4.1 is not so powerful as SQL Server 2000 and uses less hardware resources.

To install MySQL version 4.1, you should have near 32 Mb RAM and near 60 Mb hard disk space. The general MySQL version 4.1 installation does not require additional CPU resources.

Software requirements: SQL Server 2000 comes in six editions: Enterprise, Standard, Personal, Developer, Desktop Engine, and SQL Server CE (a compatible version for Windows CE)

MySQL version 4.1 comes in two editions:

The Standard edition are recommended for most users and contains general MySQL features. The Max edition includes additional features such as the Berkeley DB storage engine, OpenSSL support, user-defined functions (UDFs), and BIG_TABLE support.MySQL version 4.1 requires the following software:

Platform
Operating System Version
Windows-based
Windows 95/98/NT/2000/XP/2003
Sun Solaris
Solaris 8 (SPARC)
FreeBSD
FreeBSD 4.x (x86)
Mac OS X
Mac OS X v10.2
HP-UX
HP-UX 10.20 (RISC 1.0),HP-UX 11.11 (PA-RISC 1.1 and 2.0),HP-UX 11.11 (PA-RISC 2.0, 64-bit only)
AIX-Based
AIX 5.1 (RS6000),AIX 4.3.2 (RS6000),AIX 4.3.3 (RS6000)
QNX
QNX 6.2.1 (x86)
SGI Irix
SGI Irix 6.5
Dec OSF
Dec OSF 5.1 (Alpha)

Performance comparison

It is very difficult to make the performance comparison between SQL Server 2000 and MySQL version 4.1. The performance of your databases depends rather from the experience of the database developers and database administrator than from the database's provider. You can use both of these RDBMS to build stable and efficient system. However, it is possible to define the typical transactions, which used in inventory control systems, airline reservation systems and banking systems. After defining these typical transactions, it is possible to run them under the different database management systems working on the different hardware and software platforms.

TPC tests The Transaction Processing Performance Council (TPC.Org) is independent organization that specifies the typical transactions (transactions used in inventory control systems, airline reservation systems and banking systems) and some general rules these transactions should satisfy.The TPC produces benchmarks that measure transaction processing and database performance in terms of how many transactions a given system and database can perform per unit of time, e.g., transactions per second or transactions per minute.The TPC organization made the specification for many tests. There are TPC-C, TPC-H, TPC-R, TPC-W and some old tests, such as TPC-A, TPC-B and TPC-D. The most popular test is the TPC-C test (OLTP test).At the moment the article was wrote, SQL Server 2000 held the second position in the TPC-C by performance results. See Top Ten TPC-C by Performance Version 5 Results At the moment the article was wrote, SQL Server 2000 held the top TPC-C by price/performance results.See Top Ten TPC-C by Price/Performance Version 5 Results MySQL does not participate in TPC-C tests, they make their own benchmark tests. These tests are not independent, but if you interesting, see this link:The MySQL Benchmark Suite

Features comparison

Both SQL Server 2000 and MySQL version 4.1 support the ANSI SQL-92 entry level and do not support the ANSI SQL-92 intermediate level. In the Features comparison section of this article, I want to make the brief comparison of the Transact-SQL with MySQL dialect and show some SQL Server 2000 and MySQL version 4.1 limits.

T-SQL vs MySQL dialectThe dialect of SQL supported by Microsoft SQL Server 2000 is called Transact-SQL (T-SQL). The dialect of SQL supported by MySQL version 4.1 is called MySQL dialect. Transact-SQL dialect is more powerful language than MySQL dialect.

Conclusion

It is not true that SQL Server 2000 is better than MySQL version 4.1 or vice versa. Both products can be used to build stable and efficient system and the stability and effectiveness of your applications and databases depend rather from the experience of the database developers and database administrator than from the database's provider. But SQL Server 2000 has some advantages in comparison with MySQL version 4.1 and vice versa.

The SQL Server 2000 advantages:

SQL Server 2000 holds the top TPC-C performance and price/performance results.
SQL Server 2000 is generally accepted as easier to install, use and manage.
Transact-SQL is more powerful language than MySQL dialect.

The MySQL version 4.1 advantages:

MySQL version 4.1 supports all known platforms, not only the Windows-based platforms.
MySQL version 4.1 requires less hardware resources.
You can use MySQL version 4.1 without any payment under the terms of the GNU General Public License. This is from MySQL version 4.1 documentation:MySQL Server was designed from the start to work with medium size databases (10-100 million rows, or about 100 MB per table) on small computer systems.

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home