The Recording
The Panelists
- Kevin Feasel
- Mala Mahadevan
- Tracy Boggiano
- Tom Norman
Notes: Questions and Topics
Transaction Modes
The first topic of the evening was around transaction modes, following from a blog post and video I released yesterday.
Not surprisingly, most of us use explicit transactions in many cases, particularly higher-risk scenarios. Tracy has a template which she fills in, and Mala follows a similar path to me: begin a transaction if it’s potentially scary or if you’re doing this in application procedures. Tom’s answer: it depends. Most of the time, Tom uses autocommit because he’s in easy mode, but when he kicks it up to dangerous mode or if he needs to wrap multiple tables in a single transaction, he’ll mark them as explicit.
We also got into a rat’s nest on nested transactions and save points. Nested transactions are a lie. Savepoints are mostly a lie.
Maximum Number of Joins in a Query
Mala brought up a topic asking about the maximum number of joins in a query. Tom took a strong stand with “It depends.” Mostly it depends on how big the tables are.
@srutzky had the best answer: 20 to 30, but they must all be to views which have 20-30 joins. I’d add on that the views need to be nested views going at least three or four levels deep.
I don’t think there’s a real answer to the question. I’ve run into cases with 18-20 joins where the query plan just falls apart and taking one of those joins out (even to a minor table where it’s a simple nested loop lookup of a fairly small number of rows) makes the query perform a lot faster. But I’ve also worked with queries with more joins than that which worked quite nicely. At the end of the day, if you are able to generate a stable plan, that’s how many joins you can get away with.
As a bonus, I rant about the phrase “Normalize until it hurts, denormalize until it works.” This isn’t the 1980s; that phrase generally doesn’t apply to OLTP systems anymore and hasn’t for a good decade-plus. If you need to denormalize your tables to get queries to run efficiently, it probably wasn’t really normalized.
Mala’s Book Corner
Mala is back with two book recommendations for us:
- Performance Tuning with DMVs by Tim Ford and Louis Davidson. This is a bit out of date—like, say, 4 or 5 versions of SQL Server out of date—but as Mala points out, the book goes into a lot of great detail on dynamic management views which are still useful today.
- Atomic Habits by James Clear.
A Diversion on Technical Writing
We ended the broadcast with a discussion on the importance of technical writing and some of the difficulties around it. It started on the idea of writing a book, but we ended up focusing on the documentation itself. One thing I want to stress is just how difficult it is to get this documentation right, especially because we tend to take mental shortcuts and expect that others will know the context currently in our heads. I’m really bad at it and have to try hard to remember that the reader needs all of the relevant context. It’s particularly difficult because the reader will go from A to B to C to D, but I might have written it B, D, A, C, such that by the time I get to A, I forget that I needed to explain something to make B make sense.
Raymond also asked where you can store documents. It’s a tough problem and we punt around the problem a bit.