FINALLY…
This is the last post I’ll write about foreign keys for a while. Maybe ever.
Let’s face it, most developers probably find them more annoying than useful, and if you didn’t implement them when you first started designing your database, you’re not likely to go back and start trying to add them in.
Depending on the size of your tables, and how much bad stuff has leaked in, it could be quite a project to fix the data, add the constraints, and then finally… Get a bunch of app errors, and realize why they weren’t there in the first place.
Now you’ve annoyed the developers. They’ll be coming with torches and pitchforks for the sa password.
But My Join Elimination!
Let’s start with known limitations!
- Multi-key foreign keys
- Untrusted foreign keys
- If you uh… need a column from the other table
And sometimes, just sometimes, the optimizer will monkey your wrench. Here’s a funny example!
That’s Not Funny.
Here’s our constraint:
|
1 2 3 |
ALTER TABLE dbo.Badges ADD CONSTRAINT fk_badges_users_id FOREIGN KEY (UserId) REFERENCES dbo.Users(Id); |
This query gets join elimination, because we followed all the rules.
|
1 2 3 4 5 6 7 8 |
SELECT b.UserId, COUNT(*) AS records FROM dbo.Users AS u JOIN dbo.Badges AS b ON u.Id = b.UserId WHERE b.Date >= '20120101' GROUP BY b.UserId HAVING COUNT(*) > 1000; |
Our constraint is trusted, it’s on one column, and we’re only referencing the Badges table in the important parts of the query: the select list and where clause.
We’re very excited. Very excited indeed about the prospects of join elimination.
We’re so excited that now we wanna write our query to only filter down to a range of users.
|
1 2 3 4 5 6 7 8 9 |
SELECT b.UserId, COUNT(*) AS records FROM dbo.Users AS u JOIN dbo.Badges AS b ON u.Id = b.UserId WHERE b.Date >= '20120101' AND b.UserId BETWEEN 1 AND 25000 GROUP BY b.UserId HAVING COUNT(*) > 1000; |
Now, if you look at that query, there are a whole lot of “b” aliases. I’m not asking for anything but a join to the Users table, just like before.
The last query got join elimination. This one doesn’t.
That’s an interesting choice, to say the least.
But I know what you’re thinking. Between is awful. Aaron said so.
How About Equality?
Let’s look for just one user. This’ll go better, right?
|
1 2 3 4 5 6 7 8 9 |
SELECT b.UserId, COUNT(*) AS records FROM dbo.Users AS u JOIN dbo.Badges AS b ON u.Id = b.UserId WHERE b.Date >= '20120101' AND b.UserId = 22656 GROUP BY b.UserId HAVING COUNT(*) > 1000; |
About That Join Elimination…
Sometimes, even if you do everything right, it doesn’t work out. I’m not doing anything particularly tricky, here, either.
If a Bouncer-American can find this in a few minutes of messing around, imagine what you can come up with?
Thanks for reading!
14 Comments. Leave new
Haven’t read the other FK articles, but I am wondering why you even need to join against the Users-table in these examples?
Gert – I need to join to Users to show a quirk with join elimination.
I what to jon plses
I had high hopes that this series of articles would finally convince me that Foreign Keys where a really good idea. But if anything I feel you have made the case that they are not worth the hassle they bring, especially if you are retrofitting them to an existing design. I get that there are downsides to not having them, but on balance I think we (my dev team) are better off without them. We have built many applications in our shop over the years some with FK some without. The applications with FK are a PIA for loading data. While the pain is obvious the benefits are not. I can’t recall an issue where we have said Gee I wish we had a FK on that table. Just my experience and 2 cents worth. Without trying to start any sort of religious war what have I missed?
Paul — no, nothing, unfortunately. I think they’re a bit like partitioning. If you’ve been using them from the get-go, they’re sweet as. If you’re trying to shoe horn some existing data in, it’s a herculean effort for not much return. I’d rather spend my time working on optimistic isolation levels or something, which would have more concrete benefits.
Thanks Erik. Really appreciate the tutorials and information all you guys/gals provide. I have learnt heaps.
The issue with not having a FK on a table is that it makes it ever so easy to load bad data as well.
DW – yes, because no one who loads data has ever disabled their foreign keys during data loading. Never. Hasn’t happened.
When I write queries with INNER JOINs, I ut as many conditions as possible into the ON clauses, and make the WHERE clause as lean as possible (if all the JOINs are INNER, I consider it a code smell for there to be a WHERE clause at all… yes I’m a full-stack dev, not a data dude). The earlier you can eliminate records, the more efficient the query.
I know the example here is contrived to show the specific point, but I’d be surprised if the JOIN wasn’t eliminated in the query plan if that filer was moved to the ON clause.
John — No. Check out this post by Conor Cunningham.
Conor has been an architect on the SQL Server team for ages.
Thanks!
Doh! Keep it simple v. premature optimization & simple wins! Shocking!
Reminds me of my high school accounting teacher, who often referred to “the exception that proves the rule”, since I now realize this has been an exception in my book, but shouldn’t be (and won’t be any more) because it does follow the rule, the KISS rule! And I’m the Silly this time! 🙂
Hi can you help me
No.
In that last “Equality” example, I wonder if SQL Server is still using the FK constraint as an optimization to eliminate a JOIN, but going the other way.
It knows that there are either 0 or 1 rows from dbo.Users matching a UserId. If it returns 1 row, then it needs to read from dbo.Badges where it thinks there will be over 7900 rows. But if it returns 0 rows, then it doesn’t need to read from dbo.Badges at all!