-
Feb 6th, 2018, 03:34 AM
#1
Thread Starter
Junior Member
I need faster code to save update data to ms.sql server 2008r2
I use Socket for receive data, i split data incoming when still in byte, so i thing it will be realtime when show in my computer. The problem when i try to update data incoming to sql server database, it will make a delay. My question , can you help me to make faster to save/update data to sql server ? thx guys
my code when receive data from socket
Code:
Private Sub doRead(ByVal ar As System.IAsyncResult)
Dim totalRead As Integer
Try
totalRead = client.GetStream.EndRead(ar)
Catch ex As Exception
MessageBox.Show(ex.Message)
Return
End Try
Dim start As Integer = 0
Dim receivedString As String
If totalRead > 0 Then
For i = 0 To totalRead - 1
If readBuffer(i) = EofLF Then
receivedString = Encoding.ASCII.GetString(readBuffer, start, i - start)
start = i
messageReceived(RestMsg & receivedString)
RestMsg = ""
End If
Next
If readBuffer(totalRead - 1) <> EofLF Then
RestMsg = Encoding.ASCII.GetString(readBuffer, start, totalRead - 1 - start)
End If
End If
Try
client.GetStream.BeginRead(readBuffer, 0, BYTES_TO_READ, AddressOf doRead, Nothing) 'Begin the reading again.
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
my code when received data and using thread and queue
Code:
Private Sub messageReceived(ByVal message As String)
UpdateStatusRTB(Me.RichTextBox1, message)
Try
Select Case message
Case vbCrLf
Case vbLf
Case Else
Dim MsgSplit() As String
MsgSplit = Split(message, "|")
If MsgSplit.Length < 10 Then Exit Sub
UpdateStatusTB(Me.TextBox1, MsgSplit(2))
Select Case MsgSplit(4) ' kode datafeed
Case 1
sqlstr = "dbo.spUpdateDatafeedOrder '" & MsgSplit(7).Trim & "','" & MsgSplit(8).Trim & "'," & Convert.ToDouble(MsgSplit(15).Trim) & "," & Convert.ToDouble(MsgSplit(16).Trim) & "," & Convert.ToDouble(MsgSplit(17).Trim) & "," & Convert.ToDouble(MsgSplit(18).Trim)
OrderQueue.Enqueue(sqlstr)
Dim ThreadOrder As New System.Threading.Thread(AddressOf UpdateDataOrder)
ThreadOrder.Start()
Case 2
sqlstr = "dbo.spUpdateDatafeedTrade '" & MsgSplit(7).Trim & "','" & MsgSplit(8).Trim & "'," & Convert.ToDouble(MsgSplit(10).Trim) & "," & Convert.ToDouble(MsgSplit(16).Trim) & "," & Convert.ToDouble(MsgSplit(17).Trim) & "," & Convert.ToDouble(MsgSplit(18).Trim) & "," & Convert.ToDouble(MsgSplit(19).Trim)
TradeQueue.Enqueue(sqlstr)
Dim ThreadTrade As New System.Threading.Thread(AddressOf UpdateDatatrade)
ThreadTrade.Start()
Case 5
sqlstr = "dbo.spUpdateDatafeedSummary '" & MsgSplit(5).Trim & "','" & MsgSplit(6).Trim & "'," & Convert.ToDouble(MsgSplit(7).Trim) & "," & Convert.ToDouble(MsgSplit(8).Trim) & "," & Convert.ToDouble(MsgSplit(9).Trim) & "," & Convert.ToDouble(MsgSplit(18).Trim) & "," & Convert.ToDouble(MsgSplit(19).Trim) & "," & Convert.ToDouble(MsgSplit(20).Trim) & "," & Convert.ToDouble(MsgSplit(21).Trim)
SummQueue.Enqueue(sqlstr)
Dim ThreadSumm As New System.Threading.Thread(AddressOf UpdateDataSumm)
ThreadSumm.Start()
Case Else
sqlstr = ""
End Select
End Select
UpdateStatusRTB(Me.RichTextBox1, "-------------")
Catch ex As Exception
UpdateStatusRTB(Me.RichTextBox2, "Err. Split Desc:" & ex.Message & "Err.Msg : " & message)
End Try
End Sub
Code:
Private Sub UpdateDataOrder()
Try
Dim SqlCmd As SqlClient.SqlCommand
If Not dequeueingOrder Then
dequeueingOrder = True
While OrderQueue.Count <> 0
con.Open()
SqlCmd = New SqlCommand(OrderQueue.Dequeue(), con)
SqlCmd.ExecuteNonQuery()
con.Close()
End While
dequeueingOrder = False
End If
Catch ex As Exception
UpdateStatusRTB(RichTextBox2, "err.desc Order : " & ex.Message & " err.mesg :" & OrderQueue.Dequeue())
End Try
Code:
UpdateDataSumm , UpdateDatatrade is same like UpdateDataOrder
Last edited by esugiawan; Feb 6th, 2018 at 04:55 AM.
-
Feb 6th, 2018, 04:05 AM
#2
Thread Starter
Junior Member
Re: I need faster code to save update data to ms.sql server 2008r2
if you have any idea to get faster reading socket , i will try too, thx
-
Feb 6th, 2018, 08:29 AM
#3
Re: I need faster code to save update data to ms.sql server 2008r2
What is the max count of OrderQueue? Instead of hammering the crap out of your DB maybe you should be modifying a datatable and call one update. You could probably update the datatable on a new thread also. Seems you are creating unnecessary connections to the server to me.
-
Feb 6th, 2018, 08:56 AM
#4
Re: I need faster code to save update data to ms.sql server 2008r2
Or at least used parameters rather than stringing every thing along like that...
But yeah... I agree... I'd stuff the data in a datatable, or set of datatables... when one of the dts gets to a certain number of rows, tie it off, send it to a thread and have that thread connect, update the database, close the connection and return. Meanwhile a new instance of the datatable is being used to collect more info. And so on...
-tg
-
Feb 7th, 2018, 07:08 AM
#5
Re: I need faster code to save update data to ms.sql server 2008r2
If you batch adds/updates to the database into a temp DataTable then push the data to the database via MERGE statement that will be faster.I have a vanilla example here.
-
Feb 7th, 2018, 11:09 AM
#6
Thread Starter
Junior Member
Re: I need faster code to save update data to ms.sql server 2008r2
Originally Posted by kareninstructor
thx karen, i will learn about your code
Originally Posted by kpmc
.....What is the max count of OrderQueue?.......
approximately 30-50 record
i try implementation datatable , and it's success in development , i will try to production, But if you have any idea , to get better code , please let me know,
thx guys
Code:
Private tblTemp As DataTable
Private oDataAdapter As SqlClient.SqlDataAdapter
Private ds As New DataSet
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
.......
Dim ConnectionString = connstring
Dim SqlCon As New SqlClient.SqlConnection
SqlCon.ConnectionString = ConnectionString
SqlCon.Open()
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = SqlCon
cmd.CommandText = “exec dbo.spSecCatList”
cmd.CommandTimeout = 6000
oDataAdapter = New SqlClient.SqlDataAdapter(cmd)
oDataAdapter.Fill(ds)
tblTemp = ds.Tables(0)
UpdateStatusRTB(RichTextBox1, tblTemp.Rows.Count)
End Sub
Data incoming from socket to messageReceived
Code:
Private Sub messageReceived(ByVal message As String)
UpdateStatusRTB(Me.RichTextBox1, message)
Try
Select Case message
....
Case Else
OrderQueue.Enqueue(message)
Dim ThreadOrder As New System.Threading.Thread(AddressOf UpdateDataOrder)
ThreadOrder.Start()
End Select
' UpdateStatusRTB(Me.RichTextBox1, "-------------")
Catch ex As Exception
UpdateStatusRTB(Me.RichTextBox2, "messageReceived Err. Split Desc:" & ex.Message & "Err.Msg : " & message)
End Try
End Sub
Making multithread from incoming messageReceived , i hope someone can give me information to use parallel.for with Queue in the below
Code:
Private dequeueingOrder As Boolean = False
Private OrderQueue As New Queue(Of String)
Private Sub UpdateDataOrder()
Dim msg As String = ""
Try
If Not dequeueingOrder Then
dequeueingOrder = True
If OrderQueue.Count = 0 Then Return
While OrderQueue.Count <> 0
msg = OrderQueue.Dequeue()
Dim MsgSplit() As String
MsgSplit = Split(msg, "|")
If MsgSplit.Length < 10 Then Exit Sub
Select Case MsgSplit(4) ' kode datafeed
Case 1
If MsgSplit(8).Trim = "RG" Then
Dim oRow() As DataRow = tblTemp.Select("no_share = '" & MsgSplit(7).Trim & "'")
oRow(0).BeginEdit()
oRow(0)("prvprice") = oRow(0)("prvprice")
oRow(0)("offer") = Convert.ToDouble(MsgSplit(15).Trim)
oRow(0)("Bid") = Convert.ToDouble(MsgSplit(16).Trim)
oRow(0)("Biddt") = Convert.ToDouble(MsgSplit(17).Trim)
oRow(0)("offerdt") = Convert.ToDouble(MsgSplit(18).Trim)
oRow(0)("high") = oRow(0)("high")
oRow(0)("low") = oRow(0)("low")
oRow(0)("last") = oRow(0)("last")
oRow(0)("change") = oRow(0)("change")
oRow(0).EndEdit()
End If
Case 2
..........
Case 5
..........
End Select
End While
dequeueingOrder = False
End If
Catch ex As Exception
UpdateStatusRTB(RichTextBox2, "err.desc Order : " & ex.Message & " err.mesg :" & OrderQueue.Dequeue())
End Try
End Sub
I use Timer to save to sql server, TimerInterval =5000
Code:
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
Try
Dim objCommandBuilder As New SqlClient.SqlCommandBuilder(oDataAdapter)
oDataAdapter.Update(tblTemp)
Catch ex As Exception
UpdateStatusRTB(RichTextBox2, "Timer1_Tick" & ex.Message)
End Try
UpdateStatusTB(TextBox1, "EndTime : " & Now.ToShortTimeString)
End Sub
Last edited by esugiawan; Feb 7th, 2018 at 11:14 AM.
-
Feb 8th, 2018, 10:32 PM
#7
Thread Starter
Junior Member
Error when implement : I need faster code to save update data to ms.sql server 2008r2
Dear all, please help
if i try to send message in development, only 1 message , the code is working , but if i try to give more than 50 messange, sometime good , sometime error.
The error message like ""Object reference not set to an instance of an object."" in this procedure
Code:
Private dequeueingOrder As Boolean = False
Private OrderQueue As New Queue(Of String)
Private Sub UpdateDataOrder()
Dim msg As String = ""
Try
If Not dequeueingOrder Then
dequeueingOrder = True
If OrderQueue.Count = 0 Then Return
While OrderQueue.Count <> 0
msg = OrderQueue.Dequeue()
Dim MsgSplit() As String
MsgSplit = Split(msg, "|")
If MsgSplit.Length < 10 Then Exit Sub
Select Case MsgSplit(4) ' kode datafeed
Case 1
If MsgSplit(8).Trim = "RG" Then
Dim oRow() As DataRow = tblTemp.Select("no_share = '" & MsgSplit(7).Trim & "'")
oRow(0).BeginEdit()
oRow(0)("prvprice") = oRow(0)("prvprice")
oRow(0)("offer") = Convert.ToDouble(MsgSplit(15).Trim)
oRow(0)("Bid") = Convert.ToDouble(MsgSplit(16).Trim)
oRow(0)("Biddt") = Convert.ToDouble(MsgSplit(17).Trim)
oRow(0)("offerdt") = Convert.ToDouble(MsgSplit(18).Trim)
oRow(0)("high") = oRow(0)("high")
oRow(0)("low") = oRow(0)("low")
oRow(0)("last") = oRow(0)("last")
oRow(0)("change") = oRow(0)("change")
oRow(0).EndEdit()
End If
Case 2
..........
Case 5
..........
End Select
End While
dequeueingOrder = False
End If
Catch ex As Exception
UpdateStatusRTB(RichTextBox2, "err.desc Order : " & ex.Message & " err.mesg :" & OrderQueue.Dequeue())
End Try
End Sub
and sometime , there was error message in sub timer like "An unhandled exception of type 'System.InvalidOperationException' occurred in System.dll"
in this procedure
Code:
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
Try
Dim objCommandBuilder As New SqlClient.SqlCommandBuilder(oDataAdapter)
oDataAdapter.Update(tblTemp)
Catch ex As Exception
UpdateStatusRTB(RichTextBox2, "Timer1_Tick" & ex.Message)
End Try
UpdateStatusTB(TextBox1, "EndTime : " & Now.ToShortTimeString)
End Sub
Last edited by esugiawan; Feb 8th, 2018 at 11:36 PM.
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
|