Stop writing dynamic SQL!

One of my many hats is database administrator, and in my time I have designed and maintained a fair sized whack of databases. Informix always had a pleasing deployment experience, being a SQL wrapper on top of C-ISAM. You could leave it on site and come back a year later, update statistics and hey presto, it's still ticking along, doing what it's supposed to do.

Barring one incident where support responded to a ticket where I saw a select statement trigger a table scan when I was filtering by primary key on 40,000,000 records and stated that the profiler knows better... Well, muchachos, given I can hand craft the C code to grab it in about a millionth of the elapsed time, I am going to respectfully disagree.

Then there was the oddity where another SQL statement took about 4 minutes to execute unless there was a SLEEP statement immediately before. Back away, don't make eye contact. Here be dragons, and a ton of comments saying, "DO NOT ALTER THIS CODE"

But I digress. I witnessed the other a day a select statement that was horrifying in almost every way. An IN filter where the list of values was about 500 items long, then mixed with an OR and another IN. We're not talking about discrete values either, the same seven or eight values were just repeated over and over. I doubted it would perform well and guess what, profiling that puppy showed 30 seconds for a result set.

Inefficient as it is, that's not my real problem. It's dynamic SQL, which means you can't leverage the database cache and you have to generate an explain plan every time you run the query. That's a second layer of inefficiency.

But that's still not my real problem. This should be a stored procedure. It's 2018 guys, SQL injection is not exactly news. Do you want your CTO to be interviewed by Forbes in response to yet another data breach?

Seriously, stop writing dynamic SQL. It's not cool.