I have this queston that I cannot get a clear answer on. I have searched the internet to find out if using foreign keys have any performane benefits but some articles yes and some say no. So what should I believe here. Does foreign keys have any performance benefits.
i guess you could look at it a couple of ways, and here's a different perspective
suppose you want to add a row, but you don't have FKs defined
however, you are concerned about integrity, so, like the many good application-centric developers of years gone by, you issue a SELECT on the parent table, using the value of the PK, to ensure that it actually exists (otherwise inserting the child would create an orphan)
thus, the full operation consists of these oprations: SELECT, check error status, proceed with INSERT if okay, else issue error message ("parent does not exist")
nowadays, the scenario is different
by allowing the database to enforce integrity, the full operation becomes: INSERT, check error status, issue error if not okay
in other words, without FKs, ensuring integrity involves two calls to the database engine, whereas with FKs, only one
If you are talking about selects (rather than inserts) then I have seen a couple of articles that suggest that the engine can formulate a better plan in very specific circumstances (I remember it involved single column dates).