Tag: O/R Mappers
I guess I am in that O/R Mapper Performance Mood given the past few blog posts:
Identity Generation via KeyTable
I think most developers probably use an Identity Column in SQL Server to generate unique identities for entities per table unless they need to use a GUID. If you are using simple integer fields for identities this isn't the fastest way to handle identities for your entities. If you really want the speed, you might be interested in using a KeyTable. You can find a description of KeyTable in Fowler's PoEAA on page 222. Since I am going to be using LightSpeed O/R Mapper to demonstrate this technique, I will just shamelessly steal the words from their documentation:
“KeyTable identity generation (Fowler PoEAA) uses a single table in your database that stores the current identity value. This allows LightSpeed to secure a block of identities (the default size being 10 and is configurable) and use them to set the identity value of newly created identities in your system. This works because every identity in the database, even in different tables, is unique.
KeyTable is a great identity method if you need high performance from you database as LightSpeed does not need to flush new entities to obtain identity values. That means less round trips to the database and therefore a more speedy application.”
The idea here is that if your O/R Mapper supports KeyTable, it reduces the number of roundtrips to the database by not having to return the identity value from parent entities saved to the database to insert them as part of the child entities, etc. With KeyTable, typically the O/R Mapper will request a configurable number of identity values from the KeyTable to be used by the O/R Mapper on subsequent requests.
KeyTable in LightSpeed O/R Mapper
LINQ To SQL does not support the use of a KeyTable, so let's use LightSpeed which feels much like LINQ To SQL. To get KeyTable Identity Generation to work with LightSpeed, you of course need to create a KeyTable. There is a script provided with LightSpeed to help with this task. One can then change a quick setting in the LightSpeedContext to enable KeyTable as opposed to the default Identity Key Generation using
IdentityMethod = IdentityMethod.KeyTable
When you use a KeyTable from time-to-time the O/R Mapper needs to request a new bank of keys from the KeyTable. LightSpeed grabs 10 at a time by default but the value is configurable. In this case it grabbed 40 - 49 and set the next available key as 50:
Simple enough. Going back to our online ecommerce store that just so happens to sell green tea and has a Category and Product Table with a 1:many realtionship, we can insert a new “Green Tea” Category and several japanese green teas simply as:
using (var uow = UnitOfWork.Create())
{
var category = new Category
{
Name = "Green Tea",
Products =
{
new Product {Name = "Gyokuro"},
new Product {Name = "Sencha"},
new Product {Name = "Matcha"}
}
};
uow.Add(category);
uow.SaveChanges();
}
Now the beauty here is that since LightSpeed does not have to first insert the Category and then retrieve its primary key before inserting the products, the insert of the Category and all its Products can happen is a single batched set of queries to the database in only 1 roundtrip:
Tell me that ain't sexy!!!! This would take a minimum of 2 roundtrips for Identity Key Generation.
Conclusion
Hopefully this set of posts is giving you some interest in understanding O/R Mappers and exactly what your O/R Mapper is doing behind the scenes. Again, it is important to profile the communication between the O/R Mapper and database to understand just how chatty and optimized the communication is during the calls. SQL Server Profiler is definitely a way to look at the communication. NHibernate Profiler will help with NHibernate. Various performance profilers like ANTS Profile and dotTrace can help. Most O/R Mappers have a way to log like the Log Property on LINQ To SQL's DataContext Class.
David Hayden