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

A Check and Foreign Key Constraint Improves Query Performance Expand / Collapse
Author
Message
Posted Wednesday, October 20, 2010 9:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:51 AM
Points: 357, Visits: 1,692
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
Posted Thursday, October 21, 2010 12:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 6, 2014 1:47 AM
Points: 292, Visits: 1,084
Nice one.
Post #1008190
Posted Thursday, October 21, 2010 12:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 3:51 PM
Points: 6, Visits: 155
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
Posted Thursday, October 21, 2010 12:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:58 AM
Points: 2, Visits: 26
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
Posted Thursday, October 21, 2010 1:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:17 AM
Points: 1, Visits: 48
-- 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
Posted Thursday, October 21, 2010 1:29 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 8:16 AM
Points: 161, Visits: 142
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
Posted Thursday, October 21, 2010 2:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Thursday, October 21, 2010 2:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:51 AM
Points: 357, Visits: 1,692

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.


blog: http://sarveshsingh.com

Twitter: @sarveshsing
Post #1008240
Posted Thursday, October 21, 2010 3:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:38 AM
Points: 2,223, Visits: 3,647
Excellant post. Thanks for writing the article





Pradeep Singh
Post #1008254
Posted Thursday, October 21, 2010 3:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:51 AM
Points: 357, Visits: 1,692

Excellant post. Thanks for writing the article


Thank you Pradeep.


blog: http://sarveshsingh.com

Twitter: @sarveshsing
Post #1008262
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse