Do you use between to return data that has dates? Do you know that between will get everything since midnight from the first criteria and up to midnight exactly from the second criteria. If you do BETWEEN 2006-10-01 AND 2006-10-02 then all the values that are greater or equal than 2006-10-01 and less or equal to 2006-10-02 will be returned. So no values after 2006-10-02 midnight will be returned. Let’s test this out, first let’s create this table

I just found out today that you have to have everything configured in structuremap before calling getInstance. I am using structureMap 2.4.9. To test this I created. 2 interfaces. Namespace Interfaces Public Interface IClass1 Sub somefunction() End Interface End Namespace``` ```vbnet Namespace Interfaces Public Interface IClass2 Sub someotherfunction() End Interface End Namespace``` And 2 Classes. ```vbnet Public Class Class1 Implements Interfaces.IClass1 Public Sub somefunction() Implements Interfaces.IClass1.somefunction Console.WriteLine("class1") End Sub End Class Public Class Class2 Implements Interfaces.IClass2 Public Sub someotherfunction() Implements Interfaces.IClass2.someotherfunction Console.WriteLine("class2") End Sub End Class And a module to run it.

Previous posts can be found here: Part One – The Beginning Part Two – The Domain Model Part Three – Testing the Schema Setting up the repositories for our objects is where this really starts to get fun for me. This is what allows us to work with the persisted objects so easily from our application code, without all the SQL getting in the way. The first thing we want to think about here is what we need the repository to do. Add/Delete/Update all come to mind of course. As well as retrieval of single objects and collections. These will be pretty much standard behaviors across most of our objects. So lets’ look at the interface first:

If you want to learn then you have to read and I read blogs (among other things). But how do you find the good things to read? It takes a while to find the good blogs, so here is a little list. Most of these guys do C# programming so I have to translate to adapt it to my situation but I don’t mind. Lets start with Ayende Rahien he is the maker of much useful software like Rhino mocks and some other things starting with Rhino. He posts a lot, usually more than once a day.

Index [Prelude][1] [Part 1][2] [Part 2][3] [Part 3][4] [Conclusion][5] So here we are. We all saw the figures. Nothing we didn’t already know, so let’s not dwell on the obvious. NHibernate will always be slower than the rest. If you look at the figures and decide not to use NHibernate or any other ORM from now on, then go right ahead, you just missed the point altogether. You didn’t notice the rest of the design did you? You only looked at the numbers and they told you nothing. Do you really want to maintain an application that has a couple of hundred of those sqlclient methods? I don’t. Did you see how structuremap made it easy to switch from one implementation to the other? So, if needed, you can always write another one for speed. Did you notice how you could write a generic class with nHibernate to do the selectall? Probably not, but here it is.

Index [Prelude][1] [Part 1][2] [Part 2][3] [Part 3][4] [Conclusion][5] This is going to be another lengthy post. But you can skip all the code bits and go to the end. Or wait a bit and read the conclusion in the last post. First, nHibernate needs some sort of configuration Imports NHibernate.Mapping.Attributes Imports NHibernate Imports NHibernate.Cfg Imports StructureMap Namespace DAL.CRUD.Hibernate ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; &lt;CLSCompliant(True)&gt; _ &lt;Pluggable("Live")&gt; _ Public Class NHibernateConfiguration #Region " Private members " ''' &lt;summary&gt; ''' Holds the Configuration so that the subclasses can use it. ''' &lt;/summary&gt; ''' &lt;remarks&gt;Of type Nhibernate.Cfg.Configuration.&lt;/remarks&gt; Private _Configuration As Configuration ''' &lt;summary&gt; ''' Holds the sessionfactory so that the sub classes can use it. ''' &lt;/summary&gt; ''' &lt;remarks&gt;is an interface of type Nhibernate.ISessionFactory&lt;/remarks&gt; Private _SessionFactory As ISessionFactory ''' &lt;summary&gt; ''' Holds the Databaseserver so we can easily switch between Test and productionservers. ''' &lt;/summary&gt; ''' &lt;remarks&gt;is of type string and will be used in the connectionstring ''' &lt;/remarks&gt; Private _ServerName As String ''' &lt;summary&gt; ''' Holds the Database so we can easily switch between Test and productiondatabases. ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Private _Database As String ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Private _SessionFactoryString As String #End Region #Region " Constructors " ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public Sub New() MakeConfiguration() End Sub #End Region #Region " Public properties " ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;value&gt;&lt;/value&gt; ''' &lt;returns&gt;&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public ReadOnly Property Configuration() As Configuration Get If _Configuration Is Nothing Then MakeConfiguration() End If Return _Configuration End Get End Property ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;value&gt;&lt;/value&gt; ''' &lt;returns&gt;&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public ReadOnly Property SessionFactory() As ISessionFactory Get If _Configuration Is Nothing Then MakeConfiguration() End If If _SessionFactory Is Nothing Then MakeSessionFactory() End If Return _SessionFactory End Get End Property ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;value&gt;&lt;/value&gt; ''' &lt;returns&gt;&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public ReadOnly Property SessionFactoryString() As String Get Return _SessionFactoryString End Get End Property ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;value&gt;&lt;/value&gt; ''' &lt;returns&gt;&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public ReadOnly Property Database() As String Get Return _Database End Get End Property ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;value&gt;&lt;/value&gt; ''' &lt;returns&gt;&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public ReadOnly Property ServerName() As String Get Return _ServerName End Get End Property #End Region #Region " Private methods " ''' &lt;summary&gt; ''' Makes the configuration and sets the property ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Private Sub MakeConfiguration() Dim _DBObjectSettings As New DBObjectSettings() _ServerName = "Server" _Database = "Database" _Configuration = New Configuration() _Configuration.SetProperty(Environment.ConnectionProvider, _DBObjectSettings.ConnectionProvider) _Configuration.SetProperty(Environment.Dialect, _DBObjectSettings.Dialect) _Configuration.SetProperty(Environment.ConnectionDriver, _DBObjectSettings.ConnectionDriver) _Configuration.SetProperty(Environment.ConnectionString, String.Format(_DBObjectSettings.ConnectionString, _ServerName, _Database)) End Sub ''' &lt;summary&gt; ''' Makes the sessionfactory ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Private Sub MakeSessionFactory() Try Dim _DBObjectSettings As New DBObjectSettings() HbmSerializer.Default.HbmDefaultAccess = _DBObjectSettings.HbmDefaultAccess HbmSerializer.Default.Validate = True _Configuration.AddInputStream(HbmSerializer.Default.Serialize(System.Reflection.Assembly.GetExecutingAssembly())) Catch ex As Exception Console.WriteLine(ex.Message) End Try Try _SessionFactory = _Configuration.BuildSessionFactory() Catch ex As Exception Console.WriteLine(ex.Message) End Try End Sub #End Region End Class End Namespace``` Then it needs some additional tests. ```vbnet &lt;Test()&gt; _ Public Sub Select_MSP_SQLClientnhibernate_1000000() Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("nhibernate") Assert.IsNotNull(_crud.Selectall) Assert.Greater(_crud.Selectall.Count, 0) Assert.AreEqual(1000000, _crud.Selectall.Count) End Sub &lt;Test()&gt; _ Public Sub Select_MSP_SQLClientnhibernate_1000() Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("nhibernate") Assert.IsNotNull(_crud.Selectall(1000)) Assert.Greater(_crud.Selectall(1000).Count, 0) Assert.AreEqual(1000, _crud.Selectall(1000).Count) End Sub &lt;Test()&gt; _ Public Sub Select_MSP_SQLClientnhibernate_1000_1000() Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("nhibernate") For i As Integer = 0 To 999 Assert.IsNotNull(_crud.Selectall(1000)) Assert.Greater(_crud.Selectall(1000).Count, 0) Assert.AreEqual(1000, _crud.Selectall(1000).Count) Next End Sub``` Then it needs some implementation. ```vbnet Imports NHibernate Imports StructureMap Namespace DAL.CRUD.Hibernate &lt;Pluggable("nhibernate")&gt; _ Public Class MSPCoordinate Implements Interfaces.IMSPCoordinate ''' &lt;summary&gt; ''' Holds the sessionfactory so that the sub classes can use it. ''' &lt;/summary&gt; ''' &lt;remarks&gt;is an interface of type Nhibernate.ISessionFactory&lt;/remarks&gt; Protected SessionFactory As ISessionFactory Public Sub New() SessionFactory = New NHibernateConfiguration().SessionFactory End Sub Public Function Selectall() As System.Collections.Generic.IList(Of Model.MspCoordinate) Implements Interfaces.IMSPCoordinate.Selectall Dim _Session As ISession = Nothing Dim _ReturnList As IList(Of Model.MspCoordinate) = Nothing _Session = SessionFactory.OpenSession _ReturnList = _Session.CreateQuery("FROM MspCoordinate").SetMaxResults(1000000).List(Of Model.MspCoordinate)() If _Session IsNot Nothing Then _Session.Close() _Session.Dispose() End If Return _ReturnList End Function Public Function Selectall1(ByVal top As Integer) As System.Collections.Generic.IList(Of Model.MspCoordinate) Implements Interfaces.IMSPCoordinate.Selectall Dim _Session As ISession = Nothing Dim _ReturnList As IList(Of Model.MspCoordinate) = Nothing _Session = SessionFactory.OpenSession Dim q As IQuery q = _Session.CreateQuery("FROM MspCoordinate") q.SetMaxResults(top) _ReturnList = q.List(Of Model.MspCoordinate)() If _Session IsNot Nothing Then _Session.Close() _Session.Dispose() End If Return _ReturnList End Function End Class End Namespace``` And then the times. <table border="1"> <tr> <th width="400"> Test </th> <th width="100"> Time </th> </tr> <tr> <td> Select_MSP_SQLClientnhibernate_1000000 </td> <td align="right"> 2:08.34 m </td> </tr> <tr> <td> Select_MSP_SQLClientnhibernate_1000 </td> <td align="right"> 0:00.09 m </td> </tr> <tr> <td> Select_MSP_SQLClientnhibernate_1000_1000 </td> <td align="right"> 1:35.81 m </td> </tr> </table> I also tried this with a transaction, but as can be expected with a select of this type, it didn&#8217;t help much. <table border="1"> <tr> <th width="400"> Test </th> <th width="100"> Time </th> </tr> <tr> <td> Select_MSP_SQLClientnhibernatetrans_1000000 </td> <td align="right"> 2:48.07 m </td> </tr> <tr> <td> Select_MSP_SQLClientnhibernatetrans_1000 </td> <td align="right"> 0:00.15 m </td> </tr> <tr> <td> Select_MSP_SQLClientnhibernatetrans_1000_1000 </td> <td align="right"> 2:14.03 m </td> </tr> </table> And the last thing I tried was with createcriteria instead of createquery. <table border="1"> <tr> <th width="400"> Test </th> <th width="100"> Time </th> </tr> <tr> <td> Select_MSP_SQLClientnhibernatecrit_1000000 </td> <td align="right"> 2:08.21 m </td> </tr> <tr> <td> Select_MSP_SQLClientnhibernatecrit_1000 </td> <td align="right"> 0:00.25 m </td> </tr> <tr> <td> Select_MSP_SQLClientnhibernatecrit_1000_1000 </td> <td align="right"> 1:36.35 m </td> </tr> </table> * * * <font color="Red">Need help with VB.Net? Come and ask a question in our <a href="http://forum.lessthandot.com/viewforum.php?f=39">VB.Net Forum</a></font> [1]: /index.php/DesktopDev/MSTech/nhibernate-performance-against-stored-pr [2]: /index.php/DesktopDev/MSTech/nhibernate-performance-against-stored-pr-1 [3]: /index.php/DesktopDev/MSTech/nhibernate-performance-against-stored-pr-2 [4]: /index.php/DesktopDev/MSTech/nhibernate-performance-against-stored-pr-3 [5]: /index.php/DesktopDev/MSTech/nhibernate-performance-against-stored-pr-4

I was reading Itzik Ben-Gan’s An Introduction to New T-SQL Programmability Features in SQL Server 2008 article yesterday after one of my friends allerted me to the following from that article For example, the plan for the following query performs an index seek on the index on the CurrencyRateDate DATETIME column: USE AdventureWorks; SELECT FromCurrencyCode, ToCurrencyCode, EndOfDayRate FROM Sales.CurrencyRate WHERE CAST(CurrencyRateDate AS DATE) = '20040701'; I was surprised by this, as we all know functions/conversions on column names are generaly bad for performance.

Index [Prelude][1] [Part 1][2] [Part 2][3] [Part 3][4] [Conclusion][5] Yes The stored procedures came to town. Here is the first one. CREATE PROC spo_getMspCoordinates AS SELECT TOP 1000000 id, a, WaveLenghtinnm FROM dbo.tbl_MSPCoordinate GO This just gets the first million out of that database. Now we hit a snag and something that kills performance. But I can’t think of a reasonable way around it without using dynamic sql. So here it is.

Index [Prelude][1] [Part 1][2] [Part 2][3] [Part 3][4] [Conclusion][5] So like I promised I would write a little comparison. First: Is this really useful? No, not really just an interesting exercise. Because in the end performance isn’t everything. So here we go. First I created a table. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_MSPCoordinate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tbl_MSPCoordinate] GO CREATE TABLE [dbo].[tbl_MSPCoordinate] ( [Id] [bigint] IDENTITY (1, 1) NOT NULL , [MSP_id] [varchar] (36) COLLATE Latin1_General_CI_AS NULL , [WaveLenghtinnm] [decimal](19, 5) NOT NULL , [A] [decimal](19, 5) NOT NULL , [Added_By] [varchar] (20) COLLATE Latin1_General_CI_AS NULL , [Added_On] [datetime] NULL ) ON [PRIMARY] GO``` Well I already had this and it happens to be filled with rows. Several million to be exact. Then I created my class. MSPCoordinate. ```vbnet Imports System.Text Imports NHibernate.Mapping.Attributes Namespace Model ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; &lt;CLSCompliant(True)&gt; _ &lt;[Class](0, table:="tbl_MSPCoordinate", lazy:=False)&gt; _ Public Class MspCoordinate #Region " Private members " ''' &lt;summary&gt; ''' A local variable called _id of type String ''' &lt;/summary&gt; ''' &lt;remarks&gt;Has Property Id&lt;/remarks&gt; Private _id As Int64 ''' &lt;summary&gt; ''' A local variable called _a of type Decimal ''' &lt;/summary&gt; ''' &lt;remarks&gt;Has Property A&lt;/remarks&gt; Private _a As Decimal ''' &lt;summary&gt; ''' A local variable called _waveLenghtinnm of type Decimal ''' &lt;/summary&gt; ''' &lt;remarks&gt;Has Property WaveLenghtinnm&lt;/remarks&gt; Private _waveLenghtinnm As Decimal #End Region #Region " Constructors " ''' &lt;summary&gt; ''' Default empty constructor ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public Sub New() Me.New(0, 0, 0) End Sub ''' &lt;summary&gt; ''' Constructor with all the fields ''' &lt;/summary&gt; ''' &lt;param name="A"&gt;DataType = Decimal&lt;/param&gt; ''' &lt;param name="WaveLenghtinnm"&gt;DataType = Decimal&lt;/param&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public Sub New(ByVal Id As Int64, ByVal A As Decimal, ByVal WaveLenghtinnm As Decimal) Me.Id = Id Me.A = A Me.WaveLenghtinnm = WaveLenghtinnm End Sub #End Region #Region " Public properties " ''' &lt;summary&gt; ''' This is the Id property. ''' &lt;/summary&gt; ''' &lt;value&gt;String&lt;/value&gt; ''' &lt;returns&gt;String&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; &lt;Id(0, Name:="Id") _ , Generator(1, Class:="native")&gt; _ Public Overridable Property Id() As Int64 Get Return _id End Get Set(ByVal Value As Int64) _id = Value End Set End Property ''' &lt;summary&gt; ''' This is the A property. ''' &lt;/summary&gt; ''' &lt;value&gt;Decimal&lt;/value&gt; ''' &lt;returns&gt;Decimal&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; &lt;[Property]()&gt; _ Public Overridable Property A() As Decimal Get Return _a End Get Set(ByVal Value As Decimal) _a = Value End Set End Property ''' &lt;summary&gt; ''' This is the WaveLenghtinnm property. ''' &lt;/summary&gt; ''' &lt;value&gt;Decimal&lt;/value&gt; ''' &lt;returns&gt;Decimal&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; &lt;[Property]()&gt; _ Public Overridable Property WaveLenghtinnm() As Decimal Get Return _waveLenghtinnm End Get Set(ByVal Value As Decimal) _waveLenghtinnm = Value End Set End Property ''' &lt;summary&gt; ''' ''' &lt;/summary&gt; ''' &lt;value&gt;&lt;/value&gt; ''' &lt;returns&gt;&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public Overridable ReadOnly Property A_absorption() As Decimal Get If _a = 0 Then Return 0 Else If Math.Log10(_a / 100) * -1 &gt; Decimal.MaxValue Then Return Decimal.MaxValue ElseIf Math.Log10(_a / 100) * -1 &lt; Decimal.MinValue Then Return Decimal.MinValue End If End If Return Convert.ToDecimal(Math.Log10(_a / 100) * -1) End Get End Property #End Region #Region " Overrides ToString " ''' &lt;summary&gt; ''' Overridden ToString method for this class ''' &lt;/summary&gt; ''' &lt;returns&gt;String&lt;/returns&gt; ''' &lt;remarks&gt;The String value of this class represented by the ToStrings of all it's private members&lt;/remarks&gt; Public Overrides Function ToString() As String Dim ReturnString As New StringBuilder() ReturnString.Append("[Id: " & _id.ToString() & "]") ReturnString.Append(" - [A: " & _a.ToString() & "]") ReturnString.Append(" - [WaveLenghtinnm: " & _waveLenghtinnm.ToString() & "]") Return ReturnString.ToString() End Function #End Region #Region " Overrides Equals " ''' &lt;summary&gt; ''' Overridden Equals method for this class ''' &lt;/summary&gt; ''' &lt;returns&gt;Boolean&lt;/returns&gt; ''' &lt;remarks&gt;The Boolean value indictaing if this is equal to the other class&lt;/remarks&gt; Public Overrides Function Equals(ByVal obj As Object) As Boolean If obj IsNot Nothing Then If obj.GetType Is Me.GetType Then Return Me._waveLenghtinnm.Equals(CType(obj, MspCoordinate)._waveLenghtinnm) Else Return False End If Else Return False End If End Function #End Region #Region " Overrides CompareTo " ''' &lt;summary&gt; ''' Overridden CompareTo method for this class ''' &lt;/summary&gt; ''' &lt;returns&gt;Integer&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Public Function CompareTo(ByVal obj As Object) As Integer If obj IsNot Nothing Then If obj.GetType Is Me.GetType Then Return Me._waveLenghtinnm.CompareTo(CType(obj, MspCoordinate)._waveLenghtinnm) Else Return -1 End If Else Return -1 End If End Function #End Region End Class End Namespace Yes, that was there too. Not in this form but something similar.

Index Prelude Part 1 Part 2 Part 3 Conclusion There is this very interesting blogpost by Robert Johnson on his “I am not Myself Blog“. I will be testing this on my servers first thing in the morning. Please keep watching this channel. So I have tested it: Part 1. Don’t forget to read the conclusion (before you start flaming). Need help with VB.Net? Come and ask a question in our VB.Net Forum