在请教一下老师，这代码怎么优化可以加快速度
Dim cmd1 As New SQLCommand
Dim dt1 As DataTable
cmd1.C
cmd1.CommandText = "S ELECT DISTINCT 所属机构,项目名称,合同金额 From {合同签订}"
dt1 = cmd1.ExecuteReader()
Dim cmd2 As New SQLCommand
Dim dt2 As DataTable
cmd2.C
cmd2.CommandText = "S ELECT DISTINCT 项目名称,开piao金额,登记日期 From {开piao申请}"
dt2 = cmd2.ExecuteReader()
Dim cmd3 As New SQLCommand
Dim dt3 As DataTable
cmd3.C
cmd3.CommandText = "S ELECT * FROM {进项piao录入}"
dt3 = cmd3.ExecuteReader()
Dim d As Integer = e.Form.Controls("所属年度").value
Dim m As Date = Date.Today
Dim i As Integer
Dim dtb As New DataTableBuilder("进项piao分年月统计")
dtb.AddDef("所属机构", Gettype(String), 32)
dtb.AddDef("项目名称", Gettype(String), 100)
dtb.AddDef("合同金额", Gettype(Double))
For i = 1 To m.Month
dtb.AddDef(d & "年" & i & "月_开piao金额" , Gettype(Double))
dtb.AddDef(d & "年" & i & "月_税价合计金额" , Gettype(Double))
dtb.AddDef(d & "年" & i & "月_可抵扣增值税" , Gettype(Double))
dtb.AddDef(d & "年" & i & "月_可抵扣成本" , Gettype(Double))
Next
dtb.Build() '生成统计表
For Each nm() As String In dt1.GetValues("所属机构|项目名称|合同金额")
Dim dr As DataRow = DataTables("进项piao分年月统计").AddNew()
dr("所属机构") = nm(0)
dr("项目名称") = nm(1)
dr("合同金额") = nm(2)
For i = 1 To m.Month
dr(d & "年" & i & "月_开piao金额") = dt2.Compute("Sum(开piao金额)","项目名称 = '" & nm(1) & "' And 登记日期 >= '" & d & "-" & i & "-01" & "' And 登记日期 <= '" & d & "-" & i & "-" & Date.DaysInMonth(d,i) & "'")
dr(d & "年" & i & "月_税价合计金额") = dt3.Compute("Sum(税价合计金额)","项目名称 = '" & nm(1) & "' And 寄达日期 >= '" & d & "-" & i & "-01" & "' And 寄达日期 <= '" & d & "-" & i & "-" & Date.DaysInMonth(d,i) & "'")
dr(d & "年" & i & "月_可抵扣增值税") = dt3.Compute("Sum(税额)","项目名称 = '" & nm(1) & "' And fa piao类别 = '专用' And 寄达日期 >= '" & d & "-" & i & "-01" & "' And 寄达日期 <= '" & d & "-" & i & "-" & Date.DaysInMonth(d,i) & "'")
dr(d & "年" & i & "月_可抵扣成本") = dr(d & "年" & i & "月_税价合计金额") - dr(d & "年" & i & "月_可抵扣增值税")
Next
Next
'MainTable= Tables("进项piao分年月统计")
Tables("进项piao分年月统计_Table1").DataSource = DataTables("进项piao分年月统计")
[此贴子已经被作者于2021/12/7 13:30:06编辑过]
加好友 
发短信
Post By：2021/12/7 9:33:00 [只看该作者]
