• Evil Kraig F (4/26/2013)


    Grant Fritchey (4/26/2013)


    TheGreenShepherd (4/26/2013)


    Don't mean to pile on here, but yeah. I fail to understand why someone would query this data in this fashion. Yeah, it improves performance for that query but it's unlikely that you'd ever actually write that query.

    Here's a blog post I wrote up[/url] that gives a different example, probably more realistic, to show how foreign keys help. They really do.

    Grant, unfortunately, I'm of the same mind as the above. I checked out your blog post and really, there's not a lot of difference. If you don't have RI in place, you have to go to those tables to make sure you have data limiters, I agree. Once you have RI in place everything you did was off the 'p' table, including the where clause. He did a where exists, you did a join, but the limitation logic is the same.

    FK's don't help where it matters, which is getting data from the connected tables. Well, they don't help anymore than a tuned index does. All trusted FKs allow for is a shortcircuit on join logic used as restrictors that you don't even need to include in the query if you have FKs in the first place.

    Well, that's what they're there for, so I'm okay with that. But to me, this isn't really 'optimization'. This is using a tool, similar to indexes, that will slow some things down and speed up others, and can be overused. If you're always associating two tables on the same field to restrict rows, using an FK to enforce it and being able to avoid the churn is probably a good idea for that restriction pattern. If you're connecting up for data, you might as well just use an index and truck on without the constant overhead of the FK.

    True. If you don't need those joins, you're better off writing the query without them. But, if we are getting down to the point where people have properly built queries, then tuning and optimization changes. You don't need things like the simplification optimizations. But, WAY too many people are not writing their queries correctly and those optimizations help them. I don't recommend this instead of tuning queries.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning