|
-
May 5th, 2017, 04:41 PM
#1
Thread Starter
Hyperactive Member
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.
-
May 6th, 2017, 12:38 AM
#2
Re: Speed is not everything unless you have lots of records
Are you using SqlBulkCopy?
-
May 6th, 2017, 12:01 PM
#3
Thread Starter
Hyperactive Member
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 ?
-
May 6th, 2017, 01:43 PM
#4
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.
-
May 8th, 2017, 07:23 AM
#5
Re: Speed is not everything unless you have lots of records
Is your SQL Server instance local or over a network?
-
May 8th, 2017, 07:29 AM
#6
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
-
May 8th, 2017, 08:32 AM
#7
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.
-
May 10th, 2017, 03:39 PM
#8
Thread Starter
Hyperactive Member
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()
-
May 10th, 2017, 03:40 PM
#9
Thread Starter
Hyperactive Member
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
-
May 10th, 2017, 03:59 PM
#10
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.
-
May 10th, 2017, 04:00 PM
#11
Re: Speed is not everything unless you have lots of records
 Originally Posted by Signalman
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...
-
May 10th, 2017, 04:19 PM
#12
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.
-
May 10th, 2017, 05:23 PM
#13
Thread Starter
Hyperactive Member
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")
-
May 10th, 2017, 05:30 PM
#14
Re: Speed is not everything unless you have lots of records
Do both versions have the same table schemas?
-
May 10th, 2017, 05:36 PM
#15
Thread Starter
Hyperactive Member
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")
-
May 10th, 2017, 06:04 PM
#16
Thread Starter
Hyperactive Member
Re: Speed is not everything unless you have lots of records
Originally Posted by PlausiblyDamp
Do both versions have the same table schemas?
Yes both are created to be exactly the Same.
-
May 16th, 2017, 06:33 AM
#17
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
May 23rd, 2017, 12:23 AM
#18
Re: Speed is not everything unless you have lots of records
Originally Posted by Signalman
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
