A Check and Foreign Key Constraint Improves Query Performance
-
Comments posted to this topic are about the item A Check and Foreign Key Constraint Improves Query Performance
-
Nice one.
-
wow, great tip, great write-up.
I ran this statement against a live, production database
select name,is_not_trusted from sys.foreign_keys
and it returned 57 untrusted foreign keys! Looks like I'll be making a script to automaticallly scan for, and update these constraints.
This article has opened up to me a whole new level of understanding query performance and internal database politics.
Many thanks,
Rod
-
Cool stuff, thanks.
In one of our main databases, around 30% of all constraints were not trusted.
But how about execution plans for stored procedures, are they automatically invalidated? Or do I have to execute sp_recompile for all the stored procedures as well?
Cheers
Michael
-
-- this is optimized also
select so.* from SalesOrderdetail as so
where so.CustomerID IN (select s.CustomerID from sales as s)
-- SQL TEAM forgets to optimize this
select so.* from SalesOrderdetail as so
JOIN sales as s ON so.CustomerID =s.CustomerID
-
Hi,
This was a brilliant article, highlighting something I've never thouht of before. Thumbs up!
But I have a follow up question: How do you suggest that I write a query that pulls out a specific row? Let's say, all rows for a specific customerId or details for only one sales order.
It seems to me as if an inner join syntax is better in that case.
-
Hi,
If someone has added a foreign key constraint, why would he write a query like this.
So, I have a small query that does having foreign key constraint improves the performance incase joins are used?
Thanks,
Garima
-
Hi,
This was a brilliant article, highlighting something I've never thouht of before. Thumbs up!
But I have a follow up question: How do you suggest that I write a query that pulls out a specific row? Let's say, all rows for a specific customerId or details for only one sales order.
It seems to me as if an inner join syntax is better in that case.
You can use inner join or subquery. please look at the execution plan and see which is good for you.
-
Excellant post. Thanks for writing the article 🙂
Pradeep Singh -
Excellant post. Thanks for writing the article
Thank you Pradeep.
-
Thank you satnam
-
Unfortunatly it turns out that untrusted check constrains will not always become trusted after altering the contraint as is done in this article. If you find yourself in this situation, this can come due to the constraint being configured (at create time) as not for replication. This can be seen by querying sys.foreign_keys with the condition is_not_for_replication = 1.
In this situation, which can be by design, only re-creating the foreign key without not for replication will make the desired constraint trusted.
More not for replication can be read here:
http://msdn.microsoft.com/en-us/library/ms152529(SQL.90).aspx
-
Great article SS
Hope this helps,
Rich[p]
[/p] -
any idea how do we check the same in sql server 2000? sysforeignkeys doesn't have is_trusted column..
Pradeep Singh -
garima.arya (10/21/2010)
If someone has added a foreign key constraint, why would he write a query like this.Interesting point about how the query optimizer works but I'm more with Garima. If there's a foreign key constraint there what's the advantage of putting the exists check in the code? The only thing I can think of is that it will make it a little easier to follow if you're not familiar with the DB but I wouldn't think that would be worth giving the optimizer the possibility of generating an execution plan that will scan both tables.
Viewing 15 posts - 1 through 15 (of 92 total)
You must be logged in to reply to this topic. Login to reply