Index

Prelude
Part 1
Part 2
Part 3
Conclusion


Yes The stored procedures came to town.

Here is the first one.

T-SQL
1
2
3
4
5
6
CREATE PROC spo_getMspCoordinates
AS
SELECT TOP 1000000 id, a, WaveLenghtinnm
FROM dbo.tbl_MSPCoordinate
 
GO
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.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
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
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.

vb.net
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
32
33
34
35
36
37
38
39
40
41
Imports NUnit.Framework
Imports StructureMap
 
Namespace Test
    <TestFixture()> _
    Public Class TestSpeedNHibernate
 
        <SetUp()> _
        Public Sub Setup()
            StructureMapConfiguration.UseDefaultStructureMapConfigFile = False
            StructureMapConfiguration.ScanAssemblies.IncludeTheCallingAssembly()
 
        End Sub
 
        <Test()> _
        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
 
        <Test()> _
        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
 
        <Test()> _
        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
Imports NUnit.Framework
Imports StructureMap

Namespace Test
    <TestFixture()> _
    Public Class TestSpeedNHibernate

        <SetUp()> _
        Public Sub Setup()
            StructureMapConfiguration.UseDefaultStructureMapConfigFile = False
            StructureMapConfiguration.ScanAssemblies.IncludeTheCallingAssembly()

        End Sub

        <Test()> _
        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

        <Test()> _
        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

        <Test()> _
        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

vb.net
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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
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.

T-SQL
1
2
3
4
5
ALTER PROC spo_getMspCoordinatesTop
@TOP INT
AS
SET ROWCOUNT @TOP
SELECT id, a, WaveLenghtinnm FROM dbo.tbl_MSPCoordinate
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