Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Desktop Developer

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

Tools

Tag cloud

.net .net 3.5 .net 4.0 2003 access modifiers beginners book buildup c# excel f# linq microsoft reporting engine nhibernate nhprof nunit programming reflection reportviewer resharper rhino mocks structuremap threading unit testing vb.net vb.net. visual studio 2010 windows forms windows presentation foundation wpf

Authors

Main Categories

Search

XML Feeds

What is RSS?

Google Ads

« nHibernate performance against Stored procedures part 2nHibernate performance against Stored procedures »
2
comments

nHibernate performance against Stored procedures part 1

by chrissie1 on Jul 23, 2008 in categories Microsoft Technologies, VB.NET
Rate Post:
submit to reddit Digg!FacebookDotnetkicks

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.

tsql
Line number Off | Hide | Select all
  1. IF exists (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N‘[dbo].[tbl_MSPCoordinate]’) and OBJECTPROPERTY(id, N‘IsUserTable’) = 1)
  2. DROP TABLE [dbo].[tbl_MSPCoordinate]
  3. GO
  4.  
  5. CREATE TABLE [dbo].[tbl_MSPCoordinate] (
  6.     [Id] [BIGINT] IDENTITY (1, 1) NOT NULL ,
  7.     [MSP_id] [VARCHAR] (36) COLLATE Latin1_General_CI_AS NULL ,
  8.     [WaveLenghtinnm] [DECIMAL](19, 5) NOT NULL ,
  9.     [A] [DECIMAL](19, 5) NOT NULL ,
  10.     [Added_By] [VARCHAR] (20) COLLATE Latin1_General_CI_AS NULL ,
  11.     [Added_On] [DATETIME] NULL
  12. ) ON [PRIMARY]
  13. 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
Line number Off | Hide | Select all
  1. Imports System.Text
  2. Imports NHibernate.Mapping.Attributes
  3.  
  4. Namespace Model
  5.     ‘'’ <summary>
  6.     ‘'’
  7.     ‘'’ </summary>
  8.     ‘'’ <remarks></remarks>
  9.     <CLSCompliant(True)> _
  10.         <[Class](0, table:="tbl_MSPCoordinate", lazy:=False)> _
  11. Public Class MspCoordinate
  12.        
  13. #Region " Private members "
  14.  
  15.         ‘'’ <summary>
  16.         ‘'’ A local variable called _id of type String
  17.         ‘'’ </summary>
  18.         ‘'’ <remarks>Has Property Id</remarks>
  19.         Private _id As Int64
  20.  
  21.         ‘'’ <summary>
  22.         ‘'’ A local variable called _a of type Decimal
  23.         ‘'’ </summary>
  24.         ‘'’ <remarks>Has Property A</remarks>
  25.         Private _a As Decimal
  26.  
  27.         ‘'’ <summary>
  28.         ‘'’ A local variable called _waveLenghtinnm of type Decimal
  29.         ‘'’ </summary>
  30.         ‘'’ <remarks>Has Property WaveLenghtinnm</remarks>
  31.         Private _waveLenghtinnm As Decimal
  32.  
  33. #End Region
  34.  
  35. #Region " Constructors "
  36.  
  37.         ‘'’ <summary>
  38.         ‘'’ Default empty constructor
  39.         ‘'’ </summary>
  40.         ‘'’ <remarks></remarks>
  41.         Public Sub New()
  42.             Me.New(0, 0, 0)
  43.         End Sub
  44.  
  45.         ‘'’ <summary>
  46.         ‘'’ Constructor with all the fields
  47.         ‘'’ </summary>
  48.         ‘'’ <param name="A">DataType = Decimal</param>
  49.         ‘'’ <param name="WaveLenghtinnm">DataType = Decimal</param>
  50.         ‘'’ <remarks></remarks>
  51.         Public Sub New(ByVal Id As Int64, ByVal A As Decimal, ByVal WaveLenghtinnm As Decimal)
  52.             Me.Id = Id
  53.             Me.A = A
  54.             Me.WaveLenghtinnm = WaveLenghtinnm
  55.         End Sub
  56.  
  57. #End Region
  58.  
  59. #Region " Public properties "
  60.  
  61.         ‘'’ <summary>
  62.         ‘'’ This is the Id property.
  63.         ‘'’ </summary>
  64.         ‘'’ <value>String</value>
  65.         ‘'’ <returns>String</returns>
  66.         ‘'’ <remarks></remarks>
  67.         <Id(0, Name:="Id") _
  68.             , Generator(1, Class:="native")> _
  69.         Public Overridable Property Id() As Int64
  70.             Get
  71.                 Return _id
  72.             End Get
  73.             Set(ByVal Value As Int64)
  74.                 _id = Value
  75.             End Set
  76.         End Property
  77.  
  78.         ‘'’ <summary>
  79.         ‘'’ This is the A property.
  80.         ‘'’ </summary>
  81.         ‘'’ <value>Decimal</value>
  82.         ‘'’ <returns>Decimal</returns>
  83.         ‘'’ <remarks></remarks>
  84.         <[Property]()> _
  85.         Public Overridable Property A() As Decimal
  86.             Get
  87.                 Return _a
  88.             End Get
  89.             Set(ByVal Value As Decimal)
  90.                 _a = Value
  91.             End Set
  92.         End Property
  93.  
  94.         ‘'’ <summary>
  95.         ‘'’ This is the WaveLenghtinnm property.
  96.         ‘'’ </summary>
  97.         ‘'’ <value>Decimal</value>
  98.         ‘'’ <returns>Decimal</returns>
  99.         ‘'’ <remarks></remarks>
  100.         <[Property]()> _
  101.         Public Overridable Property WaveLenghtinnm() As Decimal
  102.             Get
  103.                 Return _waveLenghtinnm
  104.             End Get
  105.             Set(ByVal Value As Decimal)
  106.                 _waveLenghtinnm = Value
  107.             End Set
  108.         End Property
  109.  
  110.         ‘'’ <summary>
  111.         ‘'’
  112.         ‘'’ </summary>
  113.         ‘'’ <value></value>
  114.         ‘'’ <returns></returns>
  115.         ‘'’ <remarks></remarks>
  116.         Public Overridable ReadOnly Property A_absorption() As Decimal
  117.             Get
  118.                 If _a = 0 Then
  119.                     Return 0
  120.                 Else
  121.                     If Math.Log10(_a / 100) * -1 > Decimal.MaxValue Then
  122.                         Return Decimal.MaxValue
  123.                     ElseIf Math.Log10(_a / 100) * -1 < Decimal.MinValue Then
  124.                         Return Decimal.MinValue
  125.                     End If
  126.                 End If
  127.                 Return Convert.ToDecimal(Math.Log10(_a / 100) * -1)
  128.             End Get
  129.         End Property
  130.  
  131. #End Region
  132.  
  133. #Region " Overrides ToString "
  134.  
  135.         ‘'’ <summary>
  136.         ‘'’ Overridden ToString method for this class
  137.         ‘'’ </summary>
  138.         ‘'’ <returns>String</returns>
  139.         ‘'’ <remarks>The String value of this class represented by the ToStrings of all it’s private members</remarks>
  140.         Public Overrides Function ToString() As String
  141.             Dim ReturnString As New StringBuilder()
  142.             ReturnString.Append("[Id: " & _id.ToString() & "]")
  143.             ReturnString.Append(" - [A: " & _a.ToString() & "]")
  144.             ReturnString.Append(" - [WaveLenghtinnm: " & _waveLenghtinnm.ToString() & "]")
  145.             Return ReturnString.ToString()
  146.         End Function
  147.  
  148. #End Region
  149.  
  150. #Region " Overrides Equals "
  151.  
  152.         ‘'’ <summary>
  153.         ‘'’ Overridden Equals method for this class
  154.         ‘'’ </summary>
  155.         ‘'’ <returns>Boolean</returns>
  156.         ‘'’ <remarks>The Boolean value indictaing if this is equal to the other class</remarks>
  157.         Public Overrides Function Equals(ByVal obj As Object) As Boolean
  158.             If obj IsNot Nothing Then
  159.                 If obj.GetType Is Me.GetType Then
  160.                     Return Me._waveLenghtinnm.Equals(CType(obj, MspCoordinate)._waveLenghtinnm)
  161.                 Else
  162.                     Return False
  163.                 End If
  164.             Else
  165.                 Return False
  166.             End If
  167.         End Function
  168.  
  169. #End Region
  170.  
  171. #Region " Overrides CompareTo "
  172.  
  173.         ‘'’ <summary>
  174.         ‘'’ Overridden CompareTo method for this class
  175.         ‘'’ </summary>
  176.         ‘'’ <returns>Integer</returns>
  177.         ‘'’ <remarks></remarks>
  178.         Public Function CompareTo(ByVal obj As Object) As Integer
  179.             If obj IsNot Nothing Then
  180.                 If obj.GetType Is Me.GetType Then
  181.                     Return Me._waveLenghtinnm.CompareTo(CType(obj, MspCoordinate)._waveLenghtinnm)
  182.                 Else
  183.                     Return -1
  184.                 End If
  185.             Else
  186.                 Return -1
  187.             End If
  188.         End Function
  189.  
  190. #End Region
  191.  
  192.     End Class
  193. 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
Line number Off | Hide | Select all
  1. Imports StructureMap
  2.  
  3. Namespace DAL.Interfaces
  4.     <PluginFamily("nhibernate", issingleton:=True)> _
  5.     Public Interface IMSPCoordinate
  6.         Function Selectall() As IList(Of Model.MspCoordinate)
  7.         Function Selectall(ByVal top As Integer) As IList(Of Model.MspCoordinate)
  8.     End Interface
  9. End Namespace

Look at the structuremap beauty, yes, I still use the attributes.

And the first tests

vbnet
Line number Off | Hide | Select all
  1. Imports NUnit.Framework
  2. Imports StructureMap
  3.  
  4. Namespace Test
  5.     <TestFixture()> _
  6.     Public Class TestSpeedNHibernate
  7.  
  8.         <SetUp()> _
  9.         Public Sub Setup()
  10.             StructureMapConfiguration.UseDefaultStructureMapConfigFile = False
  11.             StructureMapConfiguration.ScanAssemblies.IncludeTheCallingAssembly()
  12.  
  13.         End Sub
  14.  
  15.         <Test()> _
  16.         Public Sub Select_MSP_SQLClient_1000000_2()
  17.             Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclient")
  18.             Assert.IsNotNull(_crud.Selectall)
  19.             Assert.Greater(_crud.Selectall.Count, 0)
  20.             Assert.AreEqual(1000000, _crud.Selectall.Count)
  21.         End Sub
  22.  
  23.         <Test()> _
  24.         Public Sub Select_MSP_SQLClient_1000()
  25.             Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclient")
  26.             Assert.IsNotNull(_crud.Selectall(1000))
  27.             Assert.Greater(_crud.Selectall(1000).Count, 0)
  28.             Assert.AreEqual(1000, _crud.Selectall(1000).Count)
  29.         End Sub
  30.  
  31.         <Test()> _
  32.         Public Sub Select_MSP_SQLClient_1000_1000()
  33.             Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclient")
  34.             For i As Integer = 0 To 999
  35.                 Assert.IsNotNull(_crud.Selectall(1000))
  36.                 Assert.Greater(_crud.Selectall(1000).Count, 0)
  37.                 Assert.AreEqual(1000, _crud.Selectall(1000).Count)
  38.             Next
  39.         End Sub
  40.  
  41.     End Class
  42. End Namespace

And then the first implementation of this.

vbnet
Line number Off | Hide | Select all
  1. Imports StructureMap
  2.  
  3. Namespace DAL.CRUD.SQLClient
  4.     <Pluggable("sqlclient")> _
  5.     Public Class MSPCoordinate
  6.         Implements Interfaces.IMSPCoordinate
  7.  
  8.         Public Function Selectall() As System.Collections.Generic.IList(Of Model.MspCoordinate) Implements Interfaces.IMSPCoordinate.Selectall
  9.             Dim _returnlist As New List(Of Model.MspCoordinate)
  10.             Dim c As New Data.SqlClient.SqlConnection("Data Source=incctex;Initial Catalog=testtexdatabase;Integrated Security=SSPI;")
  11.             Dim s As New Data.SqlClient.SqlCommand("select top 1000000 Id, A, wavelenghtinnm from tbl_mspcoordinate", c)
  12.             c.Open()
  13.             Dim r As Data.SqlClient.SqlDataReader
  14.             r = s.ExecuteReader
  15.             While r.Read
  16.                 _returnlist.Add(New Model.MspCoordinate(Convert.ToInt64(r.GetValue(0)), r.GetDecimal(1), r.GetDecimal(2)))
  17.             End While
  18.             s.Dispose()
  19.             r.Close()
  20.             c.Close()
  21.             c.Dispose()
  22.             Return _returnlist
  23.         End Function
  24.  
  25.         Public Function Selectall1(ByVal top As Integer) As System.Collections.Generic.IList(Of Model.MspCoordinate) Implements Interfaces.IMSPCoordinate.Selectall
  26.             Dim _returnlist As New List(Of Model.MspCoordinate)
  27.             Dim c As New Data.SqlClient.SqlConnection("Data Source=incctex;Initial Catalog=testtexdatabase;Integrated Security=SSPI;")
  28.             Dim s As New Data.SqlClient.SqlCommand("select top " & top.ToString & " Id, A, wavelenghtinnm from tbl_mspcoordinate", c)
  29.             c.Open()
  30.             Dim r As Data.SqlClient.SqlDataReader
  31.             r = s.ExecuteReader
  32.             While r.Read
  33.                 _returnlist.Add(New Model.MspCoordinate(Convert.ToInt64(r.GetValue(0)), r.GetDecimal(1), r.GetDecimal(2)))
  34.             End While
  35.             s.Dispose()
  36.             r.Close()
  37.             c.Close()
  38.             c.Dispose()
  39.             Return _returnlist
  40.         End Function
  41.     End Class
  42. End Namespace

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

Test Time
Select_MSP_SQLClient_10000009.00 s
Select_MSP_SQLClient_1000000_28.06 s
Select_MSP_SQLClient_10000.01 s
Select_MSP_SQLClient_1000_100010.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

About the Author

User bio imageChristiaan is a forensic technician who programs on the side, although my function description says that I do IT-things for 90% of the time . I'm an avid VB.NET fan and I use lots of the ALT.Net techniques, like unit-testing, nhibernate, logging, IoC, ...
Social SitingsTwitterLinkedInHomePageLTD RSS Feed
2490 views
More Posts by chrissie1
submit to reddit Digg!FacebookDotnetkicks

Comments and Feedback

2 comments

Comment from: Chad Myers [Visitor] · http://chadmyers.lostechies.com
***--
Can you post your population script for the database so I can run this code against my own system and play with the code a little?
25/07/08 @ 09:27
Comment from: chrissie1 [Member] Email
I'll do that as soon as possible.
25/07/08 @ 11:00

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)

Powered by B2Evolution
Valid XHTML | Valid CSS