如何提高速度呀有代码（数据库）？

[xiongying18]

下面的代码速度慢的要命，如何能快点
comm1.CommandText = "SELECT 类型,日期,备刀份数,备刀人员,操作次数,新,一次,二次,三次,四次,五次,断新,断一,断二,断三,断四,断五,没新,没一,没二,没三,没四,没五 from ZT_DATA_History where 类型=1 or 类型=2 or 类型=3 or 类型=4 or 类型=5 or 类型=6 or 类型=7"
reader = comm1.ExecuteReader
Do While reader.Read
sl = reader("新") + reader("一次") + reader("二次") + reader("三次") + reader("四次") + reader("五次")
dd = reader("断新") + reader("断一") + reader("断二") + reader("断三") + reader("断四") + reader("断五")
mysl = reader("没新") + reader("没一") + reader("没二") + reader("没三") + reader("没四") + reader("没五")

DateTimeTEMP = reader("日期")
Dim xh As Integer = DateTimeTEMP.Hour
Dim fz As Integer = DateTimeTEMP.Minute
If xh < 7 Or (xh < 8 And fz < 30) Then '当时间小于７：３０时候将日期减１
DateTimeTEMP = DateTimeTEMP.AddDays(-1)
End If

comm2.CommandText = "SELECT ID from ZT_beidao_TJ where 日期=#" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "# and ID='" & reader("备刀人员") & "'"
reader2 = comm2.ExecuteReader
Do While reader2.Read
bdr = reader2("ID")
Loop
reader2.Close()

Select Case reader("类型")
Case 1
If bdr = "" Then
comm2.CommandText = "insert into ZT_beidao_TJ(日期,ID,备刀份数,备刀次数,备刀数量) values('" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "','" & reader("备刀人员") & "'," & reader("备刀份数") & "," & reader("操作次数") & "," & sl & ")"
comm2.ExecuteNonQuery()
Else
comm2.CommandText = "Update ZT_beidao_TJ set 备刀份数=备刀份数+" & reader("备刀份数") & ",备刀次数=备刀次数+" & reader("操作次数") & ",备刀数量=备刀数量+" & sl & " where 日期=#" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "# and ID='" & reader("备刀人员") & "'"
comm2.ExecuteNonQuery()
End If
Case 2
If bdr = "" Then
comm2.CommandText = "insert into ZT_beidao_TJ(日期,ID,填份份数,填份次数,填份数量) values('" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "','" & reader("备刀人员") & "'," & reader("备刀份数") & "," & reader("操作次数") & "," & sl & ")"
comm2.ExecuteNonQuery()
Else
comm2.CommandText = "Update ZT_beidao_TJ set 填份份数=填份份数+" & reader("备刀份数") & ",填份次数=填份次数+" & reader("操作次数") & ",填份数量=填份数量+" & sl & " where 日期=#" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "# and ID='" & reader("备刀人员") & "'"
comm2.ExecuteNonQuery()
End If
Case 3
If bdr = "" Then
comm2.CommandText = "insert into ZT_beidao_TJ(日期,ID,填加次数,填加数量) values('" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "','" & reader("备刀人员") & "'," & reader("操作次数") & "," & sl & ")"
comm2.ExecuteNonQuery()
Else
comm2.CommandText = "Update ZT_beidao_TJ set 填加次数=填加次数+" & reader("操作次数") & ",填加数量=填加数量+" & sl & " where 日期=#" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "# and ID='" & reader("备刀人员") & "'"
comm2.ExecuteNonQuery()
End If
Case 4
If bdr = "" Then
comm2.CommandText = "insert into ZT_beidao_TJ(日期,ID,完出次数,完出数量) values('" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "','" & reader("备刀人员") & "'," & reader("操作次数") & "," & sl & ")"
comm2.ExecuteNonQuery()
Else
comm2.CommandText = "Update ZT_beidao_TJ set 完出次数=完出次数+" & reader("操作次数") & ",完出数量=完出数量+" & sl & " where 日期=#" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "# and ID='" & reader("备刀人员") & "'"
comm2.ExecuteNonQuery()
End If
Case 5
If bdr = "" Then
comm2.CommandText = "insert into ZT_beidao_TJ(日期,ID,出库次数,出库数量) values('" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "','" & reader("备刀人员") & "'," & reader("操作次数") & "," & sl & ")"
comm2.ExecuteNonQuery()
Else
comm2.CommandText = "Update ZT_beidao_TJ set 出库次数=出库次数+" & reader("操作次数") & ",出库数量=出库数量+" & sl & " where 日期=#" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "# and ID='" & reader("备刀人员") & "'"
comm2.ExecuteNonQuery()
End If
Case 6
If bdr = "" Then
comm2.CommandText = "insert into ZT_beidao_TJ(日期,ID,完返次数,完返数量,完返断刀,完返没用) values('" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "','" & reader("备刀人员") & "'," & reader("操作次数") & "," & sl & "," & dd & "," & mysl & ")"
comm2.ExecuteNonQuery()
Else
comm2.CommandText = "Update ZT_beidao_TJ set 完返次数=完返次数+" & reader("操作次数") & ",完返数量=完返数量+" & sl & ",完返断刀=完返断刀+" & dd & ",完返没用=完返没用+" & mysl & " where 日期=#" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "# and ID='" & reader("备刀人员") & "'"
comm2.ExecuteNonQuery()
End If
Case 7
If bdr = "" Then
comm2.CommandText = "insert into ZT_beidao_TJ(日期,ID,返库次数,返库数量,返库断刀,返库没用) values('" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "','" & reader("备刀人员") & "'," & reader("操作次数") & "," & sl & "," & dd & "," & mysl & ")"
comm2.ExecuteNonQuery()
Else
comm2.CommandText = "Update ZT_beidao_TJ set 返库次数=返库次数+" & reader("操作次数") & ",返库数量=返库数量+" & sl & ",返库断刀=返库断刀+" & dd & ",返库没用=返库没用+" & mysl & " where 日期=#" & DateTimeTEMP.Year & "-" & DateTimeTEMP.Month & "-" & DateTimeTEMP.Day & "# and ID='" & reader("备刀人员") & "'"
comm2.ExecuteNonQuery()
End If
End Select
bdr = ""
Loop
reader.Close()

[pupstar]

写一个方法，传参数吧

[xiebinren]

不慢才怪！
数据库表结构不规范
自己可以做一个数据库操作类，
也可以写存储过程，传标志进去执行。

[xiongying18]

group by 类型,日期,备刀份数,备刀人员,操作次数 "

起什么作用呀

[chuzhaowei]

用OWC制作Excel中的柱形图、饼形图、折线图的源代码，绝对经典

http://www.it846.com/chart

[xiongying18]

"SELECT 类型,日期,备刀人员,Sum([备刀份数]) as 备刀份数,Sum([操作次数]) as 操作次数,Sum([新])+Sum([一次])+Sum([二次])+Sum([三次])+Sum([四次])+Sum([五次]) as 使用,Sum([断新])+Sum([断一])+Sum([断二])+Sum([断三])+Sum([断四])+Sum([断五]) as 断刀,Sum([没新])+Sum([没一])+Sum([没二])+Sum([没三])+Sum([没四])+Sum([没五]) as 没用 from ZT_DATA_History where 类型 in (1,2,3,4,5,6,7) group by 类型,日期,备刀人员"

[chuxue1342]

你的语句写得也太麻烦.如:
类型=1 or 类型=2 or 类型=3 or 类型=4 or 类型=5 or 类型=6 or 类型=7"
可以写成:类型 in (1,2,3,4,5,6,7)
还有.像这种:
sl = reader("新") + reader("一次") + reader("二次") + reader("三次") + reader("四次") + reader("五次")
完全可以在查询的时候计算好啊.就不用查询出这么多列了!!

还有就是注意使用索引+视图进行操作!

[jinmingji]

数据库结构改以下

[xiongying18]

是这样吗？视图是什么呀，怎么使用，我是新手不会呀
comm1.CommandText = "SELECT 类型,日期,备刀份数,备刀人员,操作次数,Sum([新])+Sum([一次])+Sum([二次])+Sum([三次])+Sum([四次])+Sum([五次]),Sum([断新])+Sum([断一])+Sum([断二])+Sum([断三])+Sum([断四])+Sum([断五]),Sum([没新])+Sum([没一])+Sum([没二])+Sum([没三])+Sum([没四])+Sum([没五]) from ZT_DATA_History where 类型 in (1,2,3,4,5,6,7)"

[chuxue1342]

你的下面的语句改一下:
"SELECT 类型,日期,备刀份数,备刀人员,操作次数,Sum([新])+Sum([一次])+Sum([二次])+Sum([三次])+Sum([四次])+Sum([五次]),Sum([断新])+Sum([断一])+Sum([断二])+Sum([断三])+Sum([断四])+Sum([断五]),Sum([没新])+Sum([没一])+Sum([没二])+Sum([没三])+Sum([没四])+Sum([没五]) from ZT_DATA_History where 类型 in (1,2,3,4,5,6,7)
group by 类型,日期,备刀份数,备刀人员,操作次数 "

视图你在数据里面建.用的SQL语句新建视图,然后查询这个视图就行了!!
还要注意一点如:
Sum([新])+Sum([一次])+Sum([二次])+Sum([三次])+Sum([四次])+Sum([五次]) as 总次数
给一个新列名"总次数",方便程序调用!