Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Your profile

Search

Main Categories

Categories

July 2009
Mon Tue Wed Thu Fri Sat Sun
 << <   > >>
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

Tools

XML Feeds

What is RSS?

Tag cloud

.net .net 3.5 .net 4.0 2003 aaa syntax access modifiers beginners book buildup c# chess dependency injection drawstring enablevisualstyles excel f# generate hack ienumberable linq log4net log4postsharp ms research nhibernate nhprof nunit office order poster postsharp print programming reflection resharper rhino mocks setterinjection structuremap stylecop template threading tortoisesvn unit testing vb.net vb10 vista visual studio 2010 windows 7 windows forms windows presentation foundation wpf

Authors

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

Desktop Developer

The Desktop Developers Journal

nHibernate performance against Stored procedures part 1

by chrissie1


Permalink 23 Jul 2008 08:26 , Categories: Microsoft Technologies, VB.NET

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.

tsqlLine number On/Off | Show/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.

vbnetLine number On/Off | Show/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.

vbnetLine number On/Off | Show/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

vbnetLine number On/Off | Show/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.

vbnetLine number On/Off | Show/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

2 comments » • Send a trackback » 2117 views

Trackback address for this post

Trackback URL (right click and copy shortcut/link location)

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.
PoorExcellent
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)

Tools


RssFeeds
Donate

Blogs


Become a blogger

Statistics


Top 10 rated users for the last 7 days

User Ratings
Emtucifor23
AlexCuse12
chrissie110
gmmastros7
kaht6
traingamer6
Niikola4
LNBruno4
Remou3
SQLDenis3

Links



With Thanks To...


Powered by B2Evolution
Valid XHTML | Valid CSS