The past decade has been an extremely exciting one in all matters related to data. We have had: An ever increasing amount of data produced by social media (once called “Web 2.0”) An ever increasing amount of data produced by devices (a.k.a. the Internet of Things) An ever increasing amount of database vendors that explore … Continue reading 3 Reasons why It’s Okay to Stick with SQL
One of MongoDB's arguments when evangelising MongoDB is the fact that MongoDB is a "schemaless" database: Why Schemaless?MongoDB is a JSON-style data store. The documents stored in the database can have varying sets of fields, with different types for each field. And that's true. But it doesn't mean that there is no schema. There are … Continue reading Stop Claiming that you’re Using a Schemaless Database
There's one thing that you can do terribly wrong when working with RDBMS. And that thing is not running your calculations in the database, when you should. We're not advocating to blindly move all business logic into the database, but when I see a Stack Overflow question like this, I feel the urge to gently … Continue reading Please, Run That Calculation in Your RDBMS
In our previous blog post "10 Common Mistakes Java Developers Make When Writing SQL", we have made a point about batching being important when inserting large data sets. In most databases and with most JDBC drivers, you can get a significant performance improvement when running a single prepared statement in batch mode as such: PreparedStatement … Continue reading What you Didn’t Know About JDBC Batch
Lots of blog posts and research papers are written about the topics of scaling up and scaling out. This interesting blog post, for instance, sheds some light on the two strategies with respect to physical maintenance costs, such as cooling and electricity consumption. Certainly non-negligible aspects for very large systems. But before solving problems at … Continue reading Why Staying in Control of Your SQL is so Important
Military Campaigns?? Haha. Let's meet Capt. DBA, mastering Sun Tsu's Art of SQL: http://de.scribd.com/doc/15490992/The-Art-of-SQL Citing from the book cover: Do you view database applications as military campaigns? Do you see data as row upon row upon row of enemy columns to be winnowed down and slashed away? [sic!] SQL veteran Stéphane Faroult does. This is … Continue reading Do You View Database Applications as Military Campaigns?
A great database has just gotten better. PostgreSQL 9.3 has been released today. While improved reliability and availability is certainly quite a thrilling addition, from the jOOQ perspective, the most interesting features are new SQL syntax elements. These include: Better support for JSON LATERAL JOIN (or as SQL:1999 calls it: lateral derived table) Materialised views … Continue reading PostgreSQL 9.3 Released!
In my recent SQL work for a large Swiss bank, I have maintained nested database view monsters whose unnested SQL code amounted up to 5k lines of code, joining the same table over and over again in separate subselects combined via UNION operations. This monster performed in way under 50ms, no matter how we queried … Continue reading The Myth About Slow SQL JOIN Operations
This is MySQL's Bad Idea #573 (after #384, which I've blogged about before) I've just had a terrible experience with a bug report from the jOOQ User Group, related to escaping of backslashes in string literals in MySQL. First, I thought to myself, whatever. SQL doesn't escape backslashes. The only escape character within a string … Continue reading MySQL Bad Idea #573