Index

Prelude

Part 1

Part 2

Part 3

Conclusion

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
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <remarks></remarks>
    <CLSCompliant(True)> _
        <[Class](0, table:="tbl_MSPCoordinate", lazy:=False)> _
Public Class MspCoordinate
        
#Region " Private members "

        ''' <summary>
        ''' A local variable called _id of type String
        ''' </summary>
        ''' <remarks>Has Property Id</remarks>
        Private _id As Int64

        ''' <summary>
        ''' A local variable called _a of type Decimal
        ''' </summary>
        ''' <remarks>Has Property A</remarks>
        Private _a As Decimal

        ''' <summary>
        ''' A local variable called _waveLenghtinnm of type Decimal
        ''' </summary>
        ''' <remarks>Has Property WaveLenghtinnm</remarks>
        Private _waveLenghtinnm As Decimal

#End Region

#Region " Constructors "

        ''' <summary>
        ''' Default empty constructor
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub New()
            Me.New(0, 0, 0)
        End Sub

        ''' <summary>
        ''' Constructor with all the fields
        ''' </summary>
        ''' <param name="A">DataType = Decimal</param>
        ''' <param name="WaveLenghtinnm">DataType = Decimal</param>
        ''' <remarks></remarks>
        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 "

        ''' <summary>
        ''' This is the Id property.
        ''' </summary>
        ''' <value>String</value>
        ''' <returns>String</returns>
        ''' <remarks></remarks>
        <Id(0, Name:="Id") _
            , Generator(1, Class:="native")> _
        Public Overridable Property Id() As Int64
            Get
                Return _id
            End Get
            Set(ByVal Value As Int64)
                _id = Value
            End Set
        End Property

        ''' <summary>
        ''' This is the A property.
        ''' </summary>
        ''' <value>Decimal</value>
        ''' <returns>Decimal</returns>
        ''' <remarks></remarks>
        <[Property]()> _
        Public Overridable Property A() As Decimal
            Get
                Return _a
            End Get
            Set(ByVal Value As Decimal)
                _a = Value
            End Set
        End Property

        ''' <summary>
        ''' This is the WaveLenghtinnm property.
        ''' </summary>
        ''' <value>Decimal</value>
        ''' <returns>Decimal</returns>
        ''' <remarks></remarks>
        <[Property]()> _
        Public Overridable Property WaveLenghtinnm() As Decimal
            Get
                Return _waveLenghtinnm
            End Get
            Set(ByVal Value As Decimal)
                _waveLenghtinnm = Value
            End Set
        End Property

        ''' <summary>
        ''' 
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overridable ReadOnly Property A_absorption() As Decimal
            Get
                If _a = 0 Then
                    Return 0
                Else
                    If Math.Log10(_a / 100) * -1 > Decimal.MaxValue Then
                        Return Decimal.MaxValue
                    ElseIf Math.Log10(_a / 100) * -1 < 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 "

        ''' <summary>
        ''' Overridden ToString method for this class
        ''' </summary>
        ''' <returns>String</returns>
        ''' <remarks>The String value of this class represented by the ToStrings of all it's private members</remarks>
        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 "

        ''' <summary>
        ''' Overridden Equals method for this class
        ''' </summary>
        ''' <returns>Boolean</returns>
        ''' <remarks>The Boolean value indictaing if this is equal to the other class</remarks>
        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 "

        ''' <summary>
        ''' Overridden CompareTo method for this class
        ''' </summary>
        ''' <returns>Integer</returns>
        ''' <remarks></remarks>
        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.

Then I had to create a Dal to make life easy.

First the interface.

```vbnet Imports StructureMap

Namespace DAL.Interfaces <PluginFamily(“nhibernate”, issingleton:=True)> _ Public Interface IMSPCoordinate Function Selectall() As IList(Of Model.MspCoordinate) Function Selectall(ByVal top As Integer) As IList(Of Model.MspCoordinate) End Interface End Namespace``` Look at the structuremap beauty, yes, I still use the attributes.

And the first tests

```vbnet Imports NUnit.Framework Imports StructureMap

Namespace Test <TestFixture()> _ Public Class TestSpeedNHibernate

    &lt;SetUp()&gt; _
    Public Sub Setup()
        StructureMapConfiguration.UseDefaultStructureMapConfigFile = False
        StructureMapConfiguration.ScanAssemblies.IncludeTheCallingAssembly()

    End Sub

    &lt;Test()&gt; _
    Public Sub Select_MSP_SQLClient_1000000_2()
        Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclient")
        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_SQLClient_1000()
        Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclient")
        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_SQLClient_1000_1000()
        Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclient")
        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

End Class

End Namespace``` And then the first implementation of this.

```vbnet Imports StructureMap

Namespace DAL.CRUD.SQLClient <Pluggable(“sqlclient”)> _ Public Class MSPCoordinate Implements Interfaces.IMSPCoordinate

    Public Function Selectall() As System.Collections.Generic.IList(Of Model.MspCoordinate) Implements Interfaces.IMSPCoordinate.Selectall
        Dim _returnlist As New List(Of Model.MspCoordinate)
        Dim c As New Data.SqlClient.SqlConnection("Data Source=incctex;Initial Catalog=testtexdatabase;Integrated Security=SSPI;")
        Dim s As New Data.SqlClient.SqlCommand("select top 1000000 Id, A, wavelenghtinnm from tbl_mspcoordinate", c)
        c.Open()
        Dim r As Data.SqlClient.SqlDataReader
        r = s.ExecuteReader
        While r.Read
            _returnlist.Add(New Model.MspCoordinate(Convert.ToInt64(r.GetValue(0)), r.GetDecimal(1), r.GetDecimal(2)))
        End While
        s.Dispose()
        r.Close()
        c.Close()
        c.Dispose()
        Return _returnlist
    End Function

    Public Function Selectall1(ByVal top As Integer) As System.Collections.Generic.IList(Of Model.MspCoordinate) Implements Interfaces.IMSPCoordinate.Selectall
        Dim _returnlist As New List(Of Model.MspCoordinate)
        Dim c As New Data.SqlClient.SqlConnection("Data Source=incctex;Initial Catalog=testtexdatabase;Integrated Security=SSPI;")
        Dim s As New Data.SqlClient.SqlCommand("select top " & top.ToString & " Id, A, wavelenghtinnm from tbl_mspcoordinate", c)
        c.Open()
        Dim r As Data.SqlClient.SqlDataReader
        r = s.ExecuteReader
        While r.Read
            _returnlist.Add(New Model.MspCoordinate(Convert.ToInt64(r.GetValue(0)), r.GetDecimal(1), r.GetDecimal(2)))
        End While
        s.Dispose()
        r.Close()
        c.Close()
        c.Dispose()
        Return _returnlist
    End Function
End Class

End Namespace``` And these are the results according to resharper unit test thing.

Test Time
Select_MSP_SQLClient_1000000 9.00 s
Select_MSP_SQLClient_1000000_2 8.06 s
Select_MSP_SQLClient_1000 0.01 s
Select_MSP_SQLClient_1000_1000 10.36 s

Looks reasonable to me. It isn’t the fastest server in the world after all.

Next installment the stored procedure route and a little snag along the way.

Need help with VB.Net? Come and ask a question in our VB.Net Forum