Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    6

    Unanswered: entity relationships and performance

    I have large, very heavily used database (SQL 2000). Performance is critical. I have a job that runs on a nightly basis to delete records no longer need from specific tables (as to keep them manageable).

    My question, does have entity relationships on these tables adversely affect performance? The cleanup batch job is taking so long now that I'd like to delete table relationships to speed it up.

    I understand the value for maintaining data integerity, but any insight as to how relationships affect performance?

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Posts
    6
    Well, I delete the relationships. The batch job that deleted records went from 6-10 hours to 22 minutes.

    So, while I don't think there is a major performance hit by having relationships established and doing selects/inserts/updates, there obviously is when it comes to deletes.

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    whether you are doing inserts, updates or deletes there wil lalways be a "HIT" due to RI. Insert or Update 10,000 records at one time, both with and without RI and you will see a diffrence. The question is does removal of RI justify the speed over potentialy corrupted data.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Apr 2002
    Posts
    6
    Obviously, the performance gain was substantial. Are there any performance gains via relationships, i.e. does the query optimizer leverage relationships when creating execution plans?

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    No. The optimizer looks at indexes, statistics and hints to decide the best query plan.
    Referential integrity is there solely to enforce the quality of the data.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •