Strong performance impact of KeyTable identifier generation method

It looks like the KeyTable id generation method has very strong performance impact. I attach an example.

Please do the following:

1. Unzip the attachment
2. Add Mindscape dlls and SQLite dll to Lib folder
3. Run the solution
4. Click "AddStoreysAndApp" button. Currently most of the code is commented out, and only ExpressionValues (Enitity<Int64> with KeyTable id generation) are added.
5. Look at the lowest listbox - it shows the execution time. On my PC it is 6.77 sec, of which adding objects (creating and adding to UOW) takes 6.55 sec. (the executed method is: DMPProject.cs / DMPProject.AddStoreysAndAppartmentsExecute)
6. Go to DataImplemented.cs / ExpressionValue.GeneratedId(). Comment out the current code and uncomment the random id generation
7. Run the solution and click "AddStoreysAndApp" button. The execution time decreases dramatically - on my PC it is 0.11 sec, of which adding objects is 0.03 sec.
8. I also checked that GuidComb generation is much faster than KeyTable. To ensure this please go to DMPProject.cs / DMPProject.AddStoreysAndAppartmentsExecute. Comment out 2 lines below "// Adding ExpressionValues - Int64/KeyTable as identifier" and uncomment 2 lines below "// Adding Storeys - Guid/GuidComb as identifier". Now the execution time is 0.31 sec, of which adding objects is 0.04 sec.
9. I also tried to change Int64 to Int32 as identifier of ExpressionValue (not included in the attachment), but it didn't improve the performance of KeyTable id generation.

Please let me know why the KeyTable id generation is so slow and how to avoid this problem.

By default, KeyTable gets IDs in blocks of 10.  You are adding 1000 entities to the database, which means KeyTable needs to hit the database 100 times just to get IDs (well, 200 times actually, as each request involves a SELECT and an UPDATE).

If you change the LightSpeedContext.IdentityBlockSize to 1000, then LightSpeed can get all the IDs it needs in one database request.  In my test this dropped the allocation time from 6 seconds down to 0.2 seconds.

That said, even doing 100 requests for blocks of 10 should be much faster than 6 seconds.  It seems this occurs because KeyTable has to do its SELECT and UPDATE in a transaction, and this slows SQLite down.  If you put a TransactionScope around your Add loop (so that the bulk adds are all occurring within the same TransactionScope), then this overhead is not incurred and I see a speed of around 0.3 seconds (with the default IdentityBlockSize of 10).

It works great! Thank you - you're ORM masters :-)