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….”