I have a remote table from a linked server that I need to join to a local table in SQL Server 2008 R2 using Nhibernate 3.3.
I am using Nhibernate 3.3.1.4000 and FluenNhibernate to map the class. It works if I use the normal join, but is is very slow (around 30s).
In SQL server management studio, if I use the "remote" hint, the query returns in less than a second.
Slow Query (30+ seconds)
Select table1.field1, table2.field2 from table1 inner join table2 on table1.id = table2.id
Fast one (less than 1 second)
Select table1.field1, table2.field2 from table1 inner remote join table2 on table1.id = table2.table1_id
In code, I'm getting the result using nhibernate.linq :
session.Query<table1>.FetchMany(x => table2s);
I'm looking for a way to force nhibernate to use the inner remote join (I'm aware that it is very sql server specific).
I also tried to find a way to map my entity to a stored procedure which would load the entity(table1) and it's child(table2) entities, but could not find a clear way to do it. Most involve using an hbm file, but I'm not sure how to map the child collections from the stored proc.
Thanks.
For the sake of completeness, I ended up creating a special view with the remote join included and mapped this view in nhibernate to a different model class.
I think it's not that easy.
You could write your own Driver which does alter the join clause on use case.
However an sql query for this use case could be much easier. I Guess you can have multiple entities per row with multiple .AddEntity() but you you have to group them in code.