Guid.comb and synchronization

Synchronize an entity between two databases.

I had set up this records with a GUID and used the guid.comb from NHibernate to generate these for me.
Then I needed to synchronize this object with an other database.

Using NHibernate, just create a new session, evict the object from the first one, and save it the next one. No problem, except a new GUID was generated....

My assumption was that an entity with a guid.com generated GUID kept its value. I was wrong.

The answer lies in the ISession.Replicate. This allowes me to copy an entity from one session to another witout losing the generated key.

  foreach (var person in persons)
  {
      sourceSession.Evict(person);
      targetSession.Replicate(person, ReplicationMode.Overwrite);
  }
  targetSession.Flush();

The ReplicationMode lets you choose the behaviour (Overwrite, LatestVersion, Ignore etc.)

Linq To NHibernate

Compile your queries

One of the hardest things to do with a database application (e.g. a c# WinForm) is to create all the queries in your app. Where to put them in the model? Use queries, or make stored procedures on the database etc etc.
Then creating all these queries in your code as strings;

"SELECT name, age, .. FROM ..."

This is a lot of work and causes runtime errors because these queries aren't checked at compile-time. Then there are issues of caching, relational data, locking etc etc. All these issues can be solved by NHibernate. This framework maps your OO-Model to your relational model and creates the queries, relations, locking etc for you.
But still there are these runtime errors which may occur, because even a Criteria/Expression in NHibernate uses strings as properties.

  .Add(Expression.Like("Title", "Linq To%"))

This can be solved using Linq To NHibernate. This extension to the ISession creates an IQueriable which uses the intellisense of Visual Studio

var q = from tasks in session.Linq<task>()
      where tasks.Title.StartsWith("Linq To")
      select tasks;

Linq To NHibernate also eases creating queries with multiple joins:

var q = from tasks in session.Linq<task>()
      where tasks.Owner.Address.City == "Dalem"
      select tasks;

I find Linq To NHibernate a great help with developing my database applications.

Optimize SQL Server 2005 Procedures

I had a stored procedure which took about 3 minutes to run, but starting this query in the management studio of sql server took 2 seconds! The problem lies in the "parameter sniffing". Google it for the exact cause, but copying the parameter variables to local variables within the procedure like the example below does the trick.

Slow procedure:

CREATE PROCEDURE GetMeMyData
@param1 NVARCHAR(20)
AS
BEGIN
SELECT id, name, otherstuff
FROM dbo.table1
WHERE param1 = @param1
END

Fast procedure:

CREATE PROCEDURE GetMeMyData
@param1 NVARCHAR(20)
AS
BEGIN
DECLARE @localParam1 NVARCHAR(20)
SET @localParam1 = @param1
SELECT id, name, otherstuff
FROM dbo.table1
WHERE param1 = @localParam1
END

At first I tought it had something to do with an outdated version of SQL Server, but it even occurs on SQL Server 2005 SP3.

Fast Clone

Ever had to make a in memory copy of a simple object? You then have to copy each property or create an IClonable implentation. Just like the simple reflection form you can use reflection to create a fast clone.
Just call the static method FastClone.Clone:

Customer c = new Customer
{
Id = Guid.NewGuid(),
Name = "Frank",
Age = 31,
Married = true
};

Customer c2 = (Customer)FastClone.Clone(c, "Id");

Optionally properties can be given to exclude from the clone.
You can download the source of FastClone here.

Enjoy!

Arithmetic Abort Enabled

I have a stored procedure in SQL Server 2005 which takes a lot of a time to run from my .Net 2.0 Framework application and sometimes even gives a "Timeout expired".
Executing the same stored procedure in SQL Server Management Studio gives the result in less than 2 seconds! Why??

The answer lays in one single setting. When SQL Server Management Studio connects to the database it sets the "Arithmetic Abort Enabled" on. This option triggers the database to create new executions plans for the stored procedures.

Setting this option improved the performance of my .Net 2.0 Framework application!