[frgotmylogin]

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]

>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]

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]

That's way worse than I thought, thanks for sharing.