More on simple databases

When I think about it some more, the difference between something like MS SQL Server or DB2 vs Hibernate + MySQL with MyISAM, is that the traditional way is for the RDBMS to be a single system, relational management AND static data storage. The persistence layer/db strategy is separating the RMS (relational management system) from the DB (static storage). By separating out the relational management portion, and moving that into the persistence layer, you have the ability to run one processing cluster which serves your application and handles the persistence layer/rms, as opposed to running two clusters, one to serve your application, and one to handle the traditional RDBMS. I would think that combining your servers into one larger cluster to handle more of the computing would be more efficient than splitting it into two clusters, in which case one cluster may hit max load while the other sits idle.

By the way, I know that there are measurable differences between MyISAM, InnoDB, or other storage engines, like row level locking as opposed to table locking on updates.

How I'm learning to love simple databases

Once upon a time I was a total believer in strict database rules. Everything should be fully normalized, schema and diagrams thought out ahead of time, strict data validation at the database level, so on and so forth. A total believer in the primacy of MS SQL Server, DB2, so on and so forth. Within the last 6 months, I've had a change of mind that's been brewing, and only lately have I more fully realized it.

When you're developing a webapp nowadays, chances are you have various frameworks and technologies that you use, each one layered on top of the other. MySQL -> Hibernate -> Java application -> JSP/Servlets -> HTML/markup, as one example. The data that you accept in from your users must pass through each layer to finally get to your storage, whether it's in memory at the application layer, or static storage at the db. Each layer has its own data validation. So if you have a date field that a user fills out, it comes in as a string, which the Servlet can accept in and convert to a valid Java date, which passes it to your application layer, in which you can put that data in an object that checks that it's a valid date (if it's defined as a Date object). Then it goes to your persistence layer, which in our example, Hibernate would check that it's a valid date. It finally gets passed to your database layer, which in our example, MySQL would check that it's a valid date. All that work to verify that it's a date several times. Why? What's the point? If you checked that it's a valid date once, and you don't do any measurable transformation of the data, there is no need to check it again.

If I think about it, when is it exactly, that I care that I check if it's a valid date? When I accept data in from the user, when I do fancy things at the application layer like date/time arithmetic, and when I display data back to the user. None of that touches the database layer. Do I care if the static data is stored in a traditional RDBMS, a bunch of XML files, or a straight up B tree like GT.M? Other than the possible performance gains by storing static data one way or another, it doesn't matter.

I currently use Wicket, and Wicket has easy to use form validation built into the framework itself, which covers all the cases in which I would care to check if the data is appropriate or not. Right now, I do datatype management at each layer because that's how I was taught, but I'm seriously considering switching everything underneath Wicket to string, integer, or float, crossing my fingers, and hoping nothing blows up.

Beyond data validation, what about primary/foreign keys? Many databases don't even have pk/fk relationships built in. Originally, I thought those databases were mad (in a bad way). However, if you think about it, the business logic which says that one table must relate to another table via a field based relationship, that business logic has to exist in your RDBMS, your persistence layer, and your application (because you must pass data with the appropriate relationships to the layer below). Why should each layer check the relational integrity of the data? The only case I can think of, is to protect myself from making a mistake at the application layer. Fine then, I can set those rules in the persistence layer as well as my application, and the database can be simply a static container, nothing more.

MySQL Auto Data Conversion

So I've been researching MySQL, and I have run into something that kinda boggles my mind. Whenever you give MySQL the wrong datatype for a field, MySQL attempts to do autoconversion of your data. This in itself isn't a bad thing, and some of them may make sense, such as doing string to int or int to string. However, if you pass MySQL data that it is out of bounds, MySQL chooses data that is in bounds, and places that data in the field instead. So if you have a field defined as a tinyint, whose bounds are -127 to 127, and you pass it 1,000, MySQL will input 127, and consider it a success. 

You have to write your own MySQL specific data validation in your application BEFORE you pass it to MySQL..... Say it with me..... WHAT??? The whole idea with abstraction and error catching is that each layer of your software stack should be able to pass "something" to another layer and receive back success or failure, at which point the originating layer can decide how to handle it. By having your application layer have to do database validation checks BEFORE passing data to MySQL, you loose the benefits of abstraction, and by definition, you loose the benefits of modern software engineering. 

Somebody out there explain to my how MySQL's handling of out of bounds data is a good idea, cause I don't get it.

Comparisons between GT.M and other software ecosystems

Since the beginning of modern computer programming (circa 1980 or so), people involved in EHR's have been trying to compare Mumps to say, C/C++, or Java, or VB. Then they try to compare Mumps to common database engines, such as SQL Server, MySQL, and Oracle. Those comparisons are generally not equitable all around, as Mumps is a combination of static file storage, programming language, runtime environment, and related utilities.

As a static file storage system, Mumps alone is inequitable to SQL Server, or other DBMS's, since Mumps only stores data in B-trees. Note that in the extreme back end of SQL Server, data is also stored in B-trees, but that level is never displayed to the developer. Instead, you interact with your B-tree data by using SQL the language. VistA solves this by using Fileman, which in itself is a combination DBMS, Display API, Database API, and various programmer level utilities. So rather, a better comparison would be the disk IO speed of Java vs Mumps globals, or the query speed of SQL Server vs Fileman. As a side note, Fidelity just released a beta version of PIP, which is their own relational engine, available on sourceforge. I'm really excited to try it out!

Mumps is also not directly equatable to Java, or C. Since Mumps is also a runtime environment and set of database utilities in addition to a programming language, it's better to compare Mumps to Java and JVM, or C# and .NET, or C# and WINE.

We were running some numbers at work on a virtual machine running Ubuntu 7.1, and in a nutshell, GT.M is slower than Java and C for number crunching, but GT.M has a higher level of inherent number accuracy. The Java and C implementations were having number overflows when they used a datatype that was too small to handle the large integers we were computing. Note that this did not throw a runtime error, and the results looked real enough until they were compared to a sample set. GT.M did not have this problem, and got the correct answers from the beginning.

When compared to disk IO, I have not had a chance to compare mumps global speed vs Java or C disk IO, but in GT.M I can update 4,000,000 subnodes in 17 seconds. That is blazingly fast, but again, I have nothing to compare it against.

When compared as a database, SQL Server far outperforms Fileman. The general rule of thumb is that SQL Server is faster than Fileman by a magnitude of 4 to 1. I know that many people always say how "SQL Server and Oracle are slower than Fileman", but that is simply not true. My coworkers and I have used the exact same server with the same problem set, implemented in both Fileman and SQL Server, and run the same query to receive the same dataset. SQL Server outperforms Fileman, bar none.

In Fileman's defense, it is more than just a DBMS, it handles user IO, and has it's own programming API (date/time utilities, etc.).

Our next step will be to have a complete problem which requires a large database query, and number crunching as a cohesive unit. Perhaps then the Mumps model of tight integration will see an advantage as you can directly manipulate globals and Fileman from your code, whereas C or Java has to interact with SQL Server through ADO or ODBC.

I know I haven't posted numbers yet, but we have more testing that we'd like to do. I don't want to have anything indexed by google until I have a complete set of numbers to post at once in a coherent manner.