8 ms·

Ways to Tweak Slow SQL Queries

281 points by helenanders26 7y ago

rosser 7y ago
Heed the caution about CTEs being optimization fences. I had a case just this past week, where one of our engineers needed help with a query that used a LEFT JOIN against a CTE which generated text-search vectors for some subset of rows, in some large table. The query was taking 9-10 minutes, cache-warmed. Used that way — an outer join against a CTE — the planner was forced to generate a plan that produced a row for every candidate row in the table being used as input for the text-search, whether or not it would be needed, visiting nearly all of its pages. Just by moving the CTE inline (that is: "LEFT JOIN ( #{ subquery } ) AS blah"), the query completed in 17ms. This way, the planner could apply conditions from the rest of the query to the subplan from which text-search vector rows were being produced, such that it only pages containing rows it already knew it would care about would even be retrieved. The rest of this article is pretty on-point, too. Source: This stuff is my day job. EDIT: As a counter-point, because they are incredibly useful, I've also had countless cases where rewriting a query to use CTEs was the several orders of magnitude win. This also wasn't the only possible fix; the qualifying conditions in the CTE could have been improved, eliminating the extra work where it would have occurred. Like all things computers, the real answer is, "it depends..."
- war1025 7y ago
  I think I read somewhere that they are fixing some of optimization issues for CTEs in the next release of Postgres. Does anyone know whether that's true or not?
  - rosser 7y ago
    Are you thinking of the "[NOT] MATERIALIZED" clause? Yes, that's coming. https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=608b167f9f9c4553c35bb1ec0eab9ddae643989b https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
    - war1025 7y ago
      Thanks for the info! I am a big fan of the readability of CTEs. Though I guess in production code we use SqlAlchemy, so it would just be a matter of swapping `.cte()` for `.subquery()` probably...
  - ellisv 7y ago
    IIRC Postgres 12 is suppose to remove most of the optimization fences around CTEs.
- ggm 7y ago
  Like all things computers, the real answer is, "it depends..." This is what makes the job exist. If it was algorithmic we'd already have been replaced by EXPLAIN
  - Timucin 7y ago
    Each of the ~5000 stored procedures I had to strip out from a client’s large MySQL cluster agrees with this statement.
- emilsedgh 7y ago
  On Postgres 12, CTE's are not optimization fences anymore. [0] [0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
  - rosser 7y ago
    Not quite true. The default behavior changes, and new syntax is added allowing users to specify their desired behavior, but if the sub-query in the CTE does certain things, it will still be materialized instead, regardless. That is: they mostly aren't a fence any more, but can be when you want them to, and sometimes still must be.
- drunkpotato 7y ago
  Query planners are like compilers: sophisticated and sometimes surprising but not perfect. Writing sql is just like writing any other code: optimize for an easy to understand query first, performance if needed (and always comment your performance optimizations)! They are also like just-in-time hotspot optimizations: the query planner keeps statistics and the second run of a query can be 10x or more faster than the first run. (Side note: most analytics queries in my experience are run against cold data so it’s usually worth optimizing for first-query performance.) This stuff is also my day job, and fun!
julian37 7y ago
The best strategy isn't mentioned: learning how to read EXPLAIN output and acting on it. > Using wildcards at the start and end of a LIKE will slow queries down That's only true if you don't have the right indexes in place. A wildcard at the end can use a regular (btree) index. Even a LIKE query with wildcards in other places can be fast with a trigram index. https://niallburkley.com/blog/index-columns-for-like-in-postgres/ https://niallburkley.com/blog/index-columns-for-like-in-post...
- janpot 7y ago
  > A wildcard at the end can use a regular (btree) index. depending on the locale your db is in, it might be a little less "regular". recently fell in that trap. http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops.html http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops...
takeda 7y ago
About indexes, they improve query performance, but they also decrease write performance (and of course take space) that's why it's good idea to remove indices that aren't used or rarely used. Especially indices that are placed on columns that have random data and/or are frequently updated (prevents HOT updates) can hurt write performance.
- forcemajeure 7y ago
  AFAIK it's also proportional to the number of rows with data in the indexed columns. Inserting a row with a NULL in the indexed column does not cause an update to the index (at least experimentally it doesn't seem to contribute to index size on SQL Server). I imagine this is/was a simple optimization for DBMS to implement, but I would love to be corrected otherwise...
  - philliphaydon 7y ago
    If you change a value to null it’s still a row allocated in the index. The index size won’t shrink unless you rebuild it. Removing and deleting data will just end up with fragmentation.
- danmaz74 7y ago
  One very powerful "trick" is to use partial indexes where useful. For example, a pretty common pattern is to have tables where most rows aren't actively used most of the time, eg when you have a status column, and most rows are in the final status which you usually don't touch, so most where conditions include statuses which are not the final one. If you create a partial index WHERE status IN ('s1', 's2' ...), the index becomes a lot smaller and a lot faster.
  - james_s_tayler 7y ago
    I never thought about that, but that's a neat trick.
caf 7y ago
Try to avoid the use of ‘IN’ or ‘NOT IN’. By doing this you are performing a full table scan as the query engine looks through every row to check if the condition is met. It's not clear to me why IN () can't do an index scan or hash join?
- danmaz74 7y ago
  IN () definitely does use indexes in most cases on Postgres. The article is wrong there.
  - aiCeivi9 7y ago
    Yes in general but I was hit with case when Postgres started to switch to FTS depending on argument count inside `IN ()` while in reality using index was faster for each and every case. I had to split it in multiple queries with 100 args each, even if few month earlier it didn't have any issue with thousands.
    - danmaz74 7y ago
      That's why I wrote "in most cases" :) Anyway, in a situation like that where you have lots of values in the IN clause, I don't think that using = instead of IN would even be an option.
    - jtc331 7y ago
      I’m guessing the index in question was a partial index defined with a “WHERE column NOT NULL” restriction? This was a limit of the optimizer previously (IN clauses are broken down into AND/OR groups to prove inferences but only if <= 100 items). But Postgres 12 includes a patch I wrote so that the optimizer can prove the NOT NULL inference directly from an array operator of any size.
      - takeda 7y ago
        In my case the index is not a partial index, but it is also on PG 9.6.8, in another response[1] I provided more details. Unfortunately we are stuck with 9.6.x since before my time my company decided to use AWS Aurora, currently there's no easy path to do major version upgrade from 9.6.x and anyway 10.x is the most recent available version :( but any information why is this happening would be appreciated. [1] https://news.ycombinator.com/item?id=20863418 https://news.ycombinator.com/item?id=20863418
feike 7y ago
Good set of tips, however "Not NOT IN" is not true in general, as a single counterexample: EXPLAIN (costs off) SELECT relname, relpages FROM pg_class WHERE relname IN ('pg_class'); QUERY PLAN --------------------------------------------------------- Index Scan using pg_class_relname_nsp_index on pg_class Index Cond: (relname = 'pg_class'::name) (2 rows) In general, I'd like to echo other's comments, learning how to use EXPLAIN and how to interpret query plans is the single most important tool, as it allows you to verify your hypothesis instead of relying on rules-of-thumb.
- takeda 7y ago
  I actually had situation where the using IN resulted in a query that run for 35s and after replacing with `ANY(VALUES(...` it took ~600ms. In my case `column IN ('value1', 'value2')` was converted to an array like this: `((column)::text ANY ('{value1,value2}'::text[]))`, an index on the column was completely ignored and PG performed a sequential scan. After changing it to `column = ANY (VALUES ('value1'),('value2'))` it created a table in memory and used it when performing an index scan. I have feeling this is maybe some kind of a bug in query planner? This is on PG 9.6.8.
  - jtc331 7y ago
    I think I'd need more information to diagnose; were there any NULL values in the list? How many values?
    - takeda 7y ago
      The column can have nulls but after a quick check there's only a single record with a null value (probably added as a test, no idea). In the bad plan a sequential scan was performed and then it removed over 4 million rows (Rows Removed by Filter: 4562849) Edit: here is the query planner's output: https://explain.depesz.com/s/eVXI https://explain.depesz.com/s/eVXI After rewriting the query (change IN into ANY(VALUES( and adding an index to the "seven" table): https://explain.depesz.com/s/b4kU https://explain.depesz.com/s/b4kU
lordnacho 7y ago
Number one has got to be look at the execution plan. If it's complicated, try to simplify it, and that's where all these particular things should be checked.
- JacKTrocinskI 7y ago
  Agreed, related point, collecting statistics.
racecar789 7y ago
> If you are calling multiple views, or worse, views on views on views you are asking the query engine to run multiple queries to return your columns. I thought databases flatten all view layers into a single SQL statement before executing. Aka no performance impact except for a couple milliseconds to flatten out the views.
- radiowave 7y ago
  Yes, this was my thought as well. The views being flattened is certainly what I've found it's always looked like was happening, based on the EXPLAIN output.
craig_asp 7y ago
"Pull out the names of only the columns you need instead of using SELECT * to speed things up further." This is not performance-related in most cases. Unless the bottleneck is in the amount of data being transferred to a client over a network connection and there is a large amount of columns, or you have an index which matches the limited column list query exactly, there would be no performance difference in SELECT * vs SELECT <column list>. In columnar dbs it does matter because the less columns you select the less data gets accessed on disk. However, this does not hold true for row stores because data is stored in such a way that the whole row gets accessed no matter how many columns get specified in the query. There are many other good reasons why SELECT * is acceptable only in development queries, but performance is not one of them.
- deleted 7y ago
  [deleted]
- danielbarla 7y ago
  In terms of performance, the primary rationale for only retrieving columns you need would be to allow the database engine to select a proper index to use, specifically, one that covers all the columns you are making use of. This can actually have a massive impact on the execution plan, far more than simply fetching say 2x more data, as you point out. In fact, I'd go so far as to say that the practice of selecting all columns makes it practically impossible to make use of covering indexes for high impact queries.
  - craig_asp 7y ago
    I fully agree. With both comments, both yours and branko_d's. The way I read the article, it seems to suggest that by removing rows from the select list would somehow always result in better performance. In my opinion, in the majority of cases there will be no measurable impact on performance. In some specific cases, however, yes, it can make a big difference. In postgres it also seems like selecting all columns explicitly, but out of order can be detrimental to performance: https://www.postgresql.org/message-id/5562FE06.9030903@lab.ntt.co.jp https://www.postgresql.org/message-id/5562FE06.9030903@lab.n... So, it depends :)
JacKTrocinskI 7y ago
Lightly put, the article leaves much to be desired, no mentioned of: execution plan, collecting statistics, partitioning, cost of indexing (e.g. when should you disable an index and rebuild it?), materialized views, etc.
- jambalaya 7y ago
  Surprised they didn't mention citext columns
jasoneckert 7y ago
This article has sage advice, but SQL optimization goes far beyond. Optimization is a knee-jerk reaction for me whenever I do database development and involves every level (e.g. should I use IsNull or Coalesce? Try both, look at execution plans, etc.)
reacweb 7y ago
The first principle for SQL optimization should be to reduce the number of queries. If you have a query inside a loop, the performances may be catastrophic. Using conditionals in queries was sometimes the only solution to avoid loops. The performance gain was enormous.
darkwater 7y ago
It should be somewhere reflected that this article applies in part only to PostgreSQL (some of the tips are more generic thou)
- grzm 7y ago
  Immediately following the list TOC: > ”This is written with PostgreSQL in mind but if you’re using something else, these may still help, give them a try.
frgotmylogin 7y ago
Some of this is probably SQL server specific, but we were having some scaling issues on legacy code earlier this year and I got a lot of bang for my buck checking for these things before diving in to specific issues: - code with a high row count table variable when a temp table would give more accurate execution plans due to better cardinality estimates. - ultra-complex join criteria with lots of OR logic that performed better as a UNION - lazy function calling, where a developer used a function that did more than they needed and could be replaced with a simple join to a table - looped calls to insert procs that could be done in bulk There was another entire category of problem that I guess would be described as "I hate sets" and was mostly attributable to one former employee. These were recognizable immediately upon opening the code and were a nightmare.
- neighbour 7y ago
  >There was another entire category of problem that I guess would be described as "I hate sets" and was mostly attributable to one former employee. These were recognizable immediately upon opening the code and were a nightmare. Care to elaboarate? I need a pick-me-up today.
  - frgotmylogin 7y ago
    It was a lot of stuff like - Get a list of x in a table var - while loop through x to build another list of y - while loop through y and update z one row at a time All of that rather than updating with a join. Some of his other patterns were reusing variables for different things in a proc, using inefficient functions in a way that they executed once per row before the result set was really reduced much, nesting those functions, and using loops any chance he got. You'd open up these slow, 500-700 line monster procs and have to figure out what they were doing and refactor them, but it was nearly illegible and there were no tests for them. Really a great reminder of what happens when code reviews aren't done.
    - neighbour 7y ago
      That's way worse than I thought, thanks for sharing.
neves 7y ago
These articles are fine, but it is very rare to see people using a profiler to discover what needs to be optimized.

281 points by helenanders26 7y ago

Heed the caution about CTEs being optimization fences. I had a case just this past week, where one of our engineers needed help with a query that used a LEFT JOIN against a CTE which generated text-search vectors for some subset of rows, in some large table. The query was taking 9-10 minutes, cache-warmed. Used that way — an outer join against a CTE — the planner was forced to generate a plan that produced a row for every candidate row in the table being used as input for the text-search, whether or not it would be needed, visiting nearly all of its pages. Just by moving the CTE inline (that is: "LEFT JOIN ( #{ subquery } ) AS blah"), the query completed in 17ms. This way, the planner could apply conditions from the rest of the query to the subplan from which text-search vector rows were being produced, such that it only pages containing rows it already knew it would care about would even be retrieved. The rest of this article is pretty on-point, too. Source: This stuff is my day job. EDIT: As a counter-point, because they are incredibly useful, I've also had countless cases where rewriting a query to use CTEs was the several orders of magnitude win. This also wasn't the only possible fix; the qualifying conditions in the CTE could have been improved, eliminating the extra work where it would have occurred. Like all things computers, the real answer is, "it depends..."

war1025 7y ago
I think I read somewhere that they are fixing some of optimization issues for CTEs in the next release of Postgres. Does anyone know whether that's true or not?
- rosser 7y ago
  Are you thinking of the "[NOT] MATERIALIZED" clause? Yes, that's coming. https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=608b167f9f9c4553c35bb1ec0eab9ddae643989b https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
  - war1025 7y ago
    Thanks for the info! I am a big fan of the readability of CTEs. Though I guess in production code we use SqlAlchemy, so it would just be a matter of swapping `.cte()` for `.subquery()` probably...
- ellisv 7y ago
  IIRC Postgres 12 is suppose to remove most of the optimization fences around CTEs.
ggm 7y ago
Like all things computers, the real answer is, "it depends..." This is what makes the job exist. If it was algorithmic we'd already have been replaced by EXPLAIN
- Timucin 7y ago
  Each of the ~5000 stored procedures I had to strip out from a client’s large MySQL cluster agrees with this statement.
emilsedgh 7y ago
On Postgres 12, CTE's are not optimization fences anymore. [0] [0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
- rosser 7y ago
  Not quite true. The default behavior changes, and new syntax is added allowing users to specify their desired behavior, but if the sub-query in the CTE does certain things, it will still be materialized instead, regardless. That is: they mostly aren't a fence any more, but can be when you want them to, and sometimes still must be.
drunkpotato 7y ago
Query planners are like compilers: sophisticated and sometimes surprising but not perfect. Writing sql is just like writing any other code: optimize for an easy to understand query first, performance if needed (and always comment your performance optimizations)! They are also like just-in-time hotspot optimizations: the query planner keeps statistics and the second run of a query can be 10x or more faster than the first run. (Side note: most analytics queries in my experience are run against cold data so it’s usually worth optimizing for first-query performance.) This stuff is also my day job, and fun!

julian37 7y ago

The best strategy isn't mentioned: learning how to read EXPLAIN output and acting on it. > Using wildcards at the start and end of a LIKE will slow queries down That's only true if you don't have the right indexes in place. A wildcard at the end can use a regular (btree) index. Even a LIKE query with wildcards in other places can be fast with a trigram index. https://niallburkley.com/blog/index-columns-for-like-in-postgres/ https://niallburkley.com/blog/index-columns-for-like-in-post...

janpot 7y ago
> A wildcard at the end can use a regular (btree) index. depending on the locale your db is in, it might be a little less "regular". recently fell in that trap. http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops.html http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops...

takeda 7y ago

About indexes, they improve query performance, but they also decrease write performance (and of course take space) that's why it's good idea to remove indices that aren't used or rarely used. Especially indices that are placed on columns that have random data and/or are frequently updated (prevents HOT updates) can hurt write performance.

forcemajeure 7y ago
AFAIK it's also proportional to the number of rows with data in the indexed columns. Inserting a row with a NULL in the indexed column does not cause an update to the index (at least experimentally it doesn't seem to contribute to index size on SQL Server). I imagine this is/was a simple optimization for DBMS to implement, but I would love to be corrected otherwise...
- philliphaydon 7y ago
  If you change a value to null it’s still a row allocated in the index. The index size won’t shrink unless you rebuild it. Removing and deleting data will just end up with fragmentation.
danmaz74 7y ago
One very powerful "trick" is to use partial indexes where useful. For example, a pretty common pattern is to have tables where most rows aren't actively used most of the time, eg when you have a status column, and most rows are in the final status which you usually don't touch, so most where conditions include statuses which are not the final one. If you create a partial index WHERE status IN ('s1', 's2' ...), the index becomes a lot smaller and a lot faster.
- james_s_tayler 7y ago
  I never thought about that, but that's a neat trick.

caf 7y ago

Try to avoid the use of ‘IN’ or ‘NOT IN’. By doing this you are performing a full table scan as the query engine looks through every row to check if the condition is met. It's not clear to me why IN () can't do an index scan or hash join?

danmaz74 7y ago
IN () definitely does use indexes in most cases on Postgres. The article is wrong there.
- aiCeivi9 7y ago
  Yes in general but I was hit with case when Postgres started to switch to FTS depending on argument count inside `IN ()` while in reality using index was faster for each and every case. I had to split it in multiple queries with 100 args each, even if few month earlier it didn't have any issue with thousands.
  - danmaz74 7y ago
    That's why I wrote "in most cases" :) Anyway, in a situation like that where you have lots of values in the IN clause, I don't think that using = instead of IN would even be an option.
  - jtc331 7y ago
    I’m guessing the index in question was a partial index defined with a “WHERE column NOT NULL” restriction? This was a limit of the optimizer previously (IN clauses are broken down into AND/OR groups to prove inferences but only if <= 100 items). But Postgres 12 includes a patch I wrote so that the optimizer can prove the NOT NULL inference directly from an array operator of any size.
    - takeda 7y ago
      In my case the index is not a partial index, but it is also on PG 9.6.8, in another response[1] I provided more details. Unfortunately we are stuck with 9.6.x since before my time my company decided to use AWS Aurora, currently there's no easy path to do major version upgrade from 9.6.x and anyway 10.x is the most recent available version :( but any information why is this happening would be appreciated. [1] https://news.ycombinator.com/item?id=20863418 https://news.ycombinator.com/item?id=20863418

feike 7y ago

Good set of tips, however "Not NOT IN" is not true in general, as a single counterexample: EXPLAIN (costs off) SELECT relname, relpages FROM pg_class WHERE relname IN ('pg_class'); QUERY PLAN --------------------------------------------------------- Index Scan using pg_class_relname_nsp_index on pg_class Index Cond: (relname = 'pg_class'::name) (2 rows) In general, I'd like to echo other's comments, learning how to use EXPLAIN and how to interpret query plans is the single most important tool, as it allows you to verify your hypothesis instead of relying on rules-of-thumb.

takeda 7y ago
I actually had situation where the using IN resulted in a query that run for 35s and after replacing with `ANY(VALUES(...` it took ~600ms. In my case `column IN ('value1', 'value2')` was converted to an array like this: `((column)::text ANY ('{value1,value2}'::text[]))`, an index on the column was completely ignored and PG performed a sequential scan. After changing it to `column = ANY (VALUES ('value1'),('value2'))` it created a table in memory and used it when performing an index scan. I have feeling this is maybe some kind of a bug in query planner? This is on PG 9.6.8.
- jtc331 7y ago
  I think I'd need more information to diagnose; were there any NULL values in the list? How many values?
  - takeda 7y ago
    The column can have nulls but after a quick check there's only a single record with a null value (probably added as a test, no idea). In the bad plan a sequential scan was performed and then it removed over 4 million rows (Rows Removed by Filter: 4562849) Edit: here is the query planner's output: https://explain.depesz.com/s/eVXI https://explain.depesz.com/s/eVXI After rewriting the query (change IN into ANY(VALUES( and adding an index to the "seven" table): https://explain.depesz.com/s/b4kU https://explain.depesz.com/s/b4kU

lordnacho 7y ago

Number one has got to be look at the execution plan. If it's complicated, try to simplify it, and that's where all these particular things should be checked.

JacKTrocinskI 7y ago
Agreed, related point, collecting statistics.

racecar789 7y ago

> If you are calling multiple views, or worse, views on views on views you are asking the query engine to run multiple queries to return your columns. I thought databases flatten all view layers into a single SQL statement before executing. Aka no performance impact except for a couple milliseconds to flatten out the views.

radiowave 7y ago
Yes, this was my thought as well. The views being flattened is certainly what I've found it's always looked like was happening, based on the EXPLAIN output.

craig_asp 7y ago

"Pull out the names of only the columns you need instead of using SELECT * to speed things up further." This is not performance-related in most cases. Unless the bottleneck is in the amount of data being transferred to a client over a network connection and there is a large amount of columns, or you have an index which matches the limited column list query exactly, there would be no performance difference in SELECT * vs SELECT <column list>. In columnar dbs it does matter because the less columns you select the less data gets accessed on disk. However, this does not hold true for row stores because data is stored in such a way that the whole row gets accessed no matter how many columns get specified in the query. There are many other good reasons why SELECT * is acceptable only in development queries, but performance is not one of them.

deleted 7y ago
[deleted]
danielbarla 7y ago
In terms of performance, the primary rationale for only retrieving columns you need would be to allow the database engine to select a proper index to use, specifically, one that covers all the columns you are making use of. This can actually have a massive impact on the execution plan, far more than simply fetching say 2x more data, as you point out. In fact, I'd go so far as to say that the practice of selecting all columns makes it practically impossible to make use of covering indexes for high impact queries.
- craig_asp 7y ago
  I fully agree. With both comments, both yours and branko_d's. The way I read the article, it seems to suggest that by removing rows from the select list would somehow always result in better performance. In my opinion, in the majority of cases there will be no measurable impact on performance. In some specific cases, however, yes, it can make a big difference. In postgres it also seems like selecting all columns explicitly, but out of order can be detrimental to performance: https://www.postgresql.org/message-id/5562FE06.9030903@lab.ntt.co.jp https://www.postgresql.org/message-id/5562FE06.9030903@lab.n... So, it depends :)

JacKTrocinskI 7y ago

Lightly put, the article leaves much to be desired, no mentioned of: execution plan, collecting statistics, partitioning, cost of indexing (e.g. when should you disable an index and rebuild it?), materialized views, etc.

jambalaya 7y ago
Surprised they didn't mention citext columns

jasoneckert 7y ago

This article has sage advice, but SQL optimization goes far beyond. Optimization is a knee-jerk reaction for me whenever I do database development and involves every level (e.g. should I use IsNull or Coalesce? Try both, look at execution plans, etc.)

reacweb 7y ago

The first principle for SQL optimization should be to reduce the number of queries. If you have a query inside a loop, the performances may be catastrophic. Using conditionals in queries was sometimes the only solution to avoid loops. The performance gain was enormous.

darkwater 7y ago

It should be somewhere reflected that this article applies in part only to PostgreSQL (some of the tips are more generic thou)

grzm 7y ago
Immediately following the list TOC: > ”This is written with PostgreSQL in mind but if you’re using something else, these may still help, give them a try.

frgotmylogin 7y ago

Some of this is probably SQL server specific, but we were having some scaling issues on legacy code earlier this year and I got a lot of bang for my buck checking for these things before diving in to specific issues: - code with a high row count table variable when a temp table would give more accurate execution plans due to better cardinality estimates. - ultra-complex join criteria with lots of OR logic that performed better as a UNION - lazy function calling, where a developer used a function that did more than they needed and could be replaced with a simple join to a table - looped calls to insert procs that could be done in bulk There was another entire category of problem that I guess would be described as "I hate sets" and was mostly attributable to one former employee. These were recognizable immediately upon opening the code and were a nightmare.

neighbour 7y ago
>There was another entire category of problem that I guess would be described as "I hate sets" and was mostly attributable to one former employee. These were recognizable immediately upon opening the code and were a nightmare. Care to elaboarate? I need a pick-me-up today.
- frgotmylogin 7y ago
  It was a lot of stuff like - Get a list of x in a table var - while loop through x to build another list of y - while loop through y and update z one row at a time All of that rather than updating with a join. Some of his other patterns were reusing variables for different things in a proc, using inefficient functions in a way that they executed once per row before the result set was really reduced much, nesting those functions, and using loops any chance he got. You'd open up these slow, 500-700 line monster procs and have to figure out what they were doing and refactor them, but it was nearly illegible and there were no tests for them. Really a great reminder of what happens when code reviews aren't done.
  - neighbour 7y ago
    That's way worse than I thought, thanks for sharing.

neves 7y ago

These articles are fine, but it is very rare to see people using a profiler to discover what needs to be optimized.