This comment breaks the HN guidelines by being uncivil. Please don't do that.

https://news.ycombinator.com/newsguidelines.html

https://news.ycombinator.com/newswelcome.html

Not only that, but I have been working with some queries that contradrict this. We had some UDFs that are hyper-simple fetchers of config values from a KVP config table.

I tried optimizing them out. Nothing. Looked at the query plans - 0%. SQL server had reasonably noticed what they were and done a bang-up job handling them.

That's the infuriating thing about SQL server "advice" - it's such a black box that so many of the rules are cargo-cult stuff.

I see it as a sort of trade-off. Give a lengthy explanation and people often ignore the advice. Make the advice short and frightening and people listen, but might not be so sure why!

Without judgement, here is an explanation of those items mentioned above:

1.) Don't use user defined functions in any predicate.

In order to evaluate a predicate (WHERE clause, HAVING clause, JOIN condition aka boolean comparison), the query engine needs to know what both sides of the comparison evaluate to. So for each row, it has to pull out a value out of the database, then also execute the UDF (User Defined Function) in order to answer the question "does this predicate match?". UDFs are generally going to be slower than their native equivalents, and the best suggestion is to always try to do predicate matching on pre-computed values. So if you find that you are doing a lot of UDF based predicate matching, then it may be useful to see if you can pre-calculate the value of the UDF for that row, then do the predicate matching based on the pre-computed value. In MSSQL, these columns are called "Computed Columns", and computed columns in that engine can be persistent or not. You would generally want to use the persistent type for best performance.

2.) Consider that your query needs to be able to be converted into a "search argument" to use any indexes.

At a logical level, an index is a way of sorting rows. Imagine if you take all of the books in your local library, if you have one, and sorting them in alphabetical order. If someone asks you to find all of the titles that begin with "Database", then that's pretty easy. You walk down the aisles until you find the first book that starts with the letters D, A, T, A, B, A, S, and E. Then you continue reading off the titles until you find a book that doesn't use those letters anymore. Since the books are already alphabetized, you know that you've found them all. This is the equivalent of like 'Database%'.

Now imagine that someone instead asks you to find all of the books that have the word Database anywhere in their title. That's harder. Even though the books are alphabetized, the word Database could show up anywhere in the title, not just at the beginning. So you have to read the title of every single book in order to know if it contains the word Database. This takes a lot more time. This is the equivalent of like '%Database%'.

3.) Any function you use on your values in the tables mean that there are often no indexes you can use

The scenario presented here is something like Where left(title, 4) = 'asdf' or where firstName + ' ' + lastName = '_quick_q'. Internally, the query planner is going to index use what it determines is the best based upon the left side of the predicate, and even though it might look like "left(title, 4)" would be able to use an index, and maybe in some engines it does, there is generally no index that is going to match that definition. Many times people fall into this trap with simple stuff like where UPPER(LastName) = 'Q'. It seems like the query engine could make this work, but it won't because the left hand of the predicate doesn't match the index definition.

Programmers avoid working with DBAs because programmers think of SQL and databases as an annoyance that gets in the way of just shoving data into a hole somewhere. This is why MongoDB et al are so popular; they've provided cover for programmers to drop any pretense that organized data is useful. The end result is a continuous stream of data catastrophe after data catastrophe, and thousands of wasted man-hours trying to figure out how to do something with Mongo/Hadoop/whatever that is simple with a SQL server (and often reaching incorrect conclusions anyway).

I'm not saying there's never a good reason to use a doc DB like Mongo or a "Big Data" implementation like Hadoop, but programmers don't choose to use these things for good reasons 95% of the time. They choose to use them a) because they're new, and they want the resume points; and b) because they allow them to shed the pesky DBA and the pesky database engine, always in their hair about the "Right Way" to do things so that the data can be recalled and analyzed quickly, easily, and repeatably.

There is absolutely a flip side to this too, where DBAs behave in a massively unjustified manner toward non-DBAs.

IMO the heart of the issue is the bifurcation in roles. Both DBAs and traditional coders are programmers/developers. Both should be trained in the others' concerns and expected to help out in both parts of the system, and the role distinction of 100% DBA or 100% non-DBA should not exist. This keeps balance in the incentives on both sides.