Choosing your application database

How to choose the database that will store your application data? This is a question that many ask when starting a new system and sometimes ends up opting for brands just because they know the reliability of the company, but they do not choose to perform really native tests.

I was recently working on a new project which will be a web application using the concept of process farm (pool in work farm) and I had to choose between the best database.

Remember: not always a database for a certain type of application, it will be better for another, that is, each database can be better at certain tasks that others are not.

When starting the choice between the best bank, the most popular databases were chosen to be used for a Web environment: SQL Server and MySQL. The chosen technology was ASP Classic, because it is the one I work with today and I know how to make connections with the bank no cake recipes; what I need to test is the number of queries executed per bank, regardless of the front-end application at the moment.

In a looping process, more than 1 million insertions of insert-type queries were performed in a table, and in the middle of the process, query and data removal processes were also carried out.

Both SQL Server and MySQL were subjected to the same test, always using SQL queries within ANSI standards, to ensure that differences do not affect the result. (Eg: SQL Server needs the TOP element, while MySQL uses the LIMIT command, which were not used in the tests).

In all tests, MySQL surpassed by about 3 seconds compared to SQL Server. A low time in which it would allow to choose SQL Server, if the item was branded, but for a web application where countless users access at the same time, the shorter the time the better, and in addition, most hosting plans (international and Brazilian ) do not impose limits on the size and use of several databases at the same time.

Continuing the tests, today we still did a test to prove who is better:

- File System

- MySQL using LONGBLOB to save files in cells.

It sounds like a joke, but yes, MySQL exceeded by 0.25 seconds and was faster than a server's NTFS file system.

When downloading a 4Mb static image, the server delivered an average of 4.45 seconds.

When calling the connection with the MySQL database and making the query to bring the cell containing a copy of this image with the same 4Mb, the image arrived in the browser in an average of 4.15 seconds, always below the average brought by download.

We didn't even waste time doing the test with SQL Server (which depends on the file system to manage the database).

MySQL was chosen for this application, because in addition to being a little faster than SQL Server, it was also excellent for storing and retrieving files recorded in LONGBLOB (we tried with Blob but it did not allow uploading very large images).

For those who do not know what is a web-farm or a farm application, here are some tips on what a traditional web application is like:

Traditional web application (Windows platform, IIS):

- Application is executed in only 1 work process (w3wp.exe).

- User session (and its variables) are stored in the server's RAM memory (next to the w3wp.exe process).

- Application variables are also stored in the server's RAM memory (next to the w3wp.exe process).

- When a user logs in, w3wp.exe coordinates the execution of the application, always one page at a time in a row, that is, there is a pipeline for processing the application pages for all users in a single bottleneck.

- It only works on a computer.

- It does not allow to distribute the application among other machines.

- Distribute only with the concept of a distributed virtual machine, which depends on its own servers and a good infrastructure to ensure services are always up and running.

Web-farm application:

- Application runs on 1 or thousands of work process (multiple w3wp.exe running in parallel).

- User session needs to be kept in a bank, as each page process may or may not go through w3wp.exe that has the user's information, that is, you cannot use the standard user session format of the programming language.

- Application variables must be loaded from the database, as in the same way, if the page is executed in a different process in execution, the application data may change.

- It does not depend on w3wp.exe or the server's RAM memory to hold the user's session.

- High use of the database.

- Allows you to distribute the application among several front-end computers and use any type of programming language compatible with the web server running.

- Allows you to leave a user logged in to the system forever, until he leaves or for a period (carried out in the schedule capturing the date of the last login login).

- Allows to compute a refined status of the users actually logged into the application.

- It allows spreading among other countries and using servers close to users using geo-location.

- Allows you to use generic, inexpensive servers, easy to find in any hosting plan.

- Requires a good database server with the potential to support many transactions.

As I said, not always a database chosen for one type of application will always be ideal for other types, but in our case, for example, we do not carry out tests on a database from Oracle, Postgre or DB2 from IBM, because they are not easily found in hosting plans for small businesses (Locaweb, UOL Host, etc.), which even charge additional fees for bases such as SQL Server, and in the case of MySQL they always provide on average 10 free bases for use without restriction of size.

Note regarding the bank file upload test: Image file tests were performed with files up to 5 Mb in JPEG format.