Speed is not everything unless you have lots of records-VBForums
Results 1 to 18 of 18

Thread: Speed is not everything unless you have lots of records

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Apr 2010
    Posts
    248

    Speed is not everything unless you have lots of records

    I have Just spent a long time converting one of my very old VB programms to use SQL server, it previously used SQLCE.
    Now when i ran the program, it seem to be a lot slower using the SQL server than the SQL compact.

    Upon carrying out some tests, With SQL Sever it is taking 37 seconds to insert 10000 records, compared with 3 seconds with SQLCE.

    Is this to be expected ?
    Last edited by Signalman; May 5th, 2017 at 06:56 PM.

  2. #2
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,168

    Re: Speed is not everything unless you have lots of records

    Are you using SqlBulkCopy?

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Apr 2010
    Posts
    248

    Re: Speed is not everything unless you have lots of records

    No i am not using SQLBulkCopy which may (or may not) be quicker.

    However what i am asking is should I expect a poorer performance (by a factor of 10) from a SQLserver, compared with a SQOLce ?

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,103

    Re: Speed is not everything unless you have lots of records

    Thread moved to the 'Database Development' forum


    I wouldn't expect that speed difference.

    I haven't used SQLCE enough to know its relative speed, but a local SQL Server tends to be slightly slower (10%?) than a local .mdb file.


    My guess is that you have the databases set up differently (perhaps extra indexes), and/or that you are using code that is less than ideal.


    Note that SqlBulkCopy should provide a dramatic speed boost, but even without it I wouldn't expect the speed difference you had.

  5. #5
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    703

    Re: Speed is not everything unless you have lots of records

    Is your SQL Server instance local or over a network?

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    30,846

    Re: Speed is not everything unless you have lots of records

    There's a number of things at play: speed of the network, traffic on the network, speed of the server being used, the load on that server, insertion method, indexes... and one or all of these could be causing the issue you're seeing.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,412

    Re: Speed is not everything unless you have lots of records

    You should benchmark this yourself - I for one would sure be interested in your results.

    Create a table in SQL CE with NO INDEXES - test 10000 inserts.

    Create a table in MS SQL with NO INDEXES - test 10000 inserts.

    What is the results?

    And show the code. Is it the same in that you were using "hardcoded" INSERT statements in a loop in both?

    I've used SQL CE on mobile devices - never saw the speed be anything to write home about. It sure is a simple version of SQL though - so maybe running on a real PC it performs differently.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Apr 2010
    Posts
    248

    Re: Speed is not everything unless you have lots of records

    Ok, the test has been performed. using SQLCe it gives a result of 13 seconds to insert 10000 recs. If a SQL server (SQLEXPRESS) running locally on the same machine (but not the very simple SqLocal) it gave a result of 40 seconds to insert 10000 recs.

    The code for the SQLCE is :-

    Code:
        Function testimport()
            MsgBox("ready to start")
            Dim myConnection As SqlCeEngine = New SqlCeEngine(My.Settings.CurrentDatasource)
            Using conn As New SqlCeConnection(My.Settings.CurrentDatasource)
                Dim cmd As SqlCeCommand = conn.CreateCommand
                conn.Open()
                For a = 1 To 10000
    
                    cmd.CommandText = "INSERT INTO testfile VALUES (@a," + _
                                                                      "@b," + _
                                                                      "@c," + _
                                                                      "@d," + _
                                                                      "@e," + _
                                                                      "@f," + _
                                                                      "@g," + _
                                                                      "@h," + _
                                                                      "@i," + _
                                                                      "@j," + _
                                                                      "@k," + _
                                                                      "@l," + _
                                                                      "@m," + _
                                                                      "@n," + _
                                                                      "@o," + _
                                                                      "@p," + _
                                                                      "@q," + _
                                                                      "@r," + _
                                                                      "@s," + _
                                                                      "@t," + _
                                                                      "@u," + _
                                                                      "@v," + _
                                                                      "@w," + _
                                                                      "@x," + _
                                                                      "@y," + _
                                                                      "@z," + _
                                                                      "@aa," + _
                                                                      "@ab," + _
                                                                      "@ac," + _
                                                                      "@ad," + _
                                                                      "@ae," + _
                                                                      "@af," + _
                                                                      "@ag," + _
                                                                      "@ah," + _
                                                                      "@ai," + _
                                                                      "@aj," + _
                                                                      "@ak," + _
                                                                      "@al," + _
                                                                      "@am," + _
                                                                      "@an," + _
                                                                      "@ao," + _
                                                                      "@ap," + _
                                                                      "@aq," + _
                                                                      "@ar," + _
                                                                      "@au);"
    
    
                    cmd.Parameters.Clear()
                    cmd.Parameters.Add("@a", System.Data.SqlDbType.Int).Value = a
                    cmd.Parameters.Add("@b", System.Data.SqlDbType.Int).Value = a
                    cmd.Parameters.Add("@c", System.Data.SqlDbType.NVarChar).Value = ""
                    cmd.Parameters.Add("@d", System.Data.SqlDbType.NVarChar).Value = ""
                    cmd.Parameters.Add("@e", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@f", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@g", System.Data.SqlDbType.Int).Value = 0
                    cmd.Parameters.Add("@h", System.Data.SqlDbType.Int).Value = 0
                    cmd.Parameters.Add("@i", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@j", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@k", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@l", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@m", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@n", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@o", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@p", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@q", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@r", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@s", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@t", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@u", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@v", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@w", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@x", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@y", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@z", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@aa", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ab", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ac", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ad", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ae", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@af", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ag", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ah", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ai", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@aj", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ak", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@al", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@am", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@an", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ao", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ap", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@aq", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@ar", System.Data.SqlDbType.NVarChar).Value = " "
                    cmd.Parameters.Add("@au", System.Data.SqlDbType.NVarChar).Value = " "
                    Try
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        MsgBox("Error Inserting Record" & vbCrLf & ex.Message & vbCrLf & cmd.CommandText)
    
    
                    End Try
    
                Next
                conn.Close()
                    end using
            MsgBox("Done")
    
        End Function
    and on the SQLServer the code is :-

    Code:
         MsgBox("ready to start")
            Dim SQL As New SQLControlClass()
            SQL.ExecuteOpen()
            If SQL.HasException(True) = True Then Exit Sub ' check exception
    
            For a = 1 To 10000
    
                SQL.Params.Clear()
                SQL.AddParam("@a", a)
                SQL.AddParam("@b", a)
                SQL.AddParam("@c", " ")
                SQL.AddParam("@d", " ")
                SQL.AddParam("@e", " ")
                SQL.AddParam("@f", " ")
                SQL.AddParam("@g", 0)
                SQL.AddParam("@h", 0)
                SQL.AddParam("@i", " ")
                SQL.AddParam("@j", " ")
                SQL.AddParam("@k", " ")
                SQL.AddParam("@l", " ")
                SQL.AddParam("@m", " ")
                SQL.AddParam("@n", " ")
                SQL.AddParam("@o", " ")
                SQL.AddParam("@p", " ")
                SQL.AddParam("@q", " ")
                SQL.AddParam("@r", " ")
                SQL.AddParam("@s", " ")
                SQL.AddParam("@t", " ")
                SQL.AddParam("@u", " ")
                SQL.AddParam("@v", " ")
                SQL.AddParam("@w", " ")
                SQL.AddParam("@x", " ")
                SQL.AddParam("@y", " ")
                SQL.AddParam("@z", " ")
                SQL.AddParam("@aa", " ")
                SQL.AddParam("@ab", " ")
                SQL.AddParam("@ac", " ")
                SQL.AddParam("@ad", " ")
                SQL.AddParam("@ae", " ")
                SQL.AddParam("@af", " ")
                SQL.AddParam("@ag", " ")
                SQL.AddParam("@ah", " ")
                SQL.AddParam("@ai", " ")
                SQL.AddParam("@aj", " ")
                SQL.AddParam("@ak", " ")
                SQL.AddParam("@al", " ")
                SQL.AddParam("@am", " ")
                SQL.AddParam("@an", " ")
                SQL.AddParam("@ao", " ")
                SQL.AddParam("@ap", " ")
                SQL.AddParam("@aq", " ")
                SQL.AddParam("@ar", " ")
                SQL.AddParam("@au", " ")
    
    
    
    
    
    
    
                SQL.ExecuteNonQueryNoOpenOrClose("INSERT INTO testfile VALUES (@a," + _
                                                           "@b," + _
                                                           "@c," + _
                                                           "@d," + _
                                                           "@e," + _
                                                           "@f," + _
                                                           "@g," + _
                                                           "@h," + _
                                                           "@i," + _
                                                           "@j," + _
                                                           "@k," + _
                                                           "@l," + _
                                                           "@m," + _
                                                           "@n," + _
                                                           "@o," + _
                                                           "@p," + _
                                                           "@q," + _
                                                           "@r," + _
                                                           "@s," + _
                                                           "@t," + _
                                                           "@u," + _
                                                           "@v," + _
                                                           "@w," + _
                                                           "@x," + _
                                                           "@y," + _
                                                           "@z," + _
                                                           "@aa," + _
                                                           "@ab," + _
                                                           "@ac," + _
                                                           "@ad," + _
                                                           "@ae," + _
                                                           "@af," + _
                                                           "@ag," + _
                                                           "@ah," + _
                                                           "@ai," + _
                                                           "@aj," + _
                                                           "@ak," + _
                                                           "@al," + _
                                                           "@am," + _
                                                           "@an," + _
                                                           "@ao," + _
                                                           "@ap," + _
                                                           "@aq," + _
                                                           "@ar," + _
                                                           "@au);")
    
    
                If SQL.HasException(True) = True Then Exit Sub ' check exception
            Next
    
            SQL.ExecuteClose()

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Apr 2010
    Posts
    248

    Re: Speed is not everything unless you have lots of records

    part 2 as it is to long for 1 post

    with the SQLControl class as :-

    Code:
    Imports System.Data.SqlClient
    
    Public Class SQLControlClass
        Private DBCon As New SqlConnection("Server=" & My.Settings.SQLSrverName & ";Database=" & My.Settings.SQLDbName & ";Trusted_Connection=True;")
        Private DBCmd As SqlCommand
    
        'DB Data
        Public DBDa As SqlDataAdapter
        Public DBDt As DataTable
    
    
        'Query Param
        Public Params As New List(Of SqlParameter)
    
        'Query Stats 
        Public RecordCount As Integer
        Public Exception As String
        Public FirstValue As String
    
        Public Sub New()
        End Sub
    
        'allow override to conenction string
    
        Public Sub New(ConnectionString As String)
            DBCon = New SqlConnection(ConnectionString)
    
        End Sub
    
    
        'execute query sub - add, update,delete
    
        Public Sub ExecuteQuery(Query As String)
            'reset query stats
            RecordCount = 0
            Exception = ""
    
            Try
                DBCon.Open()
                'create a db command
                DBCmd = New SqlCommand(Query, DBCon)                 '- Query = jobto do & DBCon = where to do it
                'load params into db command  
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))       ' - adds all the parmas in one statement
                Params.Clear()                                       '  clear params list once it has been used ready for next time
                'execute the command & Fill dataset
                DBDt = New DataTable                                 ' create new container to put the retrieved data into
                DBDa = New SqlDataAdapter(DBCmd)                     ' execute the DB Command via the dataadaptor 
                RecordCount = DBDa.Fill(DBDt)                        ' &  put into datatable   
    
            Catch ex As Exception
                Exception = "ExecQuery Error:- " & vbNewLine & ex.Message
    
            Finally
                'close connection 
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
    
            End Try
    
        End Sub
    
        Public Sub ExecuteOpen()
            'reset query stats
            RecordCount = 0
            Exception = ""
    
            Try
                DBCon.Open()
                '
            Catch ex As Exception
                Exception = "Open Error:- " & vbNewLine & ex.Message
    
            Finally
    
            End Try
    
        End Sub
        Public Sub ExecuteClose()
            'reset query stats
            RecordCount = 0
            Exception = ""
    
            Try
                DBCon.Close()
                '
            Catch ex As Exception
                Exception = "Close Error:- " & vbNewLine & ex.Message
    
            Finally
    
            End Try
    
        End Sub
        Public Sub ExecuteScalarQuery(Query As String)
            'reset query stats
            RecordCount = 0
            Exception = ""
            FirstValue = 0
    
            Try
                DBCon.Open()
                'create a db command
                DBCmd = New SqlCommand(Query, DBCon)                 '- Query = jobto do & DBCon = where to do it
                'load params into db command  
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))       ' - adds all the parmas in one statement
                Params.Clear()                                       '  clear params list once it has been used ready for next time
                ' DBDa = New SqlDataAdapter(DBCmd)                     ' execute the DB Command via the dataadaptor 
                FirstValue = DBCmd.ExecuteScalar()
            Catch ex As Exception
                Exception = "ExecScalar Error:- " & vbNewLine & ex.Message
    
            Finally
                'close connection 
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
    
            End Try
    
        End Sub
    
        Public Sub DataUpdate(Command As String)          ' not tested
            Try
                DBCon.Open()
                DBCmd = New SqlCommand(Command, DBCon)
                Dim changecount As Integer = DBCmd.ExecuteNonQuery()
                If changecount = 0 Then
                    MsgBox("the item you wanted could not be found")
                Else
                    MsgBox(changecount & " Records Affected")
                End If
    
    
            Catch ex As Exception
                Exception = "ExecUpdate Error:- " & vbNewLine & ex.Message
            Finally
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
            End Try
        End Sub
    
    
        Public Sub ExecuteNonQuery(Command As String)    ' As Created From  Execute Query -check wit tuturial that it is ok
            Try
                DBCon.Open()
                DBCmd = New SqlCommand(Command, DBCon)
                'load params into db command  
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))       ' - adds all the parmas in one statement
                Params.Clear()
                RecordCount = DBCmd.ExecuteNonQuery()
    
            Catch ex As Exception
                Exception = "ExecUpdate Error:- " & vbNewLine & ex.Message
            Finally
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
    
            End Try
        End Sub
        Public Sub ExecuteNonQueryNoOpenOrClose(Command As String)    ' As Created From  Execute Query -check wit tuturial that it is ok
            Try
    
                DBCmd = New SqlCommand(Command, DBCon)
                'load params into db command  
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))       ' - adds all the parmas in one statement
                Params.Clear()
                RecordCount = DBCmd.ExecuteNonQuery()
    
            Catch ex As Exception
                Exception = "ExecUpdate Error:- " & vbNewLine & ex.Message
            Finally
    
    
            End Try
        End Sub
    
    
        'add parms
        Public Sub AddParam(Name As String, Value As Object)
            Dim newParam As New SqlParameter(Name, Value)
            Params.Add(newParam)
        End Sub
    
        'error checking 
        Public Function HasException(Optional Report As Boolean = False) As Boolean
            If String.IsNullOrEmpty(Exception) Then Return False
            If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception: ")
            Return True
        End Function
    
    End Class

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,412

    Re: Speed is not everything unless you have lots of records

    My first impression.

    Parameters should be added once - outside the loop.

    Then you fill the "value" of each parameter (that is already created) inside the loop.

    You are also building a simple "concatenated" string to assign to the CommandText.

    That string never changes - so no need to re-build it 10,000 times.

    Get those things out of the loop to see how it affects the results.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,412

    Re: Speed is not everything unless you have lots of records

    Quote Originally Posted by Signalman View Post
    part 2 as it is to long for 1 post

    with the SQLControl class as :-

    Code:
    Imports System.Data.SqlClient
    
    Public Class SQLControlClass
        Private DBCon As New SqlConnection("Server=" & My.Settings.SQLSrverName & ";Database=" & My.Settings.SQLDbName & ";Trusted_Connection=True;")
        Private DBCmd As SqlCommand
    
        'DB Data
        Public DBDa As SqlDataAdapter
        Public DBDt As DataTable
    
    
        'Query Param
        Public Params As New List(Of SqlParameter)
    
        'Query Stats 
        Public RecordCount As Integer
        Public Exception As String
        Public FirstValue As String
    
        Public Sub New()
        End Sub
    
        'allow override to conenction string
    
        Public Sub New(ConnectionString As String)
            DBCon = New SqlConnection(ConnectionString)
    
        End Sub
    
    
        'execute query sub - add, update,delete
    
        Public Sub ExecuteQuery(Query As String)
            'reset query stats
            RecordCount = 0
            Exception = ""
    
            Try
                DBCon.Open()
                'create a db command
                DBCmd = New SqlCommand(Query, DBCon)                 '- Query = jobto do & DBCon = where to do it
                'load params into db command  
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))       ' - adds all the parmas in one statement
                Params.Clear()                                       '  clear params list once it has been used ready for next time
                'execute the command & Fill dataset
                DBDt = New DataTable                                 ' create new container to put the retrieved data into
                DBDa = New SqlDataAdapter(DBCmd)                     ' execute the DB Command via the dataadaptor 
                RecordCount = DBDa.Fill(DBDt)                        ' &  put into datatable   
    
            Catch ex As Exception
                Exception = "ExecQuery Error:- " & vbNewLine & ex.Message
    
            Finally
                'close connection 
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
    
            End Try
    
        End Sub
    
        Public Sub ExecuteOpen()
            'reset query stats
            RecordCount = 0
            Exception = ""
    
            Try
                DBCon.Open()
                '
            Catch ex As Exception
                Exception = "Open Error:- " & vbNewLine & ex.Message
    
            Finally
    
            End Try
    
        End Sub
        Public Sub ExecuteClose()
            'reset query stats
            RecordCount = 0
            Exception = ""
    
            Try
                DBCon.Close()
                '
            Catch ex As Exception
                Exception = "Close Error:- " & vbNewLine & ex.Message
    
            Finally
    
            End Try
    
        End Sub
        Public Sub ExecuteScalarQuery(Query As String)
            'reset query stats
            RecordCount = 0
            Exception = ""
            FirstValue = 0
    
            Try
                DBCon.Open()
                'create a db command
                DBCmd = New SqlCommand(Query, DBCon)                 '- Query = jobto do & DBCon = where to do it
                'load params into db command  
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))       ' - adds all the parmas in one statement
                Params.Clear()                                       '  clear params list once it has been used ready for next time
                ' DBDa = New SqlDataAdapter(DBCmd)                     ' execute the DB Command via the dataadaptor 
                FirstValue = DBCmd.ExecuteScalar()
            Catch ex As Exception
                Exception = "ExecScalar Error:- " & vbNewLine & ex.Message
    
            Finally
                'close connection 
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
    
            End Try
    
        End Sub
    
        Public Sub DataUpdate(Command As String)          ' not tested
            Try
                DBCon.Open()
                DBCmd = New SqlCommand(Command, DBCon)
                Dim changecount As Integer = DBCmd.ExecuteNonQuery()
                If changecount = 0 Then
                    MsgBox("the item you wanted could not be found")
                Else
                    MsgBox(changecount & " Records Affected")
                End If
    
    
            Catch ex As Exception
                Exception = "ExecUpdate Error:- " & vbNewLine & ex.Message
            Finally
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
            End Try
        End Sub
    
    
        Public Sub ExecuteNonQuery(Command As String)    ' As Created From  Execute Query -check wit tuturial that it is ok
            Try
                DBCon.Open()
                DBCmd = New SqlCommand(Command, DBCon)
                'load params into db command  
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))       ' - adds all the parmas in one statement
                Params.Clear()
                RecordCount = DBCmd.ExecuteNonQuery()
    
            Catch ex As Exception
                Exception = "ExecUpdate Error:- " & vbNewLine & ex.Message
            Finally
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
    
            End Try
        End Sub
        Public Sub ExecuteNonQueryNoOpenOrClose(Command As String)    ' As Created From  Execute Query -check wit tuturial that it is ok
            Try
    
                DBCmd = New SqlCommand(Command, DBCon)
                'load params into db command  
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))       ' - adds all the parmas in one statement
                Params.Clear()
                RecordCount = DBCmd.ExecuteNonQuery()
    
            Catch ex As Exception
                Exception = "ExecUpdate Error:- " & vbNewLine & ex.Message
            Finally
    
    
            End Try
        End Sub
    
    
        'add parms
        Public Sub AddParam(Name As String, Value As Object)
            Dim newParam As New SqlParameter(Name, Value)
            Params.Add(newParam)
        End Sub
    
        'error checking 
        Public Function HasException(Optional Report As Boolean = False) As Boolean
            If String.IsNullOrEmpty(Exception) Then Return False
            If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception: ")
            Return True
        End Function
    
    End Class
    Oh - and your class is re-adding and re-processing parameters. That's a potential place for badness...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,103

    Re: Speed is not everything unless you have lots of records

    It's worse than that... they are being added without a data type, which is very likely to slow it down.


    I'd strongly recommend not using the class for now (because it will affect speed in some way), just use virtually identical code to the SQLCe version, preferably amending both to use the suggestions in post #10.

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Apr 2010
    Posts
    248

    Re: Speed is not everything unless you have lots of records

    Thanks for the information, I have amended the code as suggested (so it nearly matches the SQLCE version) and it has had no effect. The amended code for the SQL server is shown below :-

    Code:
          MsgBox("ready to start")
            Dim con As New SqlConnection("Server=" & My.Settings.SQLSrverName & ";Database=" & My.Settings.SQLDbName & ";Trusted_Connection=True;")
    
            Dim cmd As SqlCommand = con.CreateCommand
            con.Open()
    
            cmd.Parameters.Clear()
            cmd.Parameters.Add("@a", System.Data.SqlDbType.Int)
            cmd.Parameters.Add("@b", System.Data.SqlDbType.Int)
            cmd.Parameters.Add("@c", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@d", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@e", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@f", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@g", System.Data.SqlDbType.Int)
            cmd.Parameters.Add("@h", System.Data.SqlDbType.Int)
            cmd.Parameters.Add("@i", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@j", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@k", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@l", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@m", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@n", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@o", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@p", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@q", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@r", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@s", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@t", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@u", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@v", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@w", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@x", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@y", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@z", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@aa", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ab", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ac", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ad", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ae", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@af", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ag", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ah", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ai", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@aj", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ak", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@al", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@am", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@an", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ao", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ap", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@aq", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ar", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@au", System.Data.SqlDbType.NVarChar)
    
            cmd.CommandText = "INSERT INTO testfile VALUES (@a," + _
                                                             "@b," + _
                                                             "@c," + _
                                                             "@d," + _
                                                             "@e," + _
                                                             "@f," + _
                                                             "@g," + _
                                                             "@h," + _
                                                             "@i," + _
                                                             "@j," + _
                                                             "@k," + _
                                                             "@l," + _
                                                             "@m," + _
                                                             "@n," + _
                                                             "@o," + _
                                                             "@p," + _
                                                             "@q," + _
                                                             "@r," + _
                                                             "@s," + _
                                                             "@t," + _
                                                             "@u," + _
                                                             "@v," + _
                                                             "@w," + _
                                                             "@x," + _
                                                             "@y," + _
                                                             "@z," + _
                                                             "@aa," + _
                                                             "@ab," + _
                                                             "@ac," + _
                                                             "@ad," + _
                                                             "@ae," + _
                                                             "@af," + _
                                                             "@ag," + _
                                                             "@ah," + _
                                                             "@ai," + _
                                                             "@aj," + _
                                                             "@ak," + _
                                                             "@al," + _
                                                             "@am," + _
                                                             "@an," + _
                                                             "@ao," + _
                                                             "@ap," + _
                                                             "@aq," + _
                                                             "@ar," + _
                                                             "@au);"
    
            For a = 1 To 10000
    
    
    
    
                cmd.Parameters("@a").Value = a
                cmd.Parameters("@b").Value = a
                cmd.Parameters("@c").Value = ""
                cmd.Parameters("@d").Value = ""
                cmd.Parameters("@e").Value = " "
                cmd.Parameters("@f").Value = " "
                cmd.Parameters("@g").Value = 0
                cmd.Parameters("@h").Value = 0
                cmd.Parameters("@i").Value = " "
                cmd.Parameters("@j").Value = " "
                cmd.Parameters("@k").Value = " "
                cmd.Parameters("@l").Value = " "
                cmd.Parameters("@m").Value = " "
                cmd.Parameters("@n").Value = " "
                cmd.Parameters("@o").Value = " "
                cmd.Parameters("@p").Value = " "
                cmd.Parameters("@q").Value = " "
                cmd.Parameters("@r").Value = " "
                cmd.Parameters("@s").Value = " "
                cmd.Parameters("@t").Value = " "
                cmd.Parameters("@u").Value = " "
                cmd.Parameters("@v").Value = " "
                cmd.Parameters("@w").Value = " "
                cmd.Parameters("@x").Value = " "
                cmd.Parameters("@y").Value = " "
                cmd.Parameters("@z").Value = " "
                cmd.Parameters("@aa").Value = " "
                cmd.Parameters("@ab").Value = " "
                cmd.Parameters("@ac").Value = " "
                cmd.Parameters("@ad").Value = " "
                cmd.Parameters("@ae").Value = " "
                cmd.Parameters("@af").Value = " "
                cmd.Parameters("@ag").Value = " "
                cmd.Parameters("@ah").Value = " "
                cmd.Parameters("@ai").Value = " "
                cmd.Parameters("@aj").Value = " "
                cmd.Parameters("@ak").Value = " "
                cmd.Parameters("@al").Value = " "
                cmd.Parameters("@am").Value = " "
                cmd.Parameters("@an").Value = " "
                cmd.Parameters("@ao").Value = " "
                cmd.Parameters("@ap").Value = " "
                cmd.Parameters("@aq").Value = " "
                cmd.Parameters("@ar").Value = " "
                cmd.Parameters("@au").Value = " "
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox("Error Inserting Record" & vbCrLf & ex.Message & vbCrLf & cmd.CommandText)
    
    
                End Try
    
            Next
            con.Close()
            'End Using
            MsgBox("Done")

  14. #14
    Hyperactive Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    441

    Re: Speed is not everything unless you have lots of records

    Do both versions have the same table schemas?

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Apr 2010
    Posts
    248

    Re: Speed is not everything unless you have lots of records

    I have even amended the code further (not that this will work my application, but removes some more code out of the loop and it only gives a reduction from 40 sec to 38 sec.

    it looks like the SQLServer express (is quite slow). is there anything I can add to the connection string to speed it up ?


    Code:
            MsgBox("ready to start")
            Dim con As New SqlConnection("Server=" & My.Settings.SQLSrverName & ";Database=" & My.Settings.SQLDbName & ";Trusted_Connection=True;")
    
            Dim cmd As SqlCommand = con.CreateCommand
            con.Open()
    
            cmd.Parameters.Clear()
            cmd.Parameters.Add("@a", System.Data.SqlDbType.Int)
            cmd.Parameters.Add("@b", System.Data.SqlDbType.Int)
            cmd.Parameters.Add("@c", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@d", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@e", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@f", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@g", System.Data.SqlDbType.Int)
            cmd.Parameters.Add("@h", System.Data.SqlDbType.Int)
            cmd.Parameters.Add("@i", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@j", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@k", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@l", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@m", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@n", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@o", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@p", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@q", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@r", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@s", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@t", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@u", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@v", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@w", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@x", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@y", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@z", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@aa", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ab", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ac", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ad", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ae", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@af", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ag", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ah", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ai", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@aj", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ak", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@al", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@am", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@an", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ao", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ap", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@aq", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@ar", System.Data.SqlDbType.NVarChar)
            cmd.Parameters.Add("@au", System.Data.SqlDbType.NVarChar)
    
            cmd.CommandText = "INSERT INTO testfile VALUES (@a," + _
                                                             "@b," + _
                                                             "@c," + _
                                                             "@d," + _
                                                             "@e," + _
                                                             "@f," + _
                                                             "@g," + _
                                                             "@h," + _
                                                             "@i," + _
                                                             "@j," + _
                                                             "@k," + _
                                                             "@l," + _
                                                             "@m," + _
                                                             "@n," + _
                                                             "@o," + _
                                                             "@p," + _
                                                             "@q," + _
                                                             "@r," + _
                                                             "@s," + _
                                                             "@t," + _
                                                             "@u," + _
                                                             "@v," + _
                                                             "@w," + _
                                                             "@x," + _
                                                             "@y," + _
                                                             "@z," + _
                                                             "@aa," + _
                                                             "@ab," + _
                                                             "@ac," + _
                                                             "@ad," + _
                                                             "@ae," + _
                                                             "@af," + _
                                                             "@ag," + _
                                                             "@ah," + _
                                                             "@ai," + _
                                                             "@aj," + _
                                                             "@ak," + _
                                                             "@al," + _
                                                             "@am," + _
                                                             "@an," + _
                                                             "@ao," + _
                                                             "@ap," + _
                                                             "@aq," + _
                                                             "@ar," + _
                                                             "@au);"
    
            cmd.Parameters("@c").Value = ""
            cmd.Parameters("@d").Value = ""
            cmd.Parameters("@e").Value = " "
            cmd.Parameters("@f").Value = " "
            cmd.Parameters("@g").Value = 0
            cmd.Parameters("@h").Value = 0
            cmd.Parameters("@i").Value = " "
            cmd.Parameters("@j").Value = " "
            cmd.Parameters("@k").Value = " "
            cmd.Parameters("@l").Value = " "
            cmd.Parameters("@m").Value = " "
            cmd.Parameters("@n").Value = " "
            cmd.Parameters("@o").Value = " "
            cmd.Parameters("@p").Value = " "
            cmd.Parameters("@q").Value = " "
            cmd.Parameters("@r").Value = " "
            cmd.Parameters("@s").Value = " "
            cmd.Parameters("@t").Value = " "
            cmd.Parameters("@u").Value = " "
            cmd.Parameters("@v").Value = " "
            cmd.Parameters("@w").Value = " "
            cmd.Parameters("@x").Value = " "
            cmd.Parameters("@y").Value = " "
            cmd.Parameters("@z").Value = " "
            cmd.Parameters("@aa").Value = " "
            cmd.Parameters("@ab").Value = " "
            cmd.Parameters("@ac").Value = " "
            cmd.Parameters("@ad").Value = " "
            cmd.Parameters("@ae").Value = " "
            cmd.Parameters("@af").Value = " "
            cmd.Parameters("@ag").Value = " "
            cmd.Parameters("@ah").Value = " "
            cmd.Parameters("@ai").Value = " "
            cmd.Parameters("@aj").Value = " "
            cmd.Parameters("@ak").Value = " "
            cmd.Parameters("@al").Value = " "
            cmd.Parameters("@am").Value = " "
            cmd.Parameters("@an").Value = " "
            cmd.Parameters("@ao").Value = " "
            cmd.Parameters("@ap").Value = " "
            cmd.Parameters("@aq").Value = " "
            cmd.Parameters("@ar").Value = " "
            cmd.Parameters("@au").Value = " "
    
            For a = 1 To 10000
                cmd.Parameters("@a").Value = a
                cmd.Parameters("@b").Value = a
    
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox("Error Inserting Record" & vbCrLf & ex.Message & vbCrLf & cmd.CommandText)
    
    
                End Try
    
            Next
            con.Close()
            'End Using
            MsgBox("Done")

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Apr 2010
    Posts
    248

    Re: Speed is not everything unless you have lots of records

    Quote Originally Posted by PlausiblyDamp View Post
    Do both versions have the same table schemas?
    Yes both are created to be exactly the Same.

  17. #17
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    4,981

    Re: Speed is not everything unless you have lots of records

    As mentioned, it is worth to try to do this with bulkcopy.Writetoserver

    I had a method that was inserting about 10.000 rows and i first used a loop and it took about 30 seconds.
    I used bulkcopy and it took 3 seconds!

    The problem here is that you do not have a database to hold your values and as I see it, you are inserting while creating.
    So if there is no means to copy from a source, the time the bulkcopy will save it will be in contrast while trying to create a list or a datatable with the vales and to insert it with bulkcopy. Am not sure of the time the list will do to be created before you bulkcopy it. However if what you are doing is unavoidable then you can first try to insert the data on, lets say a datatable and then bulk copy. You will save 10.000 cmd.ExecuteNonQuery() calls.

    If however you will be using the same data frequently, you can create a ready to go table and bulk copy from there.That would be ideal for copy to copy tables
    Last edited by sapator; May 16th, 2017 at 07:09 AM.
    Slow as hell.

  18. #18
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,076

    Re: Speed is not everything unless you have lots of records

    Quote Originally Posted by Signalman View Post
    I have even amended the code further (not that this will work my application, but removes some more code out of the loop and it only gives a reduction from 40 sec to 38 sec.

    it looks like the SQLServer express (is quite slow). is there anything I can add to the connection string to speed it up ?
    What about wrapping the whole operation in a transaction (outside the loop of course).
    This should normally speed it up (since you avoid the otherwise atomic commits on each loop-iteration).

    And does it have to be SQLServer?
    E.g. with SQLite you could insert your 10000 records in perhaps 0.1sec or so...

    Olaf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.