SQL语句优化？能帮提提速吗？感觉有点慢！

jygzcj

Sub POInsertIntoSQLServerAndUpdateOldPO()
   
   
    On Error GoTo ErrHandle
   
    If MsgBox("请注意不可以更新之前的记录，只能更新最新的订单记录，当天的记录可以多次更新都没有关系", vbYesNo + vbInformation, "提示") = vbNo Then
        Exit Sub
    End If
   
    Dim i As Long
   
    Dim LastRow As Long
   
    Dim Arr()
   
    Dim POSht As Worksheet
    Set POSht = Sheet5
   
    With POSht
        If .FilterMode Then
            .ShowAllData
        End If
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        If LastRow < 2 Then
            MsgBox "没有需要更新数据，请确认是否已经导入PO清单", vbInformation
            Exit Sub
        End If
        
        Arr = .Range(.Cells(1, 1), .Cells(LastRow, 23))
    End With
   
   
    Dim Cnn As Object
    Set Cnn = CreateObject("adodb.connection")
   
    Dim Rs As Object
    Set Rs = CreateObject("adodb.recordset")
   
    With Cnn
        .Provider = SQLConnectionStr
        .Open
    End With
    Dim Sql As String
   
   
    Cnn.begintrans

    For i = 2 To UBound(Arr)
        Sql = "select 采购订单 from PORecord where 采购订单=N'" & Trim(Arr(i, 1)) & "' and 序号=N'" & Trim(Arr(i, 8)) & "'"
        Rs.Open Sql, Cnn, 1, 3
        If Rs.EOF Then
                Sql = "insert into PORecord (采购订单,供应商代码,供应商名称,工厂,工厂名称,订货时间,创建时间,序号,零件属性,零件号,零件名称,数量,更新后数量,实收数量,更新后实收数量,单位,交货日期,交货地点,项目号,预计到货时间1,预计到货数量1,预计到货时间2,预计到货数量2,备注,供应商备注,最后更新人员,最后更新日期)" _
                & "values (N'" & Trim(Arr(i, 1)) & "',N'" & Trim(Arr(i, 2)) & "',N'" & Trim(Arr(i, 3)) & "',N'" & Trim(Arr(i, 4)) & "',N'" & Trim(Arr(i, 5)) & "',N'" & Trim(Arr(i, 6)) & "',N'" & Trim(Arr(i, 7)) & "',N'" & Trim(Arr(i, 8)) & "',N'" & Trim(Arr(i, 9)) & "',N'" & Trim(Arr(i, 10)) & "',N'" & Trim(Arr(i, 11)) & "',N'" & Trim(Arr(i, 12)) & "','0',N'" & Trim(Arr(i, 13)) & "','0',N'" & Trim(Arr(i, 14)) & "',N'" & Trim(Arr(i, 15)) & "',N'" & Trim(Arr(i, 16)) & "',N'" & Trim(Arr(i, 17)) & "',N'" & Trim(Arr(i, 18)) & "',N'" & Trim(Arr(i, 19)) & "',N'" & Trim(Arr(i, 20)) & "',N'" & Trim(Arr(i, 21)) & "',N'" & Trim(Arr(i, 22)) & "',N'" & Trim(Arr(i, 23)) & "',N'" & UCase(Application.UserName) & "',N'" & Format(Now, "yyyy/mm/dd hh:mm:ss") & "') "
            Else
                Sql = "update PORecord set 更新后数量=N'" & Trim(Arr(i, 12)) & "' , 更新后实收数量=N'" & Trim(Arr(i, 13)) & "' , 最后更新人员=N'" & UCase(Application.UserName) & "' , 最后更新日期=N'" & Format(Now, "yyyy/mm/dd hh:mm:ss") & "' where 采购订单=N'" & Trim(Arr(i, 1)) & "' and 序号=N'" & Trim(Arr(i, 8)) & "'"
        End If
        Rs.Close
        Cnn.Execute (Sql)
    Next
   
    Cnn.Committrans
         
   
    Set Rs = Nothing
   
    Cnn.Close
    Set Cnn = Nothing
   
    MsgBox "订单已经更新完成!", vbInformation
   
    Exit Sub
   
ErrHandle:
   
    MsgBox "订单更新过程出错，没有任何数据被更新，可能的原因是数据格式不准确", vbInformation
   
   
End Sub

MR_PP

1.批量处理：在每次循环中构建完整的SQL语句并执行，减少了对数据库的多次连接。
2.减少Recordset使用：直接使用Cnn.Execute来检查记录是否存在。
3.事务处理：确保在事务中进行所有的插入和更新操作，以提高效率和数据一致性。

1. Sub POInsertIntoSQLServerAndUpdateOldPO()
   
2.     On Error GoTo ErrHandle
   
3. 
   
4.     If MsgBox("请注意不可以更新之前的记录，只能更新最新的订单记录，当天的记录可以多次更新都没有关系", vbYesNo + vbInformation, "提示") = vbNo Then
   
5.         Exit Sub
   
6.     End If
   
7. 
   
8.     Dim i As Long
   
9.     Dim LastRow As Long
   
10.     Dim Arr()
    
11.     Dim POSht As Worksheet
    
12.     Set POSht = Sheet5
    
13. 
    
14.     With POSht
    
15.         If .FilterMode Then
    
16.             .ShowAllData
    
17.         End If
    
18.         LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
19.         If LastRow < 2 Then
    
20.             MsgBox "没有需要更新数据，请确认是否已经导入PO清单", vbInformation
    
21.             Exit Sub
    
22.         End If
    
23.         Arr = .Range(.Cells(1, 1), .Cells(LastRow, 23))
    
24.     End With
    
25. 
    
26.     Dim Cnn As Object
    
27.     Set Cnn = CreateObject("adodb.connection")
    
28.     Cnn.Open SQLConnectionStr
    
29. 
    
30.     Dim SqlInsert As String
    
31.     Dim SqlUpdate As String
    
32.     Dim SqlSelect As String
    
33.     Dim InsertValues As String
    
34.     Dim UpdateValues As String
    
35. 
    
36.     Cnn.BeginTrans
    
37. 
    
38.     For i = 2 To UBound(Arr)
    
39.         SqlSelect = "SELECT 采购订单 FROM PORecord WHERE 采购订单=N'" & Trim(Arr(i, 1)) & "' AND 序号=N'" & Trim(Arr(i, 8)) & "'"
    
40.         If Cnn.Execute(SqlSelect).EOF Then
    
41.             InsertValues = "(N'" & Trim(Arr(i, 1)) & "',N'" & Trim(Arr(i, 2)) & "',N'" & Trim(Arr(i, 3)) & "',N'" & Trim(Arr(i, 4)) & "',N'" & Trim(Arr(i, 5)) & "',N'" & Trim(Arr(i, 6)) & "',N'" & Trim(Arr(i, 7)) & "',N'" & Trim(Arr(i, 8)) & "',N'" & Trim(Arr(i, 9)) & "',N'" & Trim(Arr(i, 10)) & "',N'" & Trim(Arr(i, 11)) & "',N'" & Trim(Arr(i, 12)) & "','0',N'" & Trim(Arr(i, 13)) & "','0',N'" & Trim(Arr(i, 14)) & "',N'" & Trim(Arr(i, 15)) & "',N'" & Trim(Arr(i, 16)) & "',N'" & Trim(Arr(i, 17)) & "',N'" & Trim(Arr(i, 18)) & "',N'" & Trim(Arr(i, 19)) & "',N'" & Trim(Arr(i, 20)) & "',N'" & Trim(Arr(i, 21)) & "',N'" & Trim(Arr(i, 22)) & "',N'" & Trim(Arr(i, 23)) & "',N'" & UCase(Application.UserName) & "',N'" & Format(Now, "yyyy/mm/dd hh:mm:ss") & "')"
    
42.             SqlInsert = "INSERT INTO PORecord (采购订单,供应商代码,供应商名称,工厂,工厂名称,订货时间,创建时间,序号,零件属性,零件号,零件名称,数量,更新后数量,实收数量,更新后实收数量,单位,交货日期,交货地点,项目号,预计到货时间1,预计到货数量1,预计到货时间2,预计到货数量2,备注,供应商备注,最后更新人员,最后更新日期) VALUES " & InsertValues
    
43.             Cnn.Execute SqlInsert
    
44.         Else
    
45.             UpdateValues = "更新后数量=N'" & Trim(Arr(i, 12)) & "', 更新后实收数量=N'" & Trim(Arr(i, 13)) & "', 最后更新人员=N'" & UCase(Application.UserName) & "', 最后更新日期=N'" & Format(Now, "yyyy/mm/dd hh:mm:ss") & "'"
    
46.             SqlUpdate = "UPDATE PORecord SET " & UpdateValues & " WHERE 采购订单=N'" & Trim(Arr(i, 1)) & "' AND 序号=N'" & Trim(Arr(i, 8)) & "'"
    
47.             Cnn.Execute SqlUpdate
    
48.         End If
    
49.     Next
    
50. 
    
51.     Cnn.CommitTrans
    
52.     Cnn.Close
    
53.     Set Cnn = Nothing
    
54. 
    
55.     MsgBox "订单已经更新完成!", vbInformation
    
56.     Exit Sub
    
57. 
    
58. ErrHandle:
    
59.     MsgBox "订单更新过程出错，没有任何数据被更新，可能的原因是数据格式不准确", vbInformation
    
60. End Sub
    

复制代码

WANT-T

SQL语句没有啥可优化的

opiona

ADO将百万行Excel数据快速上传到MSSQL或MySQL数据库中 仅需几十秒

https://club.excelhome.net/thread-1666858-1-1.html

opiona

当前数据一般是Excel内数据, 或是数组内容
直接更新速度极慢

更新数据库的思路:
将当前数据上传到数据库的一个临时表(使用DLL)
再用临时表修改数据库对应数据,会快很多
数据越多, 越明显

zpy2

Cnn.Execute (Sql)
    Next

这里执行多次会慢，
可以用一句插入

insert into tbl values (v1,v2),(v2,v3)
或者存储过程

jygzcj

zpy2 发表于 2024-10-9 10:19
Cnn.Execute (Sql)
    Next

我要先检查数据内容是否存在，如果存在就更新某个字段的数据，如果不存在再执行插入，有没有更好的办法呀？

zpy2

jygzcj 发表于 2024-10-13 11:07
我要先检查数据内容是否存在，如果存在就更新某个字段的数据，如果不存在再执行插入，有没有更好的办法呀 ...

你现在的问题是循环，对带宽要求太高，我是在服务端写脚本比对的，你如果是2层架构，需要写存储过程处理一下，不能连续发送数据，应该一次性的发送提交数据。

jygzcj

opiona 发表于 2024-10-9 09:27
当前数据一般是Excel内数据, 或是数组内容
直接更新速度极慢

使用Dll上传到SQL Server上面的临时表不也是excel里面的数据上传到数据库的表中吗？你是说先不管是否存在，先统一上传了了，然后数据库中的表与表之间进行检查，没有的再插入表中，有的则更新是吗？我多次看过你的关于批量上传的贴子，但是我们办公室电脑没有管理员权限装不了无法使用的。有没有别的更好的思路呀？用ADO.net的技术做成的DLL适当用Excel上传吗？数据是否需要加工？

zpy2

zpy2 发表于 2024-10-13 11:09
你现在的问题是循环，对带宽要求太高，我是在服务端写脚本比对的，你如果是2层架构，需要写存储过程处理 ...

将当前数据上传到数据库的一个临时表(使用DLL)
再用临时表修改数据库对应数据,会快很多
数据越多, 越明显

5楼也是一种思路