Index

Prelude

Part 1

Part 2

Part 3

Conclusion


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.

CREATE PROC spo_getMspCoordinatesTop
@top VARCHAR(30)
AS
Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT TOP ' + @top + ' id, a, WaveLenghtinnm' 
SELECT @SQL = @SQL + ' FROM dbo.tbl_MSPCoordinate'

Exec ( @SQL)

GO

And here are the 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_SQLClientProcedures_1000000()
        Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclientprocedure")
        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_SQLClientProcedures_1000()
        Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclientprocedure")
        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_SQLClientProcedures_1000_1000()
        Dim _crud As DAL.Interfaces.IMSPCoordinate = StructureMap.ObjectFactory.GetNamedInstance(Of DAL.Interfaces.IMSPCoordinate)("sqlclientprocedure")
        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 here is the implementation

```vbnet Imports StructureMap

Namespace DAL.CRUD.SQLClientTransaction <Pluggable(“sqlclientprocedure”)> _ 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()
        c.Open()
        s.Connection = c
        s.CommandType = CommandType.StoredProcedure
        s.CommandText = "spo_getMspCoordinates"
        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()
        c.Open()
        s.Connection = c
        s.CommandType = CommandType.StoredProcedure
        s.CommandText = "spo_getMspCoordinatesTop"
        s.Parameters.Add(New Data.SqlClient.SqlParameter("top", SqlDbType.VarChar, 30))
        s.Parameters(0).Value = top.ToString
        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 here are the results.

Test Time
Select_MSP_SQLClientTransactions_1000000 7.53 s
Select_MSP_SQLClientTransactions_1000 0.04 s
Select_MSP_SQLClientTransactions_1000_1000 10.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 ALTER PROC spo_getMspCoordinatesTop @TOP INT AS SET ROWCOUNT @TOP SELECT id, a, WaveLenghtinnm FROM dbo.tbl_MSPCoordinate The numbers become this.

Test Time
Select_MSP_SQLClientTransactions_1000000 7.32 s
Select_MSP_SQLClientTransactions_1000 0.09 s
Select_MSP_SQLClientTransactions_1000_1000 9.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