- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 请教数据匹配效率问题 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=178194)
-- 发布时间：2022/6/22 19:34:00
-- 请教数据匹配效率问题
Dim st As Date = Date.Now
Dim dt As DataTable = DataTables("表A")
Dim dt1 As DataTable = DataTables("表B")
dt.LoadFilter = ""
dt.Load
dt1.LoadFilter = ""dt.Load
dt1.Load
MessageBox.Show("耗时: " & (Date.Now - st).TotalSeconds & "秒")
st = Date.Now
Dim w As DataRow
Dim q As Integer
dt1.AddNew(dt.Rows.Count)
For Each dr As Row In dt.Rows
w = dt1.Find("pn = \'" & dr("pn") & "\'")
If w IsNot Nothing Then
If CDate(dr("approved")) > CDate(w("approved")) Then
w("po") = dr("po")
w("pi") = dr("pi")
w("approved") = dr("approved")
w("cost") = dr("cost")
End If
Else
q = dt1.Compute("count(pn)", "pn > \'\'")
w = dt1.DataRows(q)
w("po") = dr("po")
w("pi") = dr("pi")
w("pn") = dr("pn")
w("approved") = dr("approved")
w("cost") = dr("cost")
End If
Next
MessageBox.Show("耗时: " & (Date.Now - st).TotalSeconds & "秒")
dt1.DeleteFor("pn is null")
dt1.Save
dt1.DeleteFor("pn is null")
dt1.Save
表A的数据大概在25000多条，表B的数据大概在4000条，现在将表A的数据与表B的保留最大日期的数值，匹配合并到表B中，上述代码发现这个红色范围区域，这个匹配需要70多秒，速度比较慢！这个还能怎么优化？
-- 发布时间：2022/6/23 8:35:00
--
【现在将表A的数据与表B的保留最大日期的数值，匹配合并到表B中】--这个逻辑不是很理解，请详细再描述一下
-- 发布时间：2022/6/23 9:58:00
--
以下内容为程序代码:
1 如表A
2 po pi pn approved
3 P001 1 A1 2022-06-23 12:00:00
4 P002 1 A2 2022-06-23 12:00:00
5
6
7 如表B
8 po pi pn approved
9 P003 1 A1 2022-06-22 12:00:00
10
11
12 处理后变成
13
14 如表A
15 po pi pn approved
16 P001 1 A1 2022-06-23 12:00:00
17 P002 1 A2 2022-06-23 12:00:00
1 如表A
2 po pi pn approved
3 P001 1 A1 2022-06-23 12:00:00
4 P002 1 A2 2022-06-23 12:00:00
5
6
7 如表B
8 po pi pn approved
9 P003 1 A1 2022-06-22 12:00:00
10
11
12 处理后变成
13
14 如表A
15 po pi pn approved
16 P001 1 A1 2022-06-23 12:00:00
17 P002 1 A2 2022-06-23 12:00:00
就是表A的处理的表，表B是记录表，我需要在刷新时，取到处理表的记录，然后把最新的记录更新到表B中，表A的处理可能有多条不同时间的重复记录，但只保留最后一条
[此贴子已经被作者于2022/6/23 10:02:17编辑过]
-- 发布时间：2022/6/23 10:40:00
--
还是看不懂
此主题相关图片如下：1.png
-- 发布时间：2022/6/23 11:33:00
--
这个业务场景
取采购订单明细中最后一次采购价格及采购订单号信息
上面那个处理结果是表B的，写错了，写成表A的了
-- 发布时间：2022/6/23 12:06:00
--
Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"Dim dt As DataTable
Dim cmb As WinForm.ComboBox
cmd.CommandText = "SELECT a.* From {表A} as a inner join (select pn,max(approved) as approved from {表A} group by pn) as b on a.pn = b.pn where a.approved=b.approved"
dt = cmd.ExecuteReader()
Dim dt1 As DataTable = DataTables("表B")
Dim w As DataRow
For Each dr As Row In dt.Rows
w = dt1.Find("pn = \'" & dr("pn") & "\'")
If w is Nothing Then
w = dt1.addnew
w = dt1.Find("pn = \'" & dr("pn") & "\'")
If w is Nothing Then
w = dt1.addnew
w("pn") = dr("pn")
End If
w("po") = dr("po")
w("pi") = dr("pi")
w("approved") = dr("approved")
w("cost") = dr("cost")
End If
w("po") = dr("po")
w("pi") = dr("pi")
w("approved") = dr("approved")
w("cost") = dr("cost")
Next