Database options
Hello,
I was wondering, what database do you guys use in your jobs, if any? Is there any which you would really recommend? Do you have any problems with it? Are there any missing features you would like it to have? Did you have to pay for it and about how much? What are the use cases you use it for?
You know, I am looking for a database for one project and I am not really satisfied with those I have tried. So I thought I might just as well ask around for some tips...
Thanks,
Patrik
I was wondering, what database do you guys use in your jobs, if any? Is there any which you would really recommend? Do you have any problems with it? Are there any missing features you would like it to have? Did you have to pay for it and about how much? What are the use cases you use it for?
You know, I am looking for a database for one project and I am not really satisfied with those I have tried. So I thought I might just as well ask around for some tips...
Thanks,
Patrik
Post edited by Patrik Rak on
Comments
Less meh, than what we used before, CASE, which was basically based on police custody software, I think. But, yeah, don't use it. Use something else.
Glad I could help. :D
Currently Vertica and Oracle. In the past I've used SQL Server, and MySQL. Oracle is expensive. Microsoft's migration strategy from one version of SQL Server to the next is terrible. The less said about MySQL the better. If you're doing data warehousing then a columnar database like Vertica might make sense (writes are slow but reads are very fast). But it would have to be a vast amount of data. And then for really big data you have various NoSQL options. But unless you're Google or Facebook, for a general purpose database I would recommend PostgreSQL.
At work 99% of the time it's SQL Server which is a superb database engine. For my personal website I collate all of the data in an Access database which I convert to a SQLite version (I'll explain why below).
I would say with SQL Server that unless you have access to a hosted instance in which to host your database - forget it. There are ways of hosting a local version but unless you have the tools and software it's not really worth considering. I've not used MySQL much, only hosted instances like SQL Server - someone else will be able to tell you if you can use local versions.
Access is fine for installed applications but if you're looking to host a website with it as the database engine I would consider looking elsewhere. It supports IIRC up to 25 concurrent users and that quickly gets used up.
SQLite would be my preference. It's fast, easy to use and perfect for websites. It doesn't require anything other than a dll to connect to a SQLite database so if you use ASP.NET it's simply a case of dropping the dll in your bin folder and the database in whatever database folder you choose (I'm sure with PHP and other languages it's just as simple). If you know the TSQL syntax of MySQL you'll pick up SQLite very quickly. Response speeds are almost SQL Server fast and there are plenty of IDE's out there to administer the database - there are even Firefox plugins for it.
As I said, my website uses SQLite (well, two actually, one as a copy of the Access database I mentioned, the other for collecting stats) and I've no intention of ever moving from it even though I have access to a kick-ass SQL Server instance. There's also tons of info a Google away.
Deciding to go with Oracle is like deciding to buy a herd of elephants for the milk. You take it on because something about it sounds clever (like writing triggered scripts in Java) then it sit down on your servers and won't ever budge.
In fact, avoid anything that has ties to any sort of CRM package as they're all dinosaurs.
Informix used to be good - I don't know if IBM's takeover has damaged it.
For free, if it's in-house only, or a single server, and it's not going to grow massively, then MySQL is okay. But if you're even remotely considering selling the result, stay away as they will lie and cheat you out of your rights over open-source software. The alternative everyone seems to have been going over to in the last decade is PostgreSQL, though I haven't used it myself. It seems to be well supported on Linux servers. It does have installers for a Windows version though.
But yes, I've also heard good things about SQLite for personal uses.
- IONIAN-GAMES.com -
Sometimes VSAM... ;)
Thanks for that Andrew. That's got me thinking about converting the stats collation database of my site from SQLite to something more suitable for the purpose. Slow-write, fast-read is exactly what I require; it's never occurred to me to look at a NoSQL option!
SQLite is free, as are the IDE's.
Access comes with MS Office, so whatever that costs as you'll probably need it for the IDE.
SQL Server - ranges from free to a lot!
SQL Server's free edition is Express Edition. It is actually very good but unsurprisingly does come with some limitations. The max amount of memory it can use is 1Gb and SQL Server eats memory like you wouldn't believe! Max database size is 4Gb (edit: 10Gb in 2008/2012 versions) and it's limited to using one core of a CPU.
For testing purposes it's great and in limited personal/commercial environments you can get away with it.
If someone else is paying the bills you'll never go far wrong with Oracle, but if you've got to pay for it out of your own budget PostgreSQL isn't that far behind.
Although I'm not a Microsoft fan, I did like SQL Server - it was a good solid product but it has some shortcomings that mean I only use it if I'm forced to (in other words, if some 3rd party system we have to use has a dependency on SQL server) - part of the shortcomings is that SQL Server also has a bit of a licensing minefield (something like 1000 SKUs for the product) and starts getting very expensive if you ever want to do anything that requires multiple instances. The other big shortcoming it has is that it requires Windows and can only be adequately managed using Windows, and I don't like Windows on the server. So personally I won't use SQL Server for anything I develop.
Basically I have two go-to relational databases: For simple stuff, MySQL is great, it performs well, and has grown up considerably in the last few years. For more complex stuff PostgreSQL is a lot more powerful and has better and saner support for things like stored procedures (doing the more complex stuff with MySQL leaves you frustrated and coding it in your application logic, I find - although as I said, things have improved greatly over the last 5 years).
I can't speak for the NoSQL options, I've thought of using them but every time I consider one for a project, when I sit down and think of it, it turns out the relational model is the best tool for the job after all.
You've just reminded me that there's a version of SQL Server that helps with the minefield of licencing options that's probably no use to Patrik but might help others. It's the Web Edition.
With the Standard/Enterprise/etc versions of SQL Server you have a couple of licencing options - CAL and Core.
CAL (Client Access Licence) is the number of users that can concurrently access your SQL Server instance and you buy them in packs of five IIRC. What it means is that for websites you would very quickly use up your allocated amount as users access so it's an absolute no-no for web-based applications but a yes for internal systems (where you control the number of users on the network). This is generally the cheaper option until you hit a large amount of users.
Core Licences are based on the number of cpu cores that can access the SQL instance. It has no CAL restrictions but is a freakin' nightmare to wrap your head around the licence/pricing! It's also very expensive.
Web Edition basically gives you a number of CALs that effectively makes that infinite and I think gives access to cpu 16 cores or 4 cpu sockets. It comes with it's own drawbacks though - it's not allowed to be the database engine for CRM's and its features sit somewhere in between Express and Standard editions. We use it though and it works great for us.
Postgresql is a really powerfull database that will do just about everything, but i've not yet transferred any of my programs to it yet, but really need to find the time too.
I use MySQL daily because I build web applications and websites
Im currently looking for something along the lines of that, that is also cross platform useable from Windows to Android.
I am afraid the standard SQL won't make it. I need to deal with tables with few hundreds of millions rows and the amount of data in about terabyte range, with decent speeds for both reads and writes. So I was more looking at distributed solutions which scale out, as well as those which are not limited by rigid schema. Not being a complete pain to develop for would be a bonus, too. So I was hoping that some of you might perhaps have a first hand experience with some of the NoSQL or NewSQL solutions, but from your answers it seems that most of you don't need to use those at all... Which is an interesting find, too.
Anyway, thanks for sharing your experiences. I appreciate if you'll keep them coming...
Patrik
NoSQL and other "popular" stuff have very niche uses, and I wouldn't even consider them at all in your case.
Games List 2016 - Games List 2015 - Games List 2014
I'd still go with PostgreSQL, but I'd also stick a minimum of 64GB of RAM in the server and go for the fastest drives you can get. Unless you have access to a server farm then NoSQL solutions won't be as fast.