MySQL vs MsSQL – comparisons never end
Search for the title, and you will land in several sites, which will tell you the views of the author. In fact there may be hell lot of people who would tell you that one out performs the other. But in reality what does that mean ?. If one knows how to optimize the database server, the sql, and the code behind. The performance can be tuned to a factor that both are at par. Now that is a far fetched view. Just think of the real bare minimum hardware to handle 300 tables, with less than 50 having a million rows, and the rest having master data. The MsSQL server will need a min of dual core with 8G memory, whereas the mysql would run on a 2G Virtual Machine and even out perform the MsSQL server. Think of the SELECT …. LIMIT X,Y and the SELECT GROUP_CONCAT, the advantages when considering server side pagination as well as selections directly outputting JSON for ajax frontends.
Now if you think that is the limit, no, the DBA could even choose MyISAM for the master records, InnoDB for the transactional tables, and Memory for those tables which is needed only while system is running. Also tweaks like Query Cache, Read Buffer Size, Index Size, Temp Table size etc would affect the performance heavily. One should not forget running the server on a linux os, will help by reducing the overheads of the hardware by providing socket communications for host only traffic, large filesystem for data intensive partitions, and continuous running for long periods. I have run servers for more than a couple of years without restart.
The pagination part in MsSQL is not exactly of my taste, though I agree that it may be due to the fact that I am familiar with the other piece of code. Just see the reference on Planet Source Code, which tells us aloud that before he arrived at the highlighted piece of code, he too used to transfer the whole load of junk and rely on the adodb to handle his paging. The typical M$ way of leading people and make cabbage out of good minds.
I am quite confident about my claims above, and that I have proven through a live product. Our developers are maintaining and developing using mysql and php on a Linux platform, demo servers running lighttpd, fastcgi, php and mysql. The release and patches are deployed onto our client’s environment which uses Windows 2003, IIS 6.0 and MsSQL 2005. I dont do any kind of code rewrite, just maintain the functions in different environment, but all the queries are rewritten using regular expressions, and static replaces. The system took about three months to stabilize, but now the whole of our team (Ratheesh, Anju, Divya, Anand, Lakshmi and myself) are rejoicing that the system is stable. There are others too, whose names I left out without intension, though cannot be forgotten.