How NoSQL forced the evolution of a scalable relational database

I think that might be like when rock stars die at 26 instead of hanging around long enough to become the subject of a VH1 "Where are they Now?" episode.

I was on a project that used LINQ to SQL long after Microsoft stopped development on it, and consequently fell out of love with it. The caching mechanism was flaky, and could cause really weird problems that manifested in non-obvious ways. Also, changing the database schema in a LINQ to SQL project could be a chore.

I ended up migrating us away from it and toward Dapper. We ended up bypassing it way too often to be able to straight-facedly claim it was saving us from having to understand SQL (if that was ever possible), It was the warts, but even more than that, it was the many situations where, to get the query right, we needed to use a recursive CTE, or a temp table, or a table-valued parameter, or any of the oodles of other things that are awkward or impossible with an ORM.

That's not true.

>While I was writing these queries, I was just thinking to myself "I can write this in code in a few minutes instead of an hour or more"

Well, no, because SQL is a 4th generation language of higher level than most general-purpose languages. It is a domain-specific language focused on database handling.

A simple SELECT with a few joins and indexes involved encompasses a query execution plan that would be a few hundreds of code in a regular programming language.

>I chose Mongo for a project recently.

Storing relational data on a document store is a bad idea.

And if you need a document store, Mongo is pretty much a bad option.

It doesn’t have a free tier, I can’t use it easily and just seems like a lot of hype without any 3rd party to back up their claims. Its the kind of things mongodb used to say to gain mindshare.

Most popular databases like MySQL and Postgres gained its mindshare because they were open source and anyone could verify they did what they said. Only a couple closed source databases have won it big. Behind them were huge monoliths like Microsoft and Oracle with armies of sales teams.

I’m not saying MemSQL will die. They are probably quite profitable. I work for an analytics company with a custom database. It’s just the average joe doesn’t get much from it. The cloud based dbs gets the average joe very far.

My personal opinion is akin to the parent: the more you abstract away from thinking like your data store, the easier it is to write poorly performing code, because some of the fundamentals get lost in the process.

For instance, in EF and other ORMs, one classic pitfall is "N+1" queries. Even though it's a well known issue to avoid, I still run into poorly performing loops quite often. Here, I feel the abstraction is partially responsible. Most for loops run in code (in memory at least) very quickly, even against a lot of records. However, those who develop against SQL know that you avoid cursors etc. as much as possible, and that for loops / cursors against a SQL database will bite you the instance you are dealing with thousands of records to loop through. I don't think it's always "obvious" to, say, a relatively new C# developer with less experience writing SQL, that this for loop against a SQL database is going to literally translate into thousands of select statements that will perform poorly.

I think it's possible to use a heavy ORM like Entity Framework and write well performing code, but there has to be some awareness of when the task is too complex for the ORM to perform well, and you need to switch to a stored procedure. And there has to be at least a little bit of understanding on how to write well performing interactions with the DB.

Most ORM code bases I've looked at do a pretty decent job with avoiding N+1 queries. But they've all been guilty of doing things like incurring orders of magnitude more network transfer than is necessary by pulling down a collection of objects just to calculate the sum over a single field.

I think that stuff ends up being so common because it's exactly what heavyweight ORMs are encouraging you to do. To an approximation, their entire point is to make it easy to forget that you're talking to a remote resource.

And, for that matter, by hiding the subtlety of the relational model, they also hide the power of the relational model.