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 3nHibernate performance against Stored procedures part 1 »
5
comments

nHibernate performance against Stored procedures part 2

by chrissie1 on Jul 24, 2008 in categories Microsoft Technologies
Rate Post:
submit to reddit Digg!FacebookDotnetkicks

Index

Prelude
Part 1
Part 2
Part 3
Conclusion




Yes The stored procedures came to town.

Here is the first one.

tsql
Line number Off | Hide | Select all
  1. CREATE PROC spo_getMspCoordinates
  2. AS
  3. SELECT TOP 1000000 id, a, WaveLenghtinnm
  4. FROM dbo.tbl_MSPCoordinate
  5.  
  6. 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.

tsql
Line number Off | Hide | Select all
  1. CREATE PROC spo_getMspCoordinatesTop
  2. @TOP VARCHAR(30)
  3. AS
  4. DECLARE @SQL VARCHAR(1000)
  5.  
  6. SELECT @SQL = ‘SELECT TOP ‘ + @TOP + ‘ id, a, WaveLenghtinnm’
  7. SELECT @SQL = @SQL + ‘ FROM dbo.tbl_MSPCoordinate’
  8.  
  9. EXEC ( @SQL)
  10.  
  11. GO

And here are the 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_SQLClientProcedures_1000000()
  17.             Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclientprocedure")
  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_SQLClientProcedures_1000()
  25.             Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclientprocedure")
  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_SQLClientProcedures_1000_1000()
  33.             Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclientprocedure")
  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.     End Class
  41. End Namespace

And here is the implementation

vbnet
Line number Off | Hide | Select all
  1. Imports StructureMap
  2.  
  3. Namespace DAL.CRUD.SQLClientTransaction
  4.     <Pluggable("sqlclientprocedure")> _
  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()
  12.             c.Open()
  13.             s.Connection = c
  14.             s.CommandType = CommandType.StoredProcedure
  15.             s.CommandText = "spo_getMspCoordinates"
  16.             Dim r As Data.SqlClient.SqlDataReader
  17.             r = s.ExecuteReader
  18.             While r.Read
  19.                 _returnlist.Add(New Model.MspCoordinate(Convert.ToInt64(r.GetValue(0)), r.GetDecimal(1), r.GetDecimal(2)))
  20.             End While
  21.             s.Dispose()
  22.             r.Close()
  23.             c.Close()
  24.             c.Dispose()
  25.             Return _returnlist
  26.         End Function
  27.  
  28.         Public Function Selectall1(ByVal top As Integer) As System.Collections.Generic.IList(Of Model.MspCoordinate) Implements Interfaces.IMSPCoordinate.Selectall
  29.             Dim _returnlist As New List(Of Model.MspCoordinate)
  30.             Dim c As New Data.SqlClient.SqlConnection("Data Source=incctex;Initial Catalog=testtexdatabase;Integrated Security=SSPI;")
  31.             Dim s As New Data.SqlClient.SqlCommand()
  32.             c.Open()
  33.             s.Connection = c
  34.             s.CommandType = CommandType.StoredProcedure
  35.             s.CommandText = "spo_getMspCoordinatesTop"
  36.             s.Parameters.Add(New Data.SqlClient.SqlParameter("top", SqlDbType.VarChar, 30))
  37.             s.Parameters(0).Value = top.ToString
  38.             Dim r As Data.SqlClient.SqlDataReader
  39.             r = s.ExecuteReader
  40.             While r.Read
  41.                 _returnlist.Add(New Model.MspCoordinate(Convert.ToInt64(r.GetValue(0)), r.GetDecimal(1), r.GetDecimal(2)))
  42.             End While
  43.             s.Dispose()
  44.             r.Close()
  45.             c.Close()
  46.             c.Dispose()
  47.             Return _returnlist
  48.         End Function
  49.     End Class
  50. End Namespace

And here are the results.

Test Time
Select_MSP_SQLClientTransactions_10000007.53 s
Select_MSP_SQLClientTransactions_10000.04 s
Select_MSP_SQLClientTransactions_1000_100010.51 s

Yes the Dynamic sql is killing it. But not more then the “inline” sql.

Edit

With the new Stored procedure, without dynamic sql.

tsql
Line number Off | Hide | Select all
  1. ALTER PROC spo_getMspCoordinatesTop
  2. @TOP INT
  3. AS
  4. SET ROWCOUNT @TOP
  5. SELECT id, a, WaveLenghtinnm FROM dbo.tbl_MSPCoordinate

The numbers become this.

Test Time
Select_MSP_SQLClientTransactions_10000007.32 s
Select_MSP_SQLClientTransactions_10000.09 s
Select_MSP_SQLClientTransactions_1000_10009.39 s

Not much of an improvement, but we take what we can get.
So dynamic sql isn’t all that bad after all :>






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
1360 views
More Posts by chrissie1
submit to reddit Digg!FacebookDotnetkicks

Comments and Feedback

5 comments

Comment from: DoniG [Visitor]
Unless I'm missing something, you can avoid dynamic SQL entirely by using:

CREATE PROC spo_getMspCoordinatesTop
@TOP INT
AS

SELECT TOP (@TOP) id, a, WaveLenghtinnm FROM dbo.tbl_MSPCoordinate
24/07/08 @ 06:41
Comment from: chrissie1 [Member] Email
Apparently not, yu get this error

Server: Msg 170, Level 15, State 1, Procedure spo_getMspCoordinatesTop2, Line 4
Line 4: Incorrect syntax near '@TOP'.

Thats why I had to go the dynamic sql way.
24/07/08 @ 07:20
Comment from: SQLDenis [Member] Email
DoniG is right, however that is SQL Server 2005 syntax

Here is a way to do it with SQL server 2000 syntax

CREATE PROC spo_getMspCoordinatesTop
@TOP INT
AS

set rowcount @TOP
SELECT id, a, WaveLenghtinnm FROM dbo.tbl_MSPCoordinate

This also be found on (surprise) our wiki
http://wiki.lessthandot.com/index.php/Dynamic_TOP
24/07/08 @ 09:20
Comment from: chrissie1 [Member] Email
Oh cool I'll try that in the morning and add the results.
24/07/08 @ 12:40
Comment from: dario-g [Visitor] · http://dario-g.com
Dynamic sql use wrong method. See at spExecuteSql.
25/07/08 @ 08:22

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