为什么我的应用程序工作速度变慢，是否有任何代码可以使该应用程序运行得更快

Question

我正在制作一个应用程序来查找数据网格的值是否在表 1 中，然后对表 2 进行更新 数据网格填充是表 2 我的代码正在工作，但需要 1 分钟或更长时间才能完成 1 行，是否有任何代码可以使我的应用程序更快地工作？

这是我的代码：

Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim sql As String = "select date,npk,nama,masuk,out,h_ot,flag,status from lemburapril"
    Dim dataadapter As New SqlDataAdapter(sql, str)
    str.Open()
    Dim ds As New DataSet()
    dataadapter.Fill(ds, "ds")
    str.Close()
    DataGridView1.DataSource = ds
    DataGridView1.DataMember = "ds"
    For i = 0 To ds.Tables(0).Rows.Count - 1
        Dim tgl As Date = ds.Tables(0).Rows(i).Item(0).ToString
        Dim npk As String = ds.Tables(0).Rows(i).Item(1).ToString
        Dim nama As String = ds.Tables(0).Rows(i).Item(2).ToString
        Dim masuk As String = ds.Tables(0).Rows(i).Item(3).ToString
        Dim out As String = ds.Tables(0).Rows(i).Item(4).ToString
        Dim h_ot As String = ds.Tables(0).Rows(i).Item(5).ToString
        Dim Flag As String = ds.Tables(0).Rows(i).Item(6).ToString
        Dim status As String = ds.Tables(0).Rows(i).Item(7).ToString
        For Each Rows In DataGridView1.Rows
            Dim sqlup As String = "update lemburapril set status = 'v' where date = any (select date from ABY1904$ where date ='" & tgl & "') and npk = any (select npk from ABY1904$ where npk = '" & npk & "')"
            str.Open()
            COMMANDSQL(sqlup)
            str.Close()
        Next
    Next
End Sub
Public Sub display()
    Dim sql As String = "select date,npk,nama,masuk,out,h_ot,flag,status from lemburapril"
    Dim dataadapter As New SqlDataAdapter(Sql, str)
    str.Open()
    Dim ds As New DataSet()
    dataadapter.Fill(ds, "ds")
    str.Close()
    DataGridView1.DataSource = ds
    DataGridView1.DataMember = "ds"
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    display()
End Sub
End Class

这是我的连接：

Module koneksi
Dim sqlcom As New SqlCommand
Dim commansql As String
Public str As New SqlConnection("data source= Somebody-PCSQLEXPRESS ;initial catalog= lembur ;integrated security =true")
Public Sub COMMANDSQL(ByVal S As String)
    sqlcom = New SqlCommand(S, str)
    sqlcom.CommandType = CommandType.Text
    sqlcom.ExecuteNonQuery()
End Sub
End Module

Answer 1

摆脱模块。将所有数据库对象保留在本地，以便可以关闭和释放它们。用。。。即使存在错误，"结束使用"块也会处理此问题。看起来您正在遍历所有行，并为嵌套对象中的每一行再次运行更新。这给了你 n*n，这会减慢速度。

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    display()
End Sub
Private ConnString As String = "data source= Somebody-PCSQLEXPRESS ;initial catalog= lembur ;integrated security =true"
Private dt As New DataTable
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Using Str As New SqlConnection(ConnString)
        Using cmd As New SqlCommand("Update lemburapril Set status = 'v' Where date = any (Select date From ABY1904$ Where date =@tgl) And npk = any (Select npk From ABY1904$ Where npk = @npk)", Str)
            cmd.Parameters.Add("@tgl", SqlDbType.Date)
            cmd.Parameters.Add("@npk", SqlDbType.VarChar)
            Str.Open()
            For i = 0 To dt.Rows.Count - 1
                cmd.Parameters("@tgl").Value = CDate(dt.Rows(i).Item(0))
                cmd.Parameters("@npk").Value = dt.Rows(i).Item(1).ToString
                cmd.ExecuteNonQuery()
            Next
        End Using
    End Using
End Sub
Public Sub display()
    Using Str As New SqlConnection(ConnString)
        Using cmd As New SqlCommand("select date,npk,nama,masuk,[out],h_ot,flag,status from lemburapril", Str)
            Str.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    DataGridView1.DataSource = dt
End Sub