Sequential for Microsoft SQL Server or MySQL? about idgen

[RobThree]

Huh? It's (semi)sequential for any platform... Have you read the README?

[djordjedjukic]

Yes I did. AFAIK if you want to increase performance in MSSQL you need to use sequential part at end, and for MySql you need to use sequential part at beginning. In other case you don't get any performance improvements.

[RobThree]

I don't know where you read that but that's nonsense. You want a sequence to be sequential because of less index fragmentation (especially when it's a clustered index, which PK's usually are). There's no difference between:

100001
100002
100003
100004

And:

100000
200000
300000
400000

Both are sequential.

[djordjedjukic]

If you have time, you can read this article:
https://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database

[RobThree]

You're missing the point completely. That article is about GUID's in binary and in string form; MySQL doesn't have a GUID type so you need to store it as VARCHAR. MSSQL does have a GUID type and can store it in binary (a much more efficient) form. Since the string representation doesn't reflect the actual order of the bytes in a GUID there will be some difference in where the sequence is etc. which in turn affects how much index fragmentation occurs.

But that's all moot because ID's generated by IdGen are no GUID's and shouldn't be stored as such (unless you want to fit a square peg in a round hole). ID's generated by IdGen are (unsigned) 64 bit integers which should be stored as such. Both MySQL and MSSQL support this (both call it BigInt).

Also, IdGen is designed to be used in a distributed way; it can generate Id's without hosts / processes / threads having to coordinate stuff to avoid collisions during ID generation. One of the big advantages of GUID's (or UUID's) is that, by their nature, the chance of a collision is incredibly, stupendously, small (so you won't need to coordinate anything either). But since they fragment indices they're not always a good choice. IdGen (or SnowFlake or derivatives) generate ID's that avoid collisions by using a 'generator' part which, essentially, assigns each generator it's own 'range' to generate ID's in (avoiding collisions with other generators) but are still (semi)sequential (less index fragmentation). These ID's are half the size (64 bits vs. 128 bits for (G/U)UID's but should suffice in most situations. Which one you need is up to you to decide.

I keep saying "(semi)sequential" because, within the same timestamp (which, by default, is a millisecond but can be anything depending on the mask and TimeSource there will be non-sequential ID's. So if you have more than one generator (which, again, is the intended scenario for IdGen) then, yes, you will have mostly sequential ID's but not within the same timestamp (which, again, will still be orders of magnitude better than "random" ID's and will not fragment your indices as bad as (G/U)UID's do).

[djordjedjukic]

Ouh, obviously I didn't read carefully. I thought library is generating sequential GUID's and not integers.
So we will have integer which is not auto increment, it will have good performance (like int), but will not be predictable (like guid).
I would say this is even better, you are getting the best from both worlds.
And one more question, IdGenerator should be singleton?

I apologize for not reading doc carefully, thanks on patience and detailed explanation.

ps. Maybe you should put one example of ID in readme, so it can be easier to spot how ID looks like.

[RobThree]

but will not be predictable (like guid)

It's actually highly predictable. It may not be completely sequential like an auto-incremented ID (usually) is, but it is still predictable to a high degree.

And one more question, IdGenerator should be singleton?

It depends on your use-case. It could be singleton, but more likely you'll want an instance per thread or process. Again, that's what the generator-part is for; it ensures different threads, processes, hosts, whatever don't generate colliding ID's without having to coordinate.

ps. Maybe you should put one example of ID in readme, so it can be easier to spot how ID looks like.

I'll consider putting that in the README.

Depending on what you're looking for you may also want to consider a ULID, which has been implemented for many languages too (including a .Net implementation also by yours truly)