Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910»»»

A Check and Foreign Key Constraint Improves Query Performance Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 12:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:10 PM
Points: 52, Visits: 263
Nice post!

R Glen Cooper
Post #1447115
Posted Friday, April 26, 2013 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:57 PM
Points: 7, Visits: 114
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!!
Post #1447127
Posted Friday, April 26, 2013 1:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:53 AM
Points: 112, Visits: 542
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
Post #1447137
Posted Friday, April 26, 2013 2:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 6,168, Visits: 7,233
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
Post #1447166
Posted Friday, April 26, 2013 4:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:34 AM
Points: 3,261, Visits: 1,955
Very nice write up. Thanks for posting it!
Post #1447199
Posted Saturday, April 27, 2013 5:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 78, Visits: 104
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.
Post #1447257
Posted Saturday, April 27, 2013 12:59 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 8,571, Visits: 9,076
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
Post #1447289
Posted Monday, April 29, 2013 4:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 15,541, Visits: 27,919
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1447433
Posted Monday, April 29, 2013 4:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 8,571, Visits: 9,076
accidental post - deleted

Tom
Post #1447809
Posted Monday, April 29, 2013 4:50 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 8,571, Visits: 9,076
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
Post #1447817
« Prev Topic | Next Topic »

Add to briefcase «««678910»»»

Permissions Expand / Collapse