4 ms·

Some tips from a SQL Server DBA: * DON'T USE USER DEFINED FUNCTIONS IN ANY PREDICATE OF ANY TYPE EVER, THIS IS DEATH. * If you want things to go fast, conside

by hobs 10y ago

Some tips from a SQL Server DBA: * DON'T USE USER DEFINED FUNCTIONS IN ANY PREDICATE OF ANY TYPE EVER, THIS IS DEATH. * If you want things to go fast, consider that your query needs to be able to be converted into a "search argument" to use any of those precious indexes you made, so: LIKE 'hdfhafsdh%' -- This works! LIKE '%fafads%' -- Scanning all the things, so sad. In the same vein, any function you use on your values in the tables mean that there are often no indexes you can use to help your operation (with a few technical exceptions around specific optimizations SQL Server has around datetime <-> date calculations ) Download SQL Sentry Plan Explorer - it just became free and with it you can shred apart your execution plans with ease and keep histories of the work you have done on a query, or watch a plan change over time as your tune it with ease. https://sentryone.com/plan-explorer https://sentryone.com/plan-explorer

dang 10y ago
Please don't use all-caps for emphasis. This is in the HN guidelines: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
- mSparks 10y ago
  please take your life somewhere else.
_quick_q 10y ago
Typical DBA comment... Shout out rules without any explanation or rationale. No wonder many programmers avoid working with DBAs.
- closed 10y ago
  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!
- Pxtl 10y ago
  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.
  - timlin 10y ago
    The biggest problem with scalar UDFs is not the IO performance. It is that they force the query to serial (non-parallel). On queries that return a non-trivial quantity of data, that hurts. https://www.brentozar.com/blitz/blitz-result-user-defined-functions-slowing-down-sql-server/ https://www.brentozar.com/blitz/blitz-result-user-defined-fu... http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forci...
    - Pxtl 10y ago
      I'm curious, how do you spot this "forces parallel" on a query plan? Or is this one of those magic things you have to just know? Would it make more sense to have a precompiler go through and inline my UDFs? They seem pretty amenable to a macro-expansion.
  - swasheck 10y ago
    It's not a "black box" but what ends up happening is that the recommendation is handed down/out to others without full explanation. Additionally, data volumes truly matter, for the reason(s) outlined above vis a vis table scanning due to (non)determinism.
- dsp1234 10y ago
  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.
- dang 10y ago
  This comment breaks the HN guidelines by being uncivil. Please don't do that. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newswelcome.html https://news.ycombinator.com/newswelcome.html
  - mSparks 10y ago
    please take your life somewhere else.
- cookiecaper 10y ago
  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.
  - Pxtl 10y ago
    My problem, as a developer, is how much database behaviour seems actively user-hostile. The horrifying implications of tri-value logic and the lack of a modern type-system to allow us to have a sensible alternative to NULL is infuriating. The bipolar attitude about how normalizing data is so important but how so much falls apart when you actually have normalized data. How infuriatingly hard basic, sane code-reuse is. "Don't use scalar UDFs" basically screams out to me "this is a bad programming language". Is it any wonder that developers go running to NoSQL solutions? It's not "relational algebra" that they're running from - schemaless behavior is just gravy on top of escaping dealing with the muddy, deceptive black-box world of databases. The database takes care of everythign until it doesn't.
    - cookiecaper 10y ago
gigatexal 10y ago
in that vein inline table valued functions are preferred if you must use one: http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function http://stackoverflow.com/questions/2554333/multi-statement-t...

by hobs 10y ago

dang 10y ago

Please don't use all-caps for emphasis. This is in the HN guidelines: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html

mSparks 10y ago
please take your life somewhere else.

_quick_q 10y ago

Typical DBA comment... Shout out rules without any explanation or rationale. No wonder many programmers avoid working with DBAs.

closed 10y ago
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!
Pxtl 10y ago
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.
- timlin 10y ago
  The biggest problem with scalar UDFs is not the IO performance. It is that they force the query to serial (non-parallel). On queries that return a non-trivial quantity of data, that hurts. https://www.brentozar.com/blitz/blitz-result-user-defined-functions-slowing-down-sql-server/ https://www.brentozar.com/blitz/blitz-result-user-defined-fu... http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forci...
  - Pxtl 10y ago
    I'm curious, how do you spot this "forces parallel" on a query plan? Or is this one of those magic things you have to just know? Would it make more sense to have a precompiler go through and inline my UDFs? They seem pretty amenable to a macro-expansion.
- swasheck 10y ago
  It's not a "black box" but what ends up happening is that the recommendation is handed down/out to others without full explanation. Additionally, data volumes truly matter, for the reason(s) outlined above vis a vis table scanning due to (non)determinism.
dsp1234 10y ago
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.
dang 10y ago
This comment breaks the HN guidelines by being uncivil. Please don't do that. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newswelcome.html https://news.ycombinator.com/newswelcome.html
- mSparks 10y ago
  please take your life somewhere else.
cookiecaper 10y ago
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.
- Pxtl 10y ago
  My problem, as a developer, is how much database behaviour seems actively user-hostile. The horrifying implications of tri-value logic and the lack of a modern type-system to allow us to have a sensible alternative to NULL is infuriating. The bipolar attitude about how normalizing data is so important but how so much falls apart when you actually have normalized data. How infuriatingly hard basic, sane code-reuse is. "Don't use scalar UDFs" basically screams out to me "this is a bad programming language". Is it any wonder that developers go running to NoSQL solutions? It's not "relational algebra" that they're running from - schemaless behavior is just gravy on top of escaping dealing with the muddy, deceptive black-box world of databases. The database takes care of everythign until it doesn't.
  - cookiecaper 10y ago

gigatexal 10y ago

in that vein inline table valued functions are preferred if you must use one: http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function http://stackoverflow.com/questions/2554333/multi-statement-t...