2

Say I have a table with the following fields:

  • LeagueID
  • MatchID
  • SomeData

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.

Dabblernl
  • 15,831
  • 18
  • 96
  • 148

2 Answers2

5

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. ).

EBarr
  • 11,826
  • 7
  • 63
  • 85
  • Thanks -- it's usually a muddled mess between my ears:-D – EBarr Feb 01 '13 at 14:47
  • *When a=@a and b=@b* and *When b=@b and a=@a* will only perform the same when the database engine evaluates all conditions. I would suppose that the engine does not evaluate the second condition when the first is false. Just like the VB `AndAlso` or the C# `&&` operator. – Dabblernl Feb 01 '13 at 15:09
  • 1
    @Dabblernl - short circuit *can* happen, but is not guaranteed. See discussion of : [the ansi sql spec](http://stackoverflow.com/questions/789231) or [sql server](http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/). Either way, we don't know what database OP (edit: you) is working with, so I said "for the most part". – EBarr Feb 01 '13 at 16:06
  • @EBarr The links you sent about the short ciruiting of booleans are entertaining, but confirm the notion that short circuiting usually takes place in straightforward scenarios. That confirms that you should use the most discriminating field first. – Dabblernl Feb 01 '13 at 20:14
  • 1
    I'm not trying to be flippant, but I still have to disagree. Boolean evaluation is only one example. The spec says that when evaluating a where clause "it is implementation-dependent whether expressions are actually evaluated left to right". I think you're confusing the order of the fields in the index versus the order in the query text. Order in the *index* matters a great deal, in the *query text* it matters very little. I updated the question with a bit more on why this is true. – EBarr Feb 01 '13 at 21:02
0

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

Tasio
  • 351
  • 1
  • 12
  • Ok, then the question is more about the database implementation rather than EF itself. The "To the human eye the order League - Match is logical" bit is irrelevant then. – Tasio Feb 05 '13 at 08:20