共8 条记录, 每页显示 10 条, 页签: [1]

标题：SQL重复使用相同子查询如何优化

1楼

andy123 发表于：2024/1/5 16:12:00

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()

上面的代码SQL子查询中重复使用了S elect 条形码 FROM {条形码记录表} WHERE 主键 = '" & dr("主键")

请教如何优化可以提高效率？

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

2楼

有点蓝 发表于：2024/1/5 16:25:00

操作的都是不同的表，无法优化

3楼

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 & "',"

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()

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

4楼

有点蓝 发表于：2024/1/5 16:40:00

代码没少多少，从效率老说，其实反而低了。

5楼

andy123 发表于：2024/1/5 16:45:00

好的，明白了，谢谢

500行的表，整个执行完一次要50秒左右，明间太长

现在准备直接在SQL服务器上60分钟定时执行一次数据更新算了

6楼

有点蓝 发表于：2024/1/5 17:22:00

"电子进度"和“条形码记录表”不在同一个数据库？同一个数据库一条sql就行，没有必要遍历所有行

7楼

andy123 发表于：2024/1/5 17:41:00

"电子进度"和“条形码记录表”

在同一个数据库，同一个数据库一条sql，代码怎么写呢？

[此贴子已经被作者于2024/1/5 17:41:48编辑过]

8楼

有点蓝 发表于：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 & ")"

共8 条记录, 每页显示 10 条, 页签: [1]