Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unanswered: Query Performance

    I have two tables.
    Employee
    EmployeeCode int Primary Key

    Employee_Stock
    EmployeeCode int
    StockCode varchar(10)
    Primay key on (Employeecode, StockCode)..

    There is no foreign key relation between these 2 tables.
    Now my question is which query give more performance. and why?
    1. Select * from
    Employee INNER JOIN Employee_Stock on Employee.Employeecode = Employee_Stock.EmployeeCode

    2. Create a foreign Key between Employee and Employee_Stock for EmployeeCode. and run the same query.

    Actually we forgot to put the foreign key relationship between these 2 tables and we have lot of queries joining them..
    Now if we add foreignkey, is it going to improve the performance or not?

    Thanks
    Ramesh

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Foreign keys enforce business rules and relational integrity, but in themselves do not increase performance. In some cases they may actually decrease performance while the database maintains relational integrity during inserts, updates, and deletes. You should still establish them to save you all sorts of headaches later when you find that your data does not make sense.

    If you have already have indexes on your joined columns then I don't think your query is about as efficient as it is going to get. Show a query plan and verify that the indexes are being used.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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