1M records tested, SQLite's random no. generator's really high quality

edwinsn · 2015-04-17 14:32:43

Testing code below, inserted one million records without a problem using sqlite's random() function to generate the IDs.

I'm going to use this simple approach for generating **unique** Ids for tables that'll be synced from multiple branch offices to a single master server. I think it'll be workable in most cases. Any comments?

program Project5;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  Forms,
  SysUtils,
  SynCommons,
  mORMot,
  mORMotSQLite3,
  SynSQLite3Static;

{$R *.res}

type
  TSQLRandomNr = class(TSQLRecord)
  private
    FNumber: Int64;
  published
    property Number: Int64 read FNumber write FNumber;
  end;

var
  myDb: TSQLRestServerDB;
  myModel: TSQLModel;
  myTable: TSQLTableJSON;
  myId, cnt: Int64;
  myRec: TSQLRandomNr;
  total: Integer;
begin
  myModel := TSQLModel.Create([TSQLRandomNr]);
  myDb := TSQLRestServerDB.Create(myModel, 'D:\testRandomNr.db3');
  myDb.CreateMissingTables;

  cnt := 0;
  total := 1000000;
  Writeln(Format('Inserting %d random numbers', [cnt]));
  myDb.TransactionBegin(TSQLRandomNr);
  while cnt < total do
  begin
    myDb.Execute('INSERT INTO Randomnr(ID, Number) SELECT ABS(RANDOM()), ABS(RANDOM())');
    Writeln(IntToStr(cnt));

    Inc(cnt);
  end;
  myDb.Commit;

  ReadLn;

end.

Info about the SQLITE random nr. generator: https://www.sqlite.org/c3ref/randomness.html

Last edited by edwinsn (2015-04-17 14:34:32)

ab · 2015-04-17 14:35:34

I'm afraid some collision may occur...

A monotonic increasing ID, with a branch office offset in the number, may sounds safer to me.
Either you reserve some ranges from a server, or you use a time-based ID generator, as proposed by previous discussion here in the forum.

alpinistbg · 2015-04-17 15:37:42

John von Neumann wrote:

Any one who considers arithmetical methods of producing random digits is, of course, in a state of sin.

Source: Wikiquote

edwinsn · 2015-04-17 15:39:02

Arnaud,

Thanks for your comment. Yes, we discussed about it here earlier, but I'm worried about the "office offset" approach that the users might input wrong Ids and it'll won't be detected until data syncing is under way. At the end, it's the program that has to handle it. We want to avoid this.

re. timestamp-based Id's generation, I'm not sure if the chance of collision is lower, all after all, we'll still have to combine it with random numbers.

I also have found this simple node.js algorithm for generating low-collision UUID: https://github.com/recurly/node-druuid its worth looking at, but I'm not sure how to port it to Pascal...

alpinistbg · 2015-04-17 16:19:58

Using an ID with high level of entropy can literally devastate the structure of database files (or RAM cache - whichever you'd prefer). Ordering your records in the time domain will tend to load maximum number of B-pages into memory and will make meaningless all attempts to increase performance.

edwinsn · 2015-04-18 05:35:15

@alpinistbb, yes, there is no true random number generation without resorting to some kind of physical devices, and I guess that's out of the scope of the topic.

So my latest method is a time sortable 64bit UUID generation: http://synopse.info/forum/viewtopic.php?id=2522

mORMot Open Source

#1 2015-04-17 14:32:43

1M records tested, SQLite's random no. generator's really high quality

#2 2015-04-17 14:35:34

Re: 1M records tested, SQLite's random no. generator's really high quality

#3 2015-04-17 15:37:42

Re: 1M records tested, SQLite's random no. generator's really high quality

#4 2015-04-17 15:39:02

Re: 1M records tested, SQLite's random no. generator's really high quality

#5 2015-04-17 16:19:58

Re: 1M records tested, SQLite's random no. generator's really high quality

#6 2015-04-18 05:35:15

Re: 1M records tested, SQLite's random no. generator's really high quality

Board footer