2

I heard that Sequence is faster than Identity in Sql Server. But, I dont think it is a good idea to use it in Entity Framework. Becuase, to use Sequence in Entity Framework, you should make an extra call to get the next sequence.

int sequence = context.Database.SqlQuery<int>("SELECT NEXT VALUE FOR MySequenceName").FirstOrDefault();

Therefore, I think it will be slower to use Sequence in Entity Framework. Am I right? Does anyone have any idea?

Arif YILMAZ
  • 5,754
  • 26
  • 104
  • 189
  • 1
    `SEQUENCE` isn't inherently faster than `IDENTITY`. You can control the cache size which might make a small benefit in some cases but for large multi row inserts sequence is actually slower http://dba.stackexchange.com/a/31095/3690 – Martin Smith May 17 '16 at 07:48
  • 1
    In general, you shouldn't make any changes to your code "because you heard X was faster than Y". You need context for the assertion (it's rare for there to be a situation where X always outperforms Y and is a 1-1 replacement for Y - notably because if it were true, they'd just replace the implementation of Y with X). You also need to consider whether you actually have a performance issue to start with, and whether its actually caused by `Y`. – Damien_The_Unbeliever May 17 '16 at 07:50
  • thank you all for your valuable comments – Arif YILMAZ May 17 '16 at 07:52

3 Answers3

4

yes, the Sequence is faster than identity since you get it from memory rather than from the disc.

They can be used in as many situations as you want but I think their real power is to:

  • get the key before inserting
  • use a unique key from the same Sequence in multiple tables

if you don't need these points then I would just use the identity, one call to db, one transaction. using it in entity framework, as you say you will need an extra call just to get the key, which does not make it really useful.

Ermir Beqiraj
  • 867
  • 11
  • 24
2

Not necessarily (that you need multiple db calls), you can use EF in combination with Stored Procs and put the logic of ID retrieval from the sequence and the insert(s) in the Stored Proc. This could streamline and abstract the retrieval and assigning of Ids so you do not have to write that as part of your c# code.

If you want to Keep It Simple, only use Sequence where you think speed is critical and that not using Sequence would impact the performance of the Sql Statement. For example, on tables where you expect to execute large number of batch inserts or want to determine the id up front to make inserts into multiple tables more streamlined. All other tables you could continue to use Identity.

Also see this previous SO answer which addresses how you can use Sequence in your EF code, its pretty much what you already have in your question but with the rest of the c# code included.

Community
  • 1
  • 1
Igor
  • 60,821
  • 10
  • 100
  • 175
1

I heard that Sequence is faster than Identity in Sql Server.

Last time I checked, it was the exact opposite: Sequence and Identity Performance. Of course, this situation can (and probably will) change in next SQL Server releases, but for now - you heard it wrong.

Regarding the "extra call" - you don't think that the result of scope_identity() appears in your application by pure magick, don't you?

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • link is broken, [here's another one](https://byobi.wordpress.com/2012/09/02/sequence-vs-identity-performance-comparison/) - summary: identity is faster, by a LOT if you don't use sequence caching. – gbjbaanb Mar 27 '23 at 11:48