SQL Server Performance

SQL Server Foreign Key constraint benefits

Discussion in 'Performance-Related Article Discussions' started by Hmnt, Oct 27, 2009.

  1. Hmnt New Member

    We're designing a database in which I need to consider some FK(foreign key) constraints. But it is not limited to formal structuring and normalization. We go for it only if it provides any performance or scalability benefits.I've been going thru some interesting articles and googling for practical benifits, here're some links:http://www.mssqltips.com/tip.asp?tip=1296I wanted to know more about the benefits of FK (apart from the formal structuring and the famous cascaded deleteupdate)
    • FK are not 'indexed' by default so what are the considerations while indexing an FK?
    • How to handle nullable fields which are mapped as foreign key - is this allowed?
    • Apart from indexing, does this help in optimizing query-execution plans in SQL-Server?
      I know there's more but I'd prefer experts speaking on this. Please guide me.
  2. FrankKalis Moderator

    Foreign keys are among the most crucial concepts of the relational model itself. Basically what they say is that if B is referencing A, then A must exist. So, the database engine is enforcing your data integrity and you can rely on that. There is no other half way as close low level as an FK to enforce this. Sure, you can do this via the application, triggers, etc... But would you rely on that? So, in a way FK act as a last line of defence for your data integrity.
    The question whether you should also consider an index on a FK constraint depends on if you are going to delete from the parent table or not. If you are deleting data from the parent table, you certainly would want to have an index in the child table on the FK column(s). However, if you are NEVER deleting from the parent you may not want an index, because of space & performance considerations. This has to be evaluated on a case by case basis.
    NULLs are allowed in the FK column(s) in the child table. Obviously you can't have NULLs in the FK column(s) in the parent table.
    No, an FK constraint is a logical concept and as such does not have an effect on the physical execution plan. A maybe existing index however may have an impact on the execution plan.
  3. ksurvance New Member

    I agree on the importance of FK constraints to enforce referential integrity however I have a different take on the need for an index on a foreign key.
    In my experience, a foreign key almost always needs an index unless the table itself is quite small. In a relational database, joins are normally made between a primary key and a foreign key. It is difficult to imagine a scenario where a parent and child relationship would exist but the tables would not be joined frequently on the PK to FK. (although with increasing use of identity-based surrogate primary keys, the join is often made from the naturally unique primary key column to the foreign key. But that changes nothing in regard to the need for an index on the FK).
    Joins need to be indexed on both sides unless the child table is very small. the PK is of course indexed, but without an index on the FK you would probably get multiple full scans of the inner table or else a hash join instead of a nested loop. The query cost would usually be much higher without an index on the FK.
    Often it is assumed that if the parent table is small you don't need an index on the child table holding the foreign key, but this is incorrect. There may be only a dozen rows in the status table but there could be a million rows with a status value in the orderlineitem table and that is where you have to have an index.
    Unless the activity in your datebase is very unusual, the cost of an index when a delete from the parent table occurs is trivial compared to the cost of not having an index for the many joins of the two tables.
    It is quite common to omit the index on the foreign key. In my consulting engagement I always run a script to identify FKs without an index. Supplying the missing FK indexes is sometimes all I have to do to make dramatic improvements in performance.
    Regards,
    Kurt.
  4. FrankKalis Moderator

    Interesting points!
    I have the case here where the parent tables have at most only a couple of thousand rows while the child table has + 160 million rows after 1 year of production. Actually the number of "processed" rows that were in the table but got deleted is roughly 900 million rows.
    We also started off by having indices on all the FK columns, but in our test environment we couldn't observe any difference in the execution plans with and without these indices. However what we saw (and also expected) was a massive decrease in index maintenance cost when dropping the indices. Write operation became a lot lighter. Let alone the fact that we freed up a couple of GB of disk space. These were reasons enough for us to drop the indices.
    But I agree with you that when in doubt (or when you don't know exactly what you are doing) that having indices on FK columns is better than not having them. [:)]
  5. FrankKalis Moderator

    [quote user="ksurvance"]
    It is quite common to omit the index on the foreign key. In my consulting engagement I always run a script to identify FKs without an index. Supplying the missing FK indexes is sometimes all I have to do to make dramatic improvements in performance.[/quote]
    Is this so?
    Hm, dealing with client developers every day, I'm inclined to say that this is more due to the lack of knowledge (or ignorance) than a consciously made decision...
  6. ksurvance New Member

    this is very much so. There are exceptions but in general a FK should be indexed. A table that is so small that it will always be scanned is an exception. A foreign key that is not used in important joins may be an exception depending on a lot of things.
    Indexing foreign keys is accepted practice to many performance specialists. so much so that I am surprised I am hearing an argument against it on SQL-Server_Performance.com. Here is what Brad McGehee says about it in an article about auditing SQL Server performance.
    "...In essence, the column (or columns) used in tables being JOINed should be indexed for best performance. This is straight-forward advice and fairly obvious..."
    Kurt
  7. FrankKalis Moderator

    Sorry, didn't want to offend you. My "Is this so?" comment was actually aimed at this sentence "It is quite common to omit the index on the foreign key".
    Arguably SQL Server could automatically create an index on FK column(s).
    Don't misunderstand me. I agree on any argument you gave for indexing FK columns. All I'm saying is that there is always an "It depends" factor and on a case by case basis there may be good reasons to deviate from the generally accepted best practice. In that regard my first reply may have been to oversimplified and caused confusion.
  8. Hmnt New Member

    Guys .. this has been a great discussion .. thanks a lot.
    But honestly, I feel like I've stepped in an expert-to-expert discussion chamber :)
    I'm a normal "would be DBA" level developer. I've been handling some databases with a few million records. A lot goes around importing data between database and its clone and then using that clone in the web-app environment.
    Well, I've known that keeping PK indexes automatically and so it helps speedup the data access. Now, from this discussion I derive that if I'm using JOINs in my SQL-Queries then I shud use FK and index it to make the JOIN operations efficient.
    For example, I have a table OrgMaster (contains all the Org records) then I have a BookingMaster table (contains all the Booking records). Now, the OrgMaster.Id is being 'referenced' as BookingMaster.OrgId. So, I have an FK for the OrgId-to-Id relationship and I shud 'index' it for better performance of any JOIN operation between both of these tables .. did I get it correctly?
    All the above - at the cost of extra overhead of space and time (while inserting record in the table with FK).
    I'd request that you provide me a list of points to be considered, like -
    • # Is FK-index going to eat up too much space ime as table grows few million records?
    • # In that case, is it worth to go for an FK-index "each time"?
    • # In what case shud I NOT apply FK or Index it or do neither of it (of course I can handle a LOT from the app)
    • # Any other tricky to speedup JOIN or other such time-consuming lookups?
    Thank you.
  9. FrankKalis Moderator

    [quote user="Hmnt"]
    I'd request that you provide me a list of points to be considered, like -
    • # Is FK-index going to eat up too much space ime as table grows few million records?
    [/quote]
    That should not be your major concern in the first place. IMHO, 1st concern is data integrity, 2nd is performance, and then the rest...
    A quick check here tells me that an index on an integer column in a 12.1 million row table consumes just about a few MB's. Time? Well, that depends on the usage of your system and the data modification activity
    Usually yes, but there are exceptions in my opinion. Most systems will benefit more from the presence of these indices than they will lose from the overhead of maintaining them.
    Even if an index may be subject to discussion, you almost certainly would want to have and keep the FK in place. Sure, you can handle much stuff from the app, but what if someone does not use this app? Or you have some other interface to other systems or load data? Without integrity constraints all bets are off that you can really trust the answer you get from the database.
    A good & sound schema and proper queries with good WHERE clauses. [:)]
  10. Hmnt New Member

    WOW .. thanks a lot I've learned a lot from your comments!
    In nutshell - I'll apply FK as and where applicable. For now I can consider indexing as and where necessary it does cost a bit but the gain is more .. so I'll index FK and other combinations as and where necessary.
    • I believe I can index a single column or column combinations (which might or might not be FK).
    • I believe indexing on int type columns is less costly compared to indexing string columns.
    Pls correct me if anything else is missing.
    Oh and BTW, I've some complex queries (i.e. multiple JOINs and Lookup operations) where do you think is the best place to put them for expert review and comments ?
    Shud I open a new thread? Any other sites that you want to suggest .. like stackoverflow.com, etc..
  11. FrankKalis Moderator

    - Yes, a FK constraint can be defined on a set of columns in which the number of columns can be 1 or more. [:)]
    - Yes, typically (but not necessarily) your key columns are of some numeric data type and it's less costly to maintain such indices
    As for your query questions: I would probably give this site a try and start a new thread here and see if you are happy with the answers. If not, there are a couple of other good SQL Server related sites out there, such as SQL Server Central.com & SQLTeam.com.
    Personally I'm not a fan of stackoverflow. Yes, I know at the moment this almost sounds like blasphemy, but that's my opinion and I won't go into details here. At the end of the end it is your decision anyway. [:)]

Share This Page