So, I'm doing a project where I have to read data from a PLC with modbus protocol and insert the data into a database in this case MySQL. I'm having no trouble reading the modbus data and inserting into MySQL using for loop. However I have to insert probably around 1000 data per second into the database. I tried doing 100 data and it took around 5 second for the all the data to be inserted into my table. Is there any ways to insert the data quicker? or is it the limit of MySQL database? I tried using SQL Server too, it took around 1.5 seconds to insert 100 data into the table
Here's my code:
myConnectionString = "server=127.0.0.1;" _
& "uid=root;" _
& "pwd=;" _
& "database=modbus"
modbus.IPAddress = "192.168.18.70"
modbus.Port = 502
Do
If modbus.Connected = False Then
Try
modbus.Connect()
If modbus.Connected Then
Label1.Text = "Connected"
End If
Catch ex As Exception
Label1.Text = "Disconnected"
End Try
End If
If conn.State = 0 Then
conn.ConnectionString = myConnectionString
Try
conn.Open()
Label2.Text = conn.State
Catch ex As MySql.Data.MySqlClient.MySqlException
Label2.Text = conn.State
End Try
End If
If modbus.Connected Then
Dim Regvals As Integer()
Dim regvals2 As Integer()
Dim regvals3 As Integer()
Dim regvals4 As Integer()
Dim regvals5 As Integer()
Dim regvals6 As Integer()
Dim regvals7 As Integer()
Dim regvals8 As Integer()
Dim regvals9 As Integer()
Dim regvals10 As Integer()
Dim sql As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim tgl As String
Try
Regvals = modbus.ReadHoldingRegisters(0, 100)
regvals2 = modbus.ReadHoldingRegisters(100, 100)
regvals3 = modbus.ReadHoldingRegisters(200, 100)
regvals2 = modbus.ReadHoldingRegisters(300, 100)
regvals3 = modbus.ReadHoldingRegisters(400, 100)
regvals2 = modbus.ReadHoldingRegisters(500, 100)
regvals3 = modbus.ReadHoldingRegisters(600, 100)
regvals2 = modbus.ReadHoldingRegisters(700, 100)
regvals3 = modbus.ReadHoldingRegisters(800, 100)
regvals10 = modbus.ReadHoldingRegisters(900, 100)
modbus.Disconnect()
Catch ex As Exception
End Try
If Regvals.Length > 0 Then
i = 0
j = 0
k = 0
ListBox1.Items.Clear()
'
For Each value As Integer In Regvals
tgl = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
ListBox1.Items.Add(value)
sql = "insert into test values('" & tgl & "'," & i & "," & Regvals(i) & ")"
i = i + 1
cmd.Connection = conn
cmd.CommandText = sql
cmd.ExecuteNonQuery()
sql = ""
Label3.Text = i
Next
If regvals2.Length > 0 Then
For Each value As Integer In regvals2
tgl = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
ListBox1.Items.Add(value)
'
sql = "insert into test values('" & tgl & "'," & i & "," & regvals2(j) & ")"
j = j + 1
i = i + 1
cmd.Connection = conn
cmd.CommandText = sql
cmd.ExecuteNonQuery()
sql = ""
Label3.Text = i
Next
End If
If regvals3.Length > 0 Then
For Each value As Integer In regvals3
tgl = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
ListBox1.Items.Add(value)
'
'
sql = "insert into test values('" & tgl & "'," & i & "," & regvals3(k) & ")"
i = i + 1
k = k + 1
cmd.Connection = conn
cmd.CommandText = sql
cmd.ExecuteNonQuery()
sql = ""
Label3.Text = i
Next
End If
End If
End If
Loop
