這樣一段代碼,竟然執行了30多分鐘,實在是受不了...拿出來讓高手解決一下..
謝謝各位大俠!!
Set adoPrimaryRS1 = New Recordset
adoPrimaryRS1.Open "select * from DD_CP_PD ", DB, adOpenStatic, adLockOptimistic
DB.Execute "DELETE FROM DD_CP_PD_TEMP "
If Not (adoPrimaryRS1.EOF And adoPrimaryRS1.BOF) Then
adoPrimaryRS1.MoveFirst Do While adoPrimaryRS1.EOF = False
If Not IsNull(adoPrimaryRS1!客戶) Then
Dim R As Long
For R = 1 To 30
If adoPrimaryRS1.Fields("JCS" & Trim(CStr(R))) <> 0 Or adoPrimaryRS1.Fields("JS" & Trim(CStr(R))) <> 0 Or adoPrimaryRS1.Fields("CS" & Trim(CStr(R))) <> 0 Or adoPrimaryRS1.Fields("ZS" & Trim(CStr(R))) <> 0 Then
SQL = "insert into DD_CP_PD_TEMP(客戶,倉庫,訂單號,型体,顏色,商標,規格,結存,進庫,出庫,裝箱) " & _
"values('" & adoPrimaryRS1!客戶 & "','" & adoPrimaryRS1!倉庫 & "','" & adoPrimaryRS1!訂單號 & "'," & _
"'" & adoPrimaryRS1!型体 & "','" & adoPrimaryRS1!顏色 & "','" & adoPrimaryRS1!商標 & "','S" & Trim(CStr(R)) & "'," & _
"" & adoPrimaryRS1.Fields("JCS" & Trim(CStr(R))) & "," & adoPrimaryRS1.Fields("JS" & Trim(CStr(R))) & "," & adoPrimaryRS1.Fields("CS" & Trim(CStr(R))) & "," & adoPrimaryRS1.Fields("ZS" & Trim(CStr(R))) & ")"
DB.Execute SQL
End If
Next R
SQL = " UPDATE DD_CP_PD_TEMP SET DD_CP_PD_TEMP.S碼=DD_XT_SIZE_TEMP.S碼 FROM DD_CP_PD_TEMP,DD_XT_SIZE_TEMP WHERE DD_CP_PD_TEMP.規格=DD_XT_SIZE_TEMP.規格 AND DD_CP_PD_TEMP.型体=DD_XT_SIZE_TEMP.型体"
DB.Execute SQL
SQL = "UPDATE DD_CP_PD_TEMP SET 庫存=結存+進庫-出庫"
DB.Execute SQL
DB.Execute "UPDATE DD_CP_PD_TEMP SET 裝箱=1 WHERE 裝箱=0"
SQL = "UPDATE DD_CP_PD_TEMP SET 件數=CEILING(庫存/裝箱) "
DB.Execute SQL
End If
adoPrimaryRS1.MoveNext
Loop
End If
謝謝各位大俠!!
Set adoPrimaryRS1 = New Recordset
adoPrimaryRS1.Open "select * from DD_CP_PD ", DB, adOpenStatic, adLockOptimistic
DB.Execute "DELETE FROM DD_CP_PD_TEMP "
If Not (adoPrimaryRS1.EOF And adoPrimaryRS1.BOF) Then
adoPrimaryRS1.MoveFirst Do While adoPrimaryRS1.EOF = False
If Not IsNull(adoPrimaryRS1!客戶) Then
Dim R As Long
For R = 1 To 30
If adoPrimaryRS1.Fields("JCS" & Trim(CStr(R))) <> 0 Or adoPrimaryRS1.Fields("JS" & Trim(CStr(R))) <> 0 Or adoPrimaryRS1.Fields("CS" & Trim(CStr(R))) <> 0 Or adoPrimaryRS1.Fields("ZS" & Trim(CStr(R))) <> 0 Then
SQL = "insert into DD_CP_PD_TEMP(客戶,倉庫,訂單號,型体,顏色,商標,規格,結存,進庫,出庫,裝箱) " & _
"values('" & adoPrimaryRS1!客戶 & "','" & adoPrimaryRS1!倉庫 & "','" & adoPrimaryRS1!訂單號 & "'," & _
"'" & adoPrimaryRS1!型体 & "','" & adoPrimaryRS1!顏色 & "','" & adoPrimaryRS1!商標 & "','S" & Trim(CStr(R)) & "'," & _
"" & adoPrimaryRS1.Fields("JCS" & Trim(CStr(R))) & "," & adoPrimaryRS1.Fields("JS" & Trim(CStr(R))) & "," & adoPrimaryRS1.Fields("CS" & Trim(CStr(R))) & "," & adoPrimaryRS1.Fields("ZS" & Trim(CStr(R))) & ")"
DB.Execute SQL
End If
Next R
SQL = " UPDATE DD_CP_PD_TEMP SET DD_CP_PD_TEMP.S碼=DD_XT_SIZE_TEMP.S碼 FROM DD_CP_PD_TEMP,DD_XT_SIZE_TEMP WHERE DD_CP_PD_TEMP.規格=DD_XT_SIZE_TEMP.規格 AND DD_CP_PD_TEMP.型体=DD_XT_SIZE_TEMP.型体"
DB.Execute SQL
SQL = "UPDATE DD_CP_PD_TEMP SET 庫存=結存+進庫-出庫"
DB.Execute SQL
DB.Execute "UPDATE DD_CP_PD_TEMP SET 裝箱=1 WHERE 裝箱=0"
SQL = "UPDATE DD_CP_PD_TEMP SET 件數=CEILING(庫存/裝箱) "
DB.Execute SQL
End If
adoPrimaryRS1.MoveNext
Loop
End If
請各位大俠給個意見優化一下...謝謝!
DB.Execute SQL
SQL = "UPDATE DD_CP_PD_TEMP SET 庫存=結存+進庫-出庫"
DB.Execute SQL
DB.Execute "UPDATE DD_CP_PD_TEMP SET 裝箱=1 WHERE 裝箱=0"
SQL = "UPDATE DD_CP_PD_TEMP SET 件數=CEILING(庫存/裝箱) "
DB.Execute SQL
这几个SQL可以放在循环外面呀,因为里面根本没有需要的循环里面的条件呀.UPDATE操作也是很花时间的哦
select 时尽量不用*,而把需要的字段列出
2.
尽量减少UPDATE的次数,
3.
If Not IsNull(adoPrimaryRS1!客戶) Then 这个判断可以放查询条件里
因为你是找客户不为空的记录,这样可以减少循环次数
能否这样处理,先把客户!NULL的记录按你的格式全部添加到数据库中,然后再根据指定条件删除他们。这样可以避免很慢的If语句
2、避免在循环里面使用open和close
3、尽量用update from 表 set 字段=值
4、最后,在使用一条语句时,特别注意:如果数据量很大,查看数据库连接超时设置,别到时程序运行出现超时错误。(因为我参经发生过这种错误:5、6万条数据,两个表连接)