SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Check and Foreign Key Constraint Improves Query Performance


A Check and Foreign Key Constraint Improves Query Performance

Author
Message
Glen Cooper
Glen Cooper
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 287
Nice post!

R Glen Cooper
kb1
kb1
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 122
Thank you for a very timely (for me) and most excellent post. I had all the right indexes and FK constraints set-up on my web application database. When I checked, I found 7 (out of 23) FK contraints were not trusted.

Thank you also to Peter for pointing out why I wasn't able to get them trusted (Not for Replication).

Note, if you use the table designer FK relationships dialog box, the property is called "Enforce for Replication" and I had to mark it as "Yes" to get SS to check and start trusting the FK constraints. All the trusted constraints already had this marked as "yes" in the the Table Designer FK relationships dialog box.
Also, I didn't have to drop the FK constraint and re-create it, simply changing the "enforce for replication" to "yes" and telling SS to "check existing data on creation or re-enabling" to yes and save the table was enough for SS to start trusting these constraints.

After doing this for all previously untrusted 7 constraints, the page load times in my web application have gone from an agonising 10 seconds down to 3 seconds!
A tremendous improvement!!

However, there's still room for more improvement in my app, it would be great to see sub 1 second page load times on a fast client network connection. I'm sure the hardware's capable, I just need to find where the bottlenecks are and remove them! Articles like this definitely help!!
lnoland
lnoland
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 825
arnipetursson (4/26/2013)
I agree. It is a tautological example.
You would not need an "exists" check if you have a foreign key constraint.

Though I know that I have used this logic myself and simplified my queries because I knew that a foreign key guaranteed the behavior I desired, in retrospect, I question if that was my best strategy. By including the "exists" I am, first of all, making clear of what my target data should consist which may help someone reading my code to better understand my intentions. Most importantly, my query will return the correct data even if someone removes the foreign key constraint and adds invalid data into the table. While I shouldn't have to worry about that possibility, in the real world, databases are not always locked down and strictly controlled by a conscientious DBA and the possibility of a change like a constraint going missing is hardly unheard of. In such an environment there are still plenty of paths to catastrophe but a little redundancy can be fairly low-cost insurance against some of the more common screw-ups, particularly if the optimizer reduces any performance impact.

- Les
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20627 Visits: 7660
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 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.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6585 Visits: 2396
Very nice write up. Thanks for posting it!
briankwartler
briankwartler
Right there with Babe
Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)

Group: General Forum Members
Points: 762 Visits: 692
Nice article. Apparently in the editing of the article content, some minor inconsistencies were left in toward the end. In the paragraph that begins "Let's make it a bit more interesting," you refer to the members table which should be the SalesOrderdetail table, and your reference to TeamID column should actually be CustomerID column.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26001 Visits: 12495
I missed this somehow when it first came out - or at least I don't recall it. It's a good and interesting article, but I think it would have been better if it had used queries against a join view for the example - it would have reduced the number of silly comments I suspect.

Tom

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98485 Visits: 33014
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 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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26001 Visits: 12495
accidental post - deleted

Tom

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26001 Visits: 12495
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 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.

Craig, I think you are seeing things froma very narrow perspective: a perspective where the people who write queries (a) write them against base tables, (b) know all the base table schemata. This is very different from the situation where application developers write queries against views and are not permitted to know the base schemata. In the latter situation, the optimiser is presented with a query whose from clause includes view expansions, which may be joining tables that it can eliminate because the particular query doesn't use anything from those tables.

Yes, it's very nice from the point of view of a DBA to be able to design all the queries himself - no app devs write queries, there is no facility which provides for ad hoc queries, and only queries written by the DBA team are ever run. But in the real world it isn't always like that. I'm a great believer in proper modularity and levels of abstractrion, and I don't believe an app developer should know the base schemata (mostly because if he does he'll write stuff that depends on them, so that further evolution of the base schemata becomes impossible - and this includes the case where what was supposed to be an ad hoc query is now the most important thing used by the marketing department or by the publicity department or by some one else with a lot of muscle); and I prefer to bury the schemata behind a stored procedure interface, rather than a view interface, in the hope of preclusing ad-hoc queries produced by all sorts of random people; but I can't guarantee to achieve that, and may have to provide some views that expose the data but not any teh structure of any part of any schema for which I have no real guarantee that the structure will not need to change in a few years time to enable us to accept new requirements, cope with new regulations, and enforce new business rules. Given that the requirement changes, the base schemata will need to change, so even if I start out exposing a view per base table (usually a fairly stupid idea, but not always) those views might rapidly cease to be in one to one correspondance with base tables.

Given these real world circumstances, where queries are written by people who don't know the underlying schemmata. proper declarative referential integrity enforcement gives the optimiser a great assist in getting the best possible performance out of the system for us.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search