主题：关于代码的效率问题

下面的代码在我的电脑上测试短短240行数据居然用了62秒！请问问题出在哪里？有更高效的代码吗？

 

Dim trv As WinForm.TreeView = e.Form.Controls("TreeView1")
Dim nd As WinForm.TreeNode = trv.SelectedNode
If nd Is Nothing Or e.Form.Controls("产品条码").Enabled = False Then
    MessageBox.Show("请选择需要入库的到货单,并双击,然后单击""确定"",填入库房,库区,库位,再单击""开始入库""")
    Return
End If
Dim cmd As new SQLCommand
cmd.C
cmd.CommandText = "select * from {入库记录} where 到货单号 ='" & nd.text & "'"
If cmd.ExecuteReader.DataRows.count > 0 Then
    MessageBox.Show("本单已经存在,不可重复导入!")
    Return
End If
If DataTables("入库明细").Select("到货单号 ='" & nd.text & "'").count > 0 Then
    MessageBox.Show("本单已经存在,不可重复导入!")
    Return
End If

If MessageBox.Show("您是否确信到货单:" & nd.text & "可以无需验货直接入库?","提示",MessageBoxButtons.YesNo) = DialogResult.No Then
    Return
End If

Dim t As Date = Date.Now
DataTables("在途明细").loadFilter = ""
DataTables("在途明细").loadFilter = "单号 ='" & nd.text & "'"
DataTables("在途明细").load()

Dim sts1 As String() ={"箱号","货号","颜色","尺码号","产品条码","产品名称","数量","品牌"}
Dim sts2 As String() ={"到货箱号","货号","颜色","尺码号","产品条码","产品名称","入库数量","品牌"}
Dim r1,r2 As Row

Tables("在途明细").StopRedraw
For Each r1 In Tables("在途明细").Rows
    r2 = Tables("入库明细").AddNew()
    For i As Integer = 0 To sts1.length - 1
        r2(sts2(i)) = r1(sts1(i))
        r2("库房") = e.Form.Controls("收货库房").value
        r2("库区") = e.Form.Controls("库区").value
        r2("库位") = e.Form.Controls("库位").value
        r2("入库单号") = e.Form.Controls("入库单号").value
        r2("到货单号") = e.Form.Controls("到货单号").value
    Next
Next
Tables("在途明细").ResumeRedraw

r1 =Tables("入库记录").AddNew()
Dim s1 As String() ={"收货库房","入库单号","发货单位","发货人","收货人","入库时间","收货单位","到货单号","发货库房","入库类型"}
For i As Integer = 0 To s1.length - 1
    r1(s1(i)) = e.Form.controls(s1(i)).value
Next
Dim tp As TimeSpan = Date.Now - t
MessageBox.Show(tp.TotalSeconds)
MessageBox.Show("导入成功!请检查确认后正式入库!")

 

同样是240行，下面这段代码也竟然用了32秒！

 

If MessageBox.Show("是否确定正式入库?","提示",MessageBoxButtons.YesNo,messageboxicon.warning) = DialogResult.No Then
    Return
End If
If Tables("入库明细").Rows.count = 0 Then
    MessageBox.Show("请录入需要入库的货品!")
    Return
End If
Dim t As Date = Date.now
For Each r As Row In Tables("入库明细")
    Dim filter As String = "产品条码 ='" & r("产品条码") & "' and 库房 = '" & r("库房") & "' And 库区 ='" & r("库区") & "'  And 库位 =" & r("库位")
    Dim cmd As new SQLCommand
    cmd.C
    cmd.CommandText= "select 库存数量 From {库存表} where " & filter
    Dim v As Integer = cmd.ExecuteScalar
        Dim cmd1 As new SQLCommand
        cmd1.C
        cmd1.CommandText = "Update {库存表} set 库存数量 =" & v +  r("入库数量") & "  where " & filter

        cmd1.ExecuteNonQuery()
    If cmd1.ExecuteNonQuery < 0
        Dim dr As DataRow = DataTables("库存表").AddNew()
        Dim sts1 As String() ={"货号","颜色","尺码号","产品条码","产品名称","库房","库区","库位","库存数量","品牌"}
        Dim sts2 As String() ={"货号","颜色","尺码号","产品条码","产品名称","库房","库区","库位","入库数量","品牌"}
        For i As Integer = 0 To sts1.length -1
            dr(sts1(i)) = r(sts2(i))
            dr.save()
        Next
    End If
Next
'删除已入库的在途记录
Dim cmd2 As new SQLCommand
cmd2.C
cmd2.CommandText = "delete From {在途记录} where 单号 ='" & Tables("入库明细").Rows(0)("入库单号").Substring(3) & "'"
cmd2.ExecuteNonQuery()

Dim cmd3 As new SQLCommand
cmd3.C
cmd3.CommandText = "delete From {在途明细} where 单号 ='" & Tables("入库明细").Rows(0)("入库单号").Substring(3) & "'"
cmd3.ExecuteNonQuery()

Dim tp As TimeSpan = Date.now - t
messagebox.Show(tp.TotalSeconds)

DataTables("库存表").LoadFilter = ""
DataTables("库存表").load()
MessageBox.Show("已成功入库")

 

 

 不应的，你可以在多个地方msgbox(tp)来看一下，应该是在赋值的时候触发了相关的代码，连锁反应，导致变慢吧。

果然这样，注释事件代码后，第一段用了3.6秒，第二段1.3秒，看来需要暂停执行事件代码。

不过仅仅240行，即便这样的速度还是慢，能不能帮忙看看能不能更快些？最好能控制在0.5秒以内。

我感觉自己写的代码在执行效率上还是有问题，只是不知道问题在哪，应该从哪些方面改进。

 一行一行的拷贝数据，3.6秒算不错了。况且你还执行了sql语句。

在相对应的条件列上建立索引，提高SQL的执行速度。

楼主，最好给实例文件，您这个肯定不正常！

 

列命名尽量要规范化，便于写代码，比如您将在途明细和入库明细两个表的列名称统一一下，写代码也就没必要这么绕，只要在报表和标题上区分开来即可，类似的地方都可以这样考虑，写代码会更简洁，还有有些表如果有关联，可先添加关联的父记录，在添加观念明细记录，通过表事件写代码，有的字段就没必要这么来回通过代码到处去赋值。

谢谢各位了！我重新改写了代码，现在速度非常快，所有执行完毕在0.3秒之内。