Words: 712 Time to read: ~ 3.5 minutes
10 Years
T-SQL Tuesday is upon us once more. T-SQL Tuesday number 120 means something else as well. 120 monthly posts equals 10 years that Adam Machanic’s ( twitter | blog ) blog post party has been going on.
Wayne Sheffield ( twitter | blog ) is hosting this month’s event. Wayne asks us something that I’m sure we’ve all thought at some stage.
What were you thinking?
https://blog.waynesheffield.com/wayne/archive/2019/11/t-sql-tuesday-120-what-were-you-thinking/
In the beginning…
The first exploration of a system leaves a lasting impact. When you first get a chance to delve into the database, you capture a shot of what the coding standards are like. You gleam the past experiences of the developers.
I’m looking for instances of NOLOCK if I’m being honest.
…there are impressions.
This impression was a What were you thinking? experience.
- DEADLOCK_PRIORITY LOW on most procedures.
- A lot of hierarchial data types.
- VARCHAR(MAX) on most columns
- Variables at the start of procedures used in equality WHERE clauses. e.g.
DECLARE @Success int; Set @Success = 4; ... WHERE StatusId = @Success. - Functions that return a single, deterministic value.
- Multi-statement Table-Valued Functions with WHILE statements.
- A plethora of indexes on the tables, all single-column indexes.
I’ve said enough.
If you had seen my face at that moment, you would have laughed. Imagine me staring, horrified, eyes darting around the screen mouthing What the…
A little thinking saves a lot of shouting
Granted it took getting a coffee and staring in disbelief at the code before I recovered. It took getting another coffee after the first before I could rationalise what I was seeing.
I took what I knew, which was these developers were smart. I tried to match that with what I was seeing. And there was an answer.
Theoretical, not Physical
The codebase read like developers who were not used to interacting with a database. Developers who thought of the database as a “place to shove data” and that’s all.
It was clear they had tried to follow the DRY (Don’t Repeat Yourself) approach (#4, #5).
They had read the documentation on hierarchical data types and Microsoft’s saying:…
The built-in hierarchyid data type makes it easier to store and query hierarchical data
https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15
…instead of a parent/child relationship tables. (#2)
They had tried to translate the .Net data type [string] into the database. Deciding that varchar(max) was its closest match. (#3)
They had tried to query the data in a row-by-row approach, instead of a set-based method (#6).
And, they had tried to deal with the consequences of these and other decisions. (#1, #7)
Understanding, not blame
It’s hard to stay annoyed at people when you can understand their motives. Their mindset is the most effective deterrent to anger I can think of. There’s no blame but understanding. You want to help them improve. And that’s where this on-going process is now.
To move away from multi-statement Table-Valued functions with WHILE statements. Here’s inline Table-Valued functions with a recursive CTE (Common Table Expression) instead.
To use variables when you have to but be aware of the change in statistics that it brings.
The difference it can make to a query and a database when the data types are apt. How memory grants, logical page reads, and more are affecting by blobs.
How DEADLOCK_PRIORITY LOW is not an option if every procedure has it! How indexes can be of more than a single column. That there is such a thing as an INCLUDES!
Seeing now that the driving force they have is to create features. But the pain force they feel is database performance. I can grok their choices and actions at the time.
Still, it didn’t stop me going What were you thinking? at the outset.
I’m no better
I’m trying to learn different languages and frameworks at the moment. If someone more knowledgeable was to come along and see my interactions with Linux. If they were to critique my Python files. Or attempt to suppress a groan at my PromQL. I’d appreciate an air of understanding, not blame at that time.
So well done to the people who dived in and attempted the work even if they didn’t know how at the time. To paraphrase; those whos face is marred by dust and sweat and blood deserve the credit.
But don’t think I didn’t see those TRANSACTION LEVEL READ UNCOMMITTED that you’re using as NOLOCKS!