April 26, 2013 at 4:40 am
This article is very helpfull. One question: is it also advisable to use these constraints in sqlserver2005?
thanx,
Riet Albers
April 26, 2013 at 5:23 am
ralbers (4/26/2013)
This article is very helpfull. One question: is it also advisable to use these constraints in sqlserver2005?thanx,
Riet Albers
Yes. Same rules.
"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
April 26, 2013 at 6:15 am
A very interesting article, but I have a question. Surely if you have a trusted foreign key constraint in place between two tables, you should not write such an "if exists" check, as by doing so you are effectively not trusting that SQL Server has managed the foreign key relationship properly?
April 26, 2013 at 7:07 am
Very nice post, and something else I will use when Executives try to tell me that I should be creating new databases to store data that is related to data in our main database, because relational theory... is just a theory... 🙂
April 26, 2013 at 7:11 am
Very interesting. I've been playing around a bit, here's my test script so far: -
USE tempdb;
SET NOCOUNT ON;
-- CONDITIONALLY DROP TABLES
IF object_id('test_Details') IS NOT NULL
BEGIN
DROP TABLE test_Details;
END;
IF object_id('test') IS NOT NULL
BEGIN
DROP TABLE test;
END;
-- CREATE TEST TABLE
CREATE TABLE test (test_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(36));
-- CREATE TEST_DETAILS TABLE
CREATE TABLE test_Details (test_Details_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, test_ID INT NOT NULL, Info VARCHAR(36));
CREATE NONCLUSTERED INDEX nc_test_Details ON test_Details (test_ID);
-- FILL TEST TABLE WITH 50 RANDOM ROWS
INSERT INTO test (Name)
SELECT TOP 50
REPLACE(CAST(NEWID() AS VARCHAR(36)) COLLATE Latin1_General_BIN2,'-','')
FROM master.sys.columns sc1
CROSS JOIN master.sys.columns sc2
CROSS JOIN master.sys.columns sc3;
-- CREATE FOREIGN KEY RELATIONSHIP
ALTER TABLE test_Details WITH NOCHECK ADD CONSTRAINT FK_test_Details_test_ID_test FOREIGN KEY (test_ID) REFERENCES test(test_ID);
-- FILL TEST_DETAILS TABLE WITH 1,000,000 RANDOM ROWS
INSERT INTO test_Details(test_ID, Info)
SELECT TOP 1000000
(ABS(CHECKSUM(NEWID())) % 50) + 1,
REPLACE(CAST(NEWID() AS VARCHAR(36)) COLLATE Latin1_General_BIN2,'-','')
FROM master.sys.columns sc1
CROSS JOIN master.sys.columns sc2
CROSS JOIN master.sys.columns sc3;
-- DECLARE HOLDER VARIABLE AND TIMING VARIABLES
DECLARE @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;
-- CHECK TRUST STATE
SELECT QUOTENAME(OBJECT_NAME(Parent_Object_ID)) AS tblName,
QUOTENAME(name) AS constraintName, is_not_trusted
FROM sys.foreign_keys;
-- CLEAR DOWN CACHE TO ENSURE CACHING DOESN'T AFFECT PERFORMANCE TESTS
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER = td.test_Details_ID
FROM test_Details td
WHERE EXISTS (SELECT 1
FROM test t
WHERE t.test_ID = td.test_ID);
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('NOT TRUSTED Duration: %s',0,1,@Duration) WITH NOWAIT;
-- MAKE CONSTRAINT TRUSTED
ALTER TABLE test_Details WITH CHECK CHECK CONSTRAINT FK_test_Details_test_ID_test;
-- CHECK TRUST STATE
SELECT QUOTENAME(OBJECT_NAME(Parent_Object_ID)) AS tblName,
QUOTENAME(name) AS constraintName, is_not_trusted
FROM sys.foreign_keys;
-- CLEAR DOWN CACHE TO ENSURE CACHING DOESN'T AFFECT PERFORMANCE TESTS
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER = td.test_Details_ID
FROM test_Details td
WHERE EXISTS (SELECT 1
FROM test t
WHERE t.test_ID = td.test_ID);
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('TRUSTED Duration: %s',0,1,@Duration) WITH NOWAIT;
Which reports: -
NOT TRUSTED Duration: 00:00:00:770
TRUSTED Duration: 00:00:00:153
April 26, 2013 at 7:54 am
I realize that it's a 3 year old article but I missed it previously. Thought I'd take a minute to say "very nicely done" and excellent tip. Thanks for taking the time tow write it.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 26, 2013 at 9:52 am
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.
April 26, 2013 at 9:59 am
I agree. It is a tautological example.
You would not need an "exists" check if you have a foreign key constraint.
However the part on "not_trusted" is very useful.
And of course foreign key constraints (that are "trusted") in addition to indexes
help the optimizer make better decisions.
April 26, 2013 at 10:08 am
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.
"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
April 26, 2013 at 10:28 am
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.
There are a few tables in some of the more active databases in my current shop that do not have foreign keys. When I asked about them, or just about any other design curiosity, the answer is always 'it was done years ago by consultants who are long gone'. SInce there is no ROI in fixing things like this, they just stay there. Now, I can see that at some point in the future, a problem may pop up, and someone will say that we need a foreign key. So it's nice to know that if the performance question comes up, this will save a whole lot of head scratching.
April 26, 2013 at 12:01 pm
Nice post!
April 26, 2013 at 12:51 pm
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!!
April 26, 2013 at 1:13 pm
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
April 26, 2013 at 2:50 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 26, 2013 at 4:36 pm
Very nice write up. Thanks for posting it!
Viewing 15 posts - 61 through 75 (of 92 total)
You must be logged in to reply to this topic. Login to reply