A Check and Foreign Key Constraint Improves Query Performance

  • ss-457805

    SSCertifiable

    Points: 5872

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

  • Satnam Singh

    Hall of Fame

    Points: 3542

    Nice one.

  • Rod Weir

    Old Hand

    Points: 323

    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

  • m.ehrt

    SSC Journeyman

    Points: 86

    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

  • fabio-1084329

    SSC Rookie

    Points: 47

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

  • Håvard

    Mr or Mrs. 500

    Points: 569

    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.

  • garima.arya

    Say Hey Kid

    Points: 681

    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

  • ss-457805

    SSCertifiable

    Points: 5872

    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.

  • ps.

    One Orange Chip

    Points: 29250

    Excellant post. Thanks for writing the article 🙂



    Pradeep Singh

  • ss-457805

    SSCertifiable

    Points: 5872

    Excellant post. Thanks for writing the article

    Thank you Pradeep.

  • ss-457805

    SSCertifiable

    Points: 5872

    Thank you satnam

  • peter-757102

    SSCertifiable

    Points: 6877

    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

  • RichardDouglas

    SSCarpal Tunnel

    Points: 4029

    Great article SS

    Hope this helps,
    Rich

    [p]
    [/p]

  • ps.

    One Orange Chip

    Points: 29250

    any idea how do we check the same in sql server 2000? sysforeignkeys doesn't have is_trusted column..



    Pradeep Singh

  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    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 93 total)

You must be logged in to reply to this topic. Login to reply