CODEWITHSUNDEEP
entity-frameworkdatabase-designentity-framework-5composite-key
Dabblernl
Dabblernl

Reputation: 16121

What is the best order for the fields of a composite key?

Say I have a table with the following fields:

A League will host many Matches. Usually each League will use its own local database, so the LeagueID field will be the same in all records for this local database. Once a year the League uploads its data to the national authority and then the LeagueID will be necessary to dsicriminate the Matches that have the same MatchID.

What is the best way to implement the composite primary key (using the EF Fluent API)?

Entity<Match>.HasKey(match=>new {match.LeagueID,match.MatchID})

OR

Entity<Match>.HasKey(match=>new {match.MatchID,match.LeagueID})

To the human eye the order League - Match is logical, as it will hold the Matches of a particular League together. But I understood that when composing a composite key it is important for performance reasons to use the most discriminating field first.

Upvotes: 2

Views: 332

Answers (2)

EBarr
EBarr

Reputation: 12026

I think you can have your cake and eat it too.

The Database
When implementing a key in the database generally a narrower key with more selective field(s) will yield better performance. This holds true for single & composite keys. I said generally, because a more selective index that doesn't really match your query pattern can be pretty useless. For example, in your composite key, if MatchID is first (the more selective), but you query more frequently by LeagueID (less selective), the selectivity will work against you.

The real issue, i think, is not is index A or B more selective, but "do you have appropriate indexes for the ways you query?" (and enforce data integrity, but that's a different discussion). So you need to figure out how you query this table. If you query by :

  • LeagueID most of the time -- index LeagueID, MatchID
  • MatchID most of the time -- index MatchID, LeagueID
  • composite LeagueID & MatchID the majority of the time -- index MatchID, LeagueID
  • a mixed bag -- you may want two indexes one for each order, but you'll have to figure out if the extra overhead of maintaining two indexes is worth the hit on insert/update/delete.

EF & The Query
For the most part, the order of the columns in your query (or the way you build a match in EF) won't make a difference. Meaning where a=@a and b=@b will yeild the same query plan & performance as where b=@b and a=@a.

Assuming you're using SQL Server, the order you write a where clause matters very little. The books on line explain the issue succinctly, stating:

The order of evaluation of logical operators can vary depending on choices made by the query optimizer. ).

Upvotes: 5

Tasio
Tasio

Reputation: 351

You can choose the order of the fields in the database by using the HasColumnOrder

Upvotes: 0

Related Questions