Results 1 to 7 of 7

Thread: I need faster code to save update data to ms.sql server 2008r2

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2018
    Posts
    18

    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.

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Jan 2018
    Posts
    18

    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

  3. #3
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * 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??? *

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    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.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jan 2018
    Posts
    18

    Re: I need faster code to save update data to ms.sql server 2008r2

    Quote Originally Posted by kareninstructor View Post
    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.
    thx karen, i will learn about your code

    Quote Originally Posted by kpmc View Post
    .....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.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jan 2018
    Posts
    18

    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
  •  



Click Here to Expand Forum to Full Width