Frameworks O How I Hate Thee

I’ve seen a lot of tech come and go in my time, but nothing I’ve seen vexes me more than “framework generated SQL”.  No doubt I’m ignorant about some aspects of it, but its usage continues to confound many a DBA.

To troubleshoot one of these bad boys, you might consider Google Glass, but it will fail you. The first issue is that these crappy frameworks generate a code tsunami that’s almost (or actually) unreadable by humans. The tables you know and love are aliased with names such as “Extent1” and the like. Multiple nestings of that, and it’s all gobbledygook aka spaghetti code.

Developers love frameworks, because they don’t have to spend time coding/maintaining SQL queries. I’m guessing it’s mostly used for UI, cause if it’s used for much more than that, performance is likely to absolutely suck. So you wicked smart developers theoretically save a bunch of money generating SQL for your UI, but then – because you have a totally crap schema – you have to pay expensive DBAs to drill down and resolve performance issues. And in the end, if your code and/or schema is bad enough, you relent, and convert it to a stored procedure call, which is exactly what your sharp DBA told you to do 20 billable hours ago.

A typical response to why developers use frameworks for databases is that they want their code to be “portable”. How many times have you seen a shop change database platforms? I could understand that argument if you used frameworks for all your code, reports, UI, everything. But if you use frameworks for the UI, and stored procedures for reporting, I guarantee that you’d have a heck of a time making that stored procedure code “generic”, such that it could be used against Oracle, Sybase, SQL Server, or DB2.

The more I think about it, I should totally love frameworks. I say that because if they were not in use, think of all the times I’d be stuck trying to improve performance, when now I can simply say: “Hey – that’s a framework query, and there’s absolutely nothing I can do about it – have a nice day….”

 

3 thoughts on “Frameworks O How I Hate Thee

  1. Pingback: A Rant About ORMs – Curated SQL

  2. Cristian Satnic

    Developers hardly think about application portability when using ORMs and frameworks to interact with the database. If anyone thinks about that it might be the application architect (if there even is one). Developers like ORMs because that enables them to deal with the database in terms of the application constructs they’re familiar with – typically objects and classes. Developers don’t think in terms of sets and data tables. They deal with objects, collections, LINQ (in C#) or other similar features for iterating over data. If an ORM can deal with that translation layer they’re more than happy to use it.

    Yes – there are many poorly designed applications where an ORM was involved but bad performance in those cases is not usually the fault of the ORM. By the time ORMs get involved the application architecture already has issues.

    A well designed application, where a DBA might be involved early to help with data models and so on, will deal with ORMs just fine and enable devs to do what they like to do best – write code in their preferred language (and that’s usually not SQL).

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *