A Check and Foreign Key Constraint Improves Query Performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sarvesh Singh » A Check and Foreign Key Constraint Improves...

87 posts, Page 1 of 9

1 2 3 4 5 »»»

A Check and Foreign Key Constraint Improves Query Performance

Rate Topic

Display Mode

Topic Options

Author

Message

ss-457805

Posted Wednesday, October 20, 2010 9:41 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:14 AM
Points: 356, Visits: 1,658

Comments posted to this topic are about the item A Check and Foreign Key Constraint Improves Query Performance

blog: http://sarveshsingh.com

Twitter: @sarveshsing

Post #1008156

Satnam Singh

Posted Thursday, October 21, 2010 12:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 05, 2013 3:42 AM
Points: 291, Visits: 1,064

Nice one.

Post #1008190

Rod Weir

Posted Thursday, October 21, 2010 12:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 5:56 PM
Points: 6, Visits: 150

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

Post #1008191

m.ehrt

Posted Thursday, October 21, 2010 12:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 7:10 AM
Points: 2, Visits: 24

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

Post #1008192

fabio-1084329

Posted Thursday, October 21, 2010 1:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 27, 2012 4:16 AM
Points: 1, Visits: 42

-- 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

Post #1008208

Håvard

Posted Thursday, October 21, 2010 1:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 5:51 AM
Points: 145, Visits: 140

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.

Post #1008209

garima.arya

Posted Thursday, October 21, 2010 2:30 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 5:20 AM
Points: 263, Visits: 22

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

Post #1008238

ss-457805

Posted Thursday, October 21, 2010 2:31 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:14 AM
Points: 356, Visits: 1,658

You can use inner join or subquery. please look at the execution plan and see which is good for you.

blog: http://sarveshsingh.com

Twitter: @sarveshsing

Post #1008240