Today is a good day to code

SQL Server 2000 vs MySQL

Posted: December 31st, 1969 | Author: | Filed under: Microsoft, Uncategorized | Tags: | No Comments »

SQL Server 2000 vs MySQL

Picture of Irv Owens Web DeveloperOne of the funny things about comparing these two systems is that they are both medium sized DBMS systems. Neither is really suited to huge enterprise level solutions, although both companies will say that they are ready for them. I have not implemented any system on Oracle, Borland, or DB 2 so I can't truly speak for their performance on an official level. On an unofficial level, however I can say that I have noticed that they are significantly slower with lighter load. With heavy load, however they perform much better with Microsoft SQL Server 2000 and MySQL falling off quickly. This is evident simply when taking a look at hosting companies who provide either SQL Server 2000 or MySQL, in a local installation, these systems are blazing, returning results for queries often in under 100 ms. In production however, when using shared hosting most of my queries take anywhere from 250 ms to regularly hitting 2000 ms. Both of which put me outside the desired window. When using Oracle locally, performance hovers around 100 ms. When using an Oracle based system in production, performance still seems to hover around 100 ms. I have heard and witnessed to some degree that Sybase feels the fastest, which each subsequent iteration of a query moving faster. Sybase is probably the smartest DB as far as adapting to use, while Oracle seems to be the strongest.

Frequently however for most web applications, SQL Server 2000 or MySQL seem to be just fine. Between the two of them, after working with SQL Server for some time, I have come to really love the enterprise manager, with it's associated tools, the Query Analyzer and the Profiler, however when working with full-text indexes, MySQL wins hands down. As far as performance is concerned, from what I have seen MySQL is faster with a moderate load. SQL Server seems better with heavier loads, and appears to have better caching. As far as that goes, it has MySQL flat out beat. MySQL could do a better job of using the cache for similar queries, for example if I were to use SQL like this:

SELECT id, username, password, lastAccess
FROM testDB

It would return that dataset and hold the dataset in memory. But, it wouldn't be smart enough to realize that this query is just accessing a subset of the one above:

SELECT id, lastAccess
FROM testDB
WHERE id < 100

From what I have seen, SQL Server does a better job realizing that those two SQL snippets are utilizing the same recordset. MySQL would treat the last query as a completely new query and go to the disk to get the data. This may improve some by using MySQL's enterprise version, Max DB, or in version 5, but from what I have seen in the betas, it doesn't seem much better.

But with the ease of full-text searching in MySQL, I will almost always prefer it to SQL Server 2000 until Microsoft starts using a built-in full-text searching solution.

Look at this:

SELECT id, field1, MATCH (field1,field2) AGAINST
('text to be searched') AS score
FROM table WHERE MATCH (table1,table2) AGAINST
('text to be searched')

That is so simple and easy to understand. Creating the index is just as easy. Creating a full-text index using SQL Server is fairly complicated, primarily as I indicated above because you have to deal with an external application. The full-text searching rules built into MySQL such as the 50% rule as you can see in effect by visiting Owens Performance Blog Search makes querying an effort for the user, but much of that can be handled by decent business logic.

Ultimately it comes down to performance for price. When compared this way, MySQL wins, it is free. Still, MS SQL Server 2000 is a good value, given it's strong performance, robustness, and quality of tools. I will never hate using it for jobs. Still, if given my preference I would choose MySQL.


Windows Vista SuperFetch

Posted: December 31st, 1969 | Author: | Filed under: Apple, Microsoft, Uncategorized | Tags: , | No Comments »

Windows Vista SuperFetch

Picture of Irv Owens Web DeveloperMost PC users are plagued more and more by chronic slowdowns the older their PC gets. Some of this, probably most of this is due to spyware, but a vast majority of this is caused by software loading into the system tray. The tray is an area in which a program can run without having to have an interface, or appear in the taskbar. This was a great idea with the introduction of Windows 95, but in the era of many computer users running greater than 1 GB of RAM it seems un-necessary, or at least that it should be redesigned in Vista.

Microsoft is attempting to remedy the problem of slowness as well as acknowledge the larger amounts of RAM in currently shipping systems by building a technology called SuperFetch. Which is a fancy caching program designed to notice which software you use most frequently, and load that into memory at start-up. This can improve the percieved performance benefit to some users, but probably would end up being a technology that would get in the way of most power users. It would in a way take users back to the heady days of Windows 3.11 when we could choose how much system memory was to be dedicated to the applications, and how much was to be reserved for hard drive cache. This system would behave in much the same way, but instead of the user being in control, the system would adjust automatically.

In attemping to understand why the system tray is strange, and probably should be either redesigned or removed, it is a good idea to look at how Apple as well as Microsoft handle GUI-less applications. In Windows developers have the ability to create full applications that run in the background without disrupting the user, or appearing on their display. These programs are known as services. Probably the most widely used service is the IIS service. IIS, if you don't know already, is Microsoft's web server product. It is similar to Apache, except it has the ability to process scripts built in to the server. Whereas with Apache it is necessary to include modules for this functionality. As a result Apache is much more flexible, which could change if Microsoft is truly to build PHP support into IIS. But I digress. Some applications such as Apache for windows run as a service, and also include a system tray icon for management. In Mac OS X applications without a GUI typically either run without any notification to the user, or place a small icon in the upper right of the screen, such porgrams are fairly rare, since most applications on the Macintosh load themselves into memory at their first use and remain there. Since it is quite unnecessary to turn the Macintosh off, or to reboot most of the time, your programs always launch insanely quickly. It would be best for Microsoft to implement a similar type of system. This would minimize the overhead necessary for keeping frequently used programs in memory, and would be vastly less complex.

Another potential solution could be to automatically dump programs from the system tray if they aren't being actively manipulated by the user, or performing some operation on the system. This would do two things beneficial to the system. First, it would free system memory, handles, and other resources for use with software that is actually doing something. Second, it would discourage developers from using the system tray as a place to put their icons. To supplement this model, Microsoft should encourage developers to write services for Windows as opposed to tray software. The way it could work is that the first time this happened, the system would notify the user that it was closing the applications and ask them if they wanted to be notified in the future. That way the user would remain in control. On systems with less than 512 MB of RAM this simple system would pay back sometimes enormous benefits, reducing the idle footprint of the OS from around 400 MB on some systems back to a managable 256 MB. Microsoft is over-engineering this one, they should review the K.I.S.S. software development methodology.