Writing SQL queries is something NHibernate eliminates for the developers when writing data access code. Complex data models and Hibernate (HQL) queries can often lead to slow performing generated SQL resulting in overall database access performance problems. Also, if the volume of data returned is large, the reflection operations which load data into the objects can add performance lag during data loading compared to instantiating objects using the new statement and assigning data to properties natively in C#.

Orasis Mapping Studio uses SQL to build your data access and can map the queries directly to the same Domain objects that NHibernate maps to. To eliminate the performance problems, we can take the NHibernate queries, capture the SQL, and optimize them to a pure ADO.NET method returning a list of NHibernate Domain objects. All this can be done through the Orasis Mapping Studio.

Capturing the SQL

You can capture the generated SQL in the NHibernate generated log when using the

<property name="show_sql">true</property>

configuration element in the NHibernate config file, either through the console or log file when running the application.

Optimizing the Query

Once you have captured the SQL, launch the Orasis Mapping Studio and open or create a mapping project connecting to your database. Then launch the Query Designer through the Tools, Query Designer menu.

Paste the query into the query designer and let it parse it. You will be able to see your query visually now and execute it with the parameters it may have to see its execution time. You can then optimize the query by minimizing sub queries and writing efficient joins.

Map to the existing NHibernate Domain Object

Once you are satisfied with the query, click the Create mapping button. This will walk you through mapping your query to the object that you want it mapped to. In the third page of the wizard, where the Type Selector appears, select the Import type tab and select the Assembly in which your Domain objects are and then select the Domain object to map to. For a more in depth walk through of creating mappings read here: http://www.orasissoftware.com/blog/post/2009/03/28/Smart-Code-Generation-for-Data-Access-Using-Orasis-Mapping-Studio.aspx

Once the mapping is complete and the project is built you will now have a generated method built with pure ADO.NET to execute the query and return you the Domain objects in a list, with the parameters necessary to execute the query. The method will be generated with very efficient ADO.NET code to execute your query and load your objects.

Your Data Access Code will now perform much faster, the SQL will be well tested and the NHibernate domain object that you have built your application will be reused. It will be all manageable through the Orasis Mapping Studio project file for future modifications to your query, object and mappings.