CODEWITHSUNDEEP
mysqlvb.net
user3224535
user3224535

Reputation: 11

How to query and update faster in VB.net

How to speed up things here and update rows faster? I know this isn't supposed to go like this, can anyone recommend a better practice.

Thank you

Public Sub searchUpdate()
    strSQL = "*Large query, including 3 tables"

    cmd = New MySqlCommand(strSQL, CONNECTION)
    dr = cmd.ExecuteReader()
    ListView1.Items.Clear()

    Do While dr.Read()
        a = (dr.Item("users").ToString())
         b = (dr.Item("b1").ToString() & "," & dr.Item("b2").ToString() & "," & dr.Item("b3").ToString() & "," & dr.Item("b4").ToString() & "," & dr.Item("b5").ToString() & "," & dr.Item("b6").ToString())
        won = (dr.Item("number").ToString) * (dr.Item("input").ToString())
        qt = (dr.Item("input").ToString)
        updateForSearch(a, won, qt)

    Loop
    dr.Close()
    cmd.Dispose()
    CONNECTION.Close()
    TextBox1.Text = Val(TextBox1.Text) + 1
    Timer2.Stop()

End Sub`

Function to update:

Public Function updateForSearch(ByVal a As String, ByVal won As Integer, ByVal qt As Integer) As Integer
    dr.Close()
    strSQL = "UPDATE users SET status=2, qt='" & qt & "', bcvd='" & won & "'  WHERE acvd='" & a & "'"
    Dim da As New MySqlDataAdapter(strSQL, CONNECTION)
    da.Fill(ds)
    dr = cmd.ExecuteReader()

End Function

Upvotes: 1

Views: 1553

Answers (1)

T.S.
T.S.

Reputation: 19404

This example shows how you can run your update without even going to VB.net

UPDATE users u 
    JOIN tbl1 t1 on u.col1 = t1.col1 
    JOIN tbl2 c on t1.col2 = t2.col2
    -- join more tables 
SET 
    u.col_a = t1.col3, ......
WHERE ....

Basically, if you can join your update table with the large join in your select - you can do it without making data leaving database. And this going to improve performance big time, first - because data is not traveling, second - because now this is batch vs. row one-by-one

May be the best way for you is to create a View out of your complex query and then use that view for update. The rules are only 3:

1 - your view must return fields used for assignment of fields in "Users".

2 - your view must return fields used for join with "users".

3 - your view may need to be aggregated, you must have not more then one matching value in the inner join

Upvotes: 1

Related Questions