ado调用数据库防止sql语句过长的方法

ado 调用数据库 防止sql 语句过长的方法

有这样一种场景，用户输入零件编号，形成一个列表，然后根据这个列表从数据库取出所有的相关内容

如果一个一个查询，性能太差，
为了性能， 用批量查询

sql 语句： select * from table where part_no in ('a','b',.....'x')

如果这个列表太长，比如2万条数据，就会报错，

解决方法：
将列表进行分页，比如，以5000的长度分页，分成多个列表 (1,2,... 5000), (5001,... 10000)...

然后逐个查询分页后的列表数据， 再将结果合并， 两万条的数据，只需四次查询即可

vba 分页的测试代码，

Sub test()
Dim s As String
s = "'a','b','c','d','e','f','g','h','i'"
Dim b As Variant

b = Split(s, ",")

Dim c As Integer
Dim lenCount As Integer
c = 2

If ((UBound(b) + 1) Mod c) = 0 Then
lenCount = Int((UBound(b) + 1) / c)
Else
lenCount = Int((UBound(b) + 1) / c) + 1
End If

MsgBox lenCount

Dim startIndex
Dim endIndex

For i = 0 To lenCount - 1
startIndex = i * c
endIndex = (i + 1) * c - 1
If endIndex > UBound(b) Then
endIndex = UBound(b)
End If
MsgBox startIndex & "," & endIndex
Next

i = 0
Do While i <= lenCount - 1
startIndex = i * c
endIndex = (i + 1) * c - 1
If endIndex > UBound(b) Then
endIndex = UBound(b)
End If
MsgBox startIndex & "," & endIndex
i = i + 1
Loop

Do While i <= lenCount - 1
startIndex = i * c
endIndex = (i + 1) * c - 1
If endIndex > UBound(b) Then
endIndex = UBound(b)
End If
i = i + 1
Loop

End Sub