Financial Advice
When I talk about “data access patterns” in relation to databases, it’s often met with blank stares.
What is a data access pattern? What does it have to do with indexes?
Well, my dear friend, today you’re gonna learn it good and hard.
Medical Doctor
Data access patterns refer to the most common ways that queries filter, join, order, and display data.
The earliest point of a data access pattern is your where clause. Different queries may have different patterns of data to look for.
Giving some generic OLTP-ish examples, you might have queries that look for:
- Customer orders
- Within a date range
- Ordered by most recent
- Items in a customer order
- With the total price
- Plus shipping
- Plus tax
- Items in stock
- Total quantity
Depending on how normalized your data is, getting some of this stuff will likely require 2-3 tables getting joins together in some manner.
But all of these different scenarios define your data access patterns, and this is how you need to gear your indexes.
A lot of people get caught up on the minutiae of indexes without taking care of any of the basics, worrying about foreign keys, GUIDs, fragmentation, and other ridiculous memes.
Personal Trainer
Your data access patterns should define your indexes, because that — along with well-written queries to use those indexes most efficiently — is what’s going to make your application fast.
I’ve blogged about some of the fundamental concepts behind this in the past:
- Let’s Design An Index Together Part 1
- Let’s Design An Index Together Part 2
- Let’s Design An Index Together Part 3
- A General Indexing Strategy For Normal Queries
- Predicate Selectivity and Index Design
- Rethinking Key Column Order
If you still need help with your index design after reading those, drop me a line! That’s the kind of thing I love helping people out with.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.