专家坐堂-SQL重复使用相同子查询如何优化Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  SQL重复使用相同子查询如何优化  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=189938)

--  作者：andy123
--  发布时间：2024/1/5 16:12:00
--  SQL重复使用相同子查询如何优化

Dim cmd As new SQ LCommand
cmd.C

For Each dr As Row In Tables("电子进度").Rows
cmd.CommandText = "S elect Count(DISTINCT SNCode) FROM {AutoTPJ} WHERE SNCode In (S elect 条形码 FROM {条形码记录表} WHERE 主键 = \'" & dr("主键") & "\')"
dr("t1") = cmd.ExecuteScalar()

cmd.CommandText = "S elect Count(DISTINCT 条形码) FROM {检验} WHERE 工序 = \'焊线\' And 条形码 In (S elect 条形码 FROM {条形码记录表} WHERE 主键 = \'" & dr("主键") & "\')"
dr("t2") = cmd.ExecuteScalar()

cmd.CommandText = "S elect Count(DISTINCT bh) FROM {wbSheet} WHERE bh In (S elect 条形码 FROM {条形码记录表} WHERE 主键 = \'" & dr("主键") & "\')"
dr("t3") = cmd.ExecuteScalar()

cmd.CommandText = "S elect Count(DISTINCT bh) FROM {Creep1} WHERE bh In (S elect 条形码 FROM {条形码记录表} WHERE 主键 = \'" & dr("主键") & "\')"
dr("t4") = cmd.ExecuteScalar()

cmd.CommandText = "S elect Count(DISTINCT SNCode) FROM {HistoryStoreData} WHERE SNCode In (S elect 条形码 FROM {条形码记录表} WHERE 主键 = \'" & dr("主键") & "\')"
dr("t5") = cmd.ExecuteScalar()
Next

上面的代码SQL子查询中重复使用了S elect 条形码 FROM {条形码记录表} WHERE 主键 = \'" & dr("主键")
请教如何优化可以提高效率？

[此贴子已经被作者于2024/1/5 16:14:19编辑过]

--  作者：有点蓝
--  发布时间：2024/1/5 16:25:00
--
操作的都是不同的表，无法优化

--  作者：andy123
--  发布时间：2024/1/5 16:36:00
--
如果将：S elect 条形码 FROM {条形码记录表} WHERE 主键 = \'" & dr("主键") 先执行，返回数据，再将数据合成为字符串s,是否可行呢？

Dim cmd As new SQ LCommand
cmd.C

For Each dr As Row In Tables("电子进度").Rows

Dim dt As DataTable
cmd.CommandText = "S elect 条形码 FROM {条形码记录表} WHERE 主键 = \'" & dr("主键") & "\')"
dt = cmd.ExecuteReader()
Dim kss As List(Of String)
Dim s As String
kss = dt.GetValues("主键")
For Each k As String In kss
s = s & "\'" & k & "\',"
Next
s = s.Remove(s.Length-1,1)

cmd.CommandText = "S elect Count(DISTINCT SNCode) FROM {AutoTPJ} WHERE SNCode In (‘”& s & "\')"
dr("t1") = cmd.ExecuteScalar()

cmd.CommandText = "S elect Count(DISTINCT 条形码) FROM {检验} WHERE 工序 = \'焊线\' And 条形码 In (‘”& s & "\')"
dr("t2") = cmd.ExecuteScalar()

cmd.CommandText = "S elect Count(DISTINCT bh) FROM {wbSheet} WHERE bh In (‘”& s & "\')"
dr("t3") = cmd.ExecuteScalar()

cmd.CommandText = "S elect Count(DISTINCT bh) FROM {Creep1} WHERE bh In (‘”& s & "\')"
dr("t4") = cmd.ExecuteScalar()

cmd.CommandText = "S elect Count(DISTINCT SNCode) FROM {HistoryStoreData} WHERE SNCode In (‘”& s & "\')"
dr("t5") = cmd.ExecuteScalar()
Next

[此贴子已经被作者于2024/1/5 16:40:14编辑过]

--  作者：有点蓝
--  发布时间：2024/1/5 16:40:00
--
代码没少多少，从效率老说，其实反而低了。

--  作者：andy123
--  发布时间：2024/1/5 16:45:00
--
好的，明白了，谢谢
500行的表，整个执行完一次要50秒左右，明间太长
现在准备直接在SQL服务器上60分钟定时执行一次数据更新算了

--  作者：有点蓝
--  发布时间：2024/1/5 17:22:00
--
"电子进度"和“条形码记录表”不在同一个数据库？同一个数据库一条sql就行，没有必要遍历所有行

--  作者：andy123
--  发布时间：2024/1/5 17:41:00
--

"电子进度"和“条形码记录表”
在同一个数据库，同一个数据库一条sql，代码怎么写呢？
[此贴子已经被作者于2024/1/5 17:41:48编辑过]

--  作者：有点蓝
--  发布时间：2024/1/6 9:20:00
--
cmd.CommandText = "Select Count(DISTINCT SNCode) FROM {AutoTPJ} WHERE SNCode In (Select 条形码 FROM {条形码记录表} as a inner join 电子进度 as b on a.主键=b.主键)"

如果Tables("电子进度").有筛选过，就这样
cmd.CommandText = "Select Count(DISTINCT SNCode) FROM {AutoTPJ} WHERE SNCode In (Select 条形码 FROM {条形码记录表} as a inner join 电子进度 as b on a.主键=b.主键 where " & Tables("电子进度").filter & ")"