Comparison of database platforms
My employer has a database committee and we are discussing different platforms. So far, we have had people present in SqlLite, MySQL and PostreSql. We are currently using the Microsoft stack, so we are all very familiar with Microsoft Sql Server.
As part of this comparison, I thought it would be interesting to create a small reference application for each database platform to learn the details of working with it.
First: does it make sense, or does this comparison require going beyond a trivial example application?
Second: I would suggest that every referenced application has a discrete but small set of requirements that satisfy many of the scenarios we encounter on a regular basis. Here's what I have so far, what else can I add to the list, but still keep the application small enough to be built in a very limited time?
-
Application layer connectivity
-
Database administration tools
-
The process of creating a schema (small "s" schema, tables / views / functions of other objects)
-
Simple CRUD (create, extract, update, delete)
-
Transaction support
Third: has anyone gone through this process, what are your findings?
source to share
In fact, the learning capabilities of each RDMS are more important. Because it depends on the application. If you need spatial data capabilities, PostGIS with PostgreSQL is better than MySQL. If you want lightweight replication, MySQL's high availability capabilities look better. There are also license issues. The link for comparison is here . We all have strengths and weaknesses. Get the requirements for your project or projects first, not compare it to the list of RDMS features you choose and decide which one should be done.
source to share
Does this idea make sense or does the comparison require going beyond the scope of trivial sampling?
I do not think it's a good idea. Most of the things that will really affect you are database management issues in the long run, and how the database management system you choose can handle these things.
In the short term, you may be tempted by things like "I found out in 3 seconds how to do this with the XYZ database management system." Now I am not saying that support is not important; vice versa. But finding an answer on Google in 3 seconds means you've got an answer to a simple question. How quickly, if ever, can you find the answer to a difficult problem?
A short (non-exhaustive) list of important things to consider:
- backup and recovery - both at the logical level and at the physical level
- good support for functions (or stored procedures), triggers, various SQL query constructs
- APIs that allow real extensibility - these things can get you out of tough situations and allow you to solve problems creatively. You would be wondering what you can do with custom types and functions. How do custom types interact with the indexing system?
- Standard SQL support - won't beat everything else, but if support is missing in a few areas, really think about why it's missing, what are the workarounds, and what the cost of those workarounds are.
- A powerful executor that offers a number of fundamental algorithms (like hash join, merge join, etc.) and indexing structures (btree, hash, possibly full text, etc.). If some algorithms or index structures are missing, consider the types of questions that the database will be ineffective in answering. Note. I don't mean just "slow"; the wrong algorithm can easily be orders of magnitude worse.
- Can a type system intelligently represent your business? If the set of types available is incredibly weak, you will have a mess. Thinking of everything as strings is like assembly programming (untyped) and you will have a mess.
A trivial application won't show you any of these things. Simple things are easy to solve. If you have a “database committee,” then your company cares about its data and you must take responsibility seriously. You need to make sure that you can easily develop applications with the results that you and your developers expect; and when you run into problems, you need to have access to a powerful system and quality support to help you get there.
source to share
First, you are outside the scope of the sample application in my humble opinion.
Second, I would choose the most suitable tool or application that you want to develop. For example, are schemas and transactions relevant to the database that stores the single-user application configuration?
Third, I've worked with Access, SQL Server, SQLite, MySQL, PostgreSQL and Oracle, and they all have their place. If you are in MS space, go to SQL Server (and don't forget Express). There are also ADO.NET ways to talk to the others on my list. It depends on what you want.
In all honesty, I doubt that an arbitrarily defined simple application is likely to really highlight the differences between database engines. I think you'd better read the promotional literature for the various engines to see what they see as their strengths. Then consider which of these questions you need and create tests specifically designed to validate the claims you care about.
For example, here are the pros and cons of the database engines I used the most that mattered to me. I am not suggesting that this is an exhaustive list, but it may give you an idea of what one might think:
MySQL: Note: MySQL has two main engines: MyISAM and InnoDB. I've never used InnoDB. Pros: Fast. Free for cheap depending on how you use it. Very convenient and easy-to-use commands for managing the circuit. Some very useful extensions to the SQL standard such as "insert ... on duplicate". Cons: MyISAM engine does not support transactions, i.e. no rollback. The MyISAM engine does not manage foreign keys for you. (InnoDB doesn't have these flaws, but as I said, I've never used it, so I can't comment further.) Many deviations from SQL standards.
Oracle: Pros: fast. Overall, good compliance with SQL standards. My brother works at Oracle, so if you buy there you will be helping my family. (Okay, maybe it doesn't matter to you ...) Cons: Difficult to install and manage. Expensive.
Postgres: Pros: Very high SQL standards compliance. Free. Very good "explain" plans. Cons: Relatively slow. The optimizer is easy to confuse with complex queries. Some awkwardness in changing existing tables.
Access: Pros: Easy to install and manage. Very simple circuit management. Built-in data entry tools and query builder for quick and dirty stuff. Cheap. Cons: Slow. Unreliable with multiple users.
source to share
I think you can research Firebird too
This is an excerpt from Firebird-General on yahoogroups and I find it pretty objective
Our natural audience is developers looking to package and sell proprietary Applications. Firebird is easier to package and install than Postgres; more capable than SQLite; and don't charge royalties like MySQL.
source to share