程序处理完的数据以CSV的格式输出,当有1000多条记录时,生成一个文件只需2-3秒(文件大小88K),而当有3000条记录时要3分钟(文件大小223K),9000条时超过9分钟(文件大小744K)。
请问为什么会差别这么大,有什么可以提速的方法吗?谢谢!下面是生成CSV文件那个过程的代码:
Public Sub createCSVMatch(strfile As String, m As Long)
Dim str As String
Dim i As Long
Dim j As Long
str = str & "车辆ID" & "," & "年" & "," & "月" & "," & "日" & "," & "时" & "," & "分" & "," & "秒" & "," & "经度" & "," & "纬度" & "," & "方向" & ","
str = str & "投影匹配" & "," & "投影点1经度" & "," & "投影点1纬度" & "," & "二次网格号" & "," & "路链号" & "," & "终端距离1" & ","
str = str & "投影点2经度" & "," & "投影点2纬度" & "," & "二次网格号" & "," & "路链号" & "," & "终端距离2" & ","
str = str & "投影点3经度" & "," & "投影点3纬度" & "," & "二次网格号" & "," & "路链号" & "," & "终端距离3" & ","
str = str & "投影点4经度" & "," & "投影点4纬度" & "," & "二次网格号" & "," & "路链号" & "," & "终端距离4" & "," & vbCrLf
For i = 1 To m
str = str & matchPnts(i).car.carid & "," & Left(matchPnts(i).car.time, 4) & "," & Mid(matchPnts(i).car.time, 5, 2) & "," & Mid(matchPnts(i).car.time, 7, 2) & "," & _
Mid(matchPnts(i).car.time, 9, 2) & "," & Mid(matchPnts(i).car.time, 11, 2) & "," & Mid(matchPnts(i).car.time, 13, 2) & "," & matchPnts(i).car.lon & "," & matchPnts(i).car.lat & "," & matchPnts(i).car.direction & "," & matchPnts(i).num & ","
For j = 1 To UBound(matchPnts(i).prjs)
str = str & matchPnts(i).prjs(j).p.x & "," & matchPnts(i).prjs(j).p.y & "," & matchPnts(i).prjs(j).sec.gridid & "," & matchPnts(i).prjs(j).sec.linkid & "," & matchPnts(i).prjs(j).p.s & ","
Next j
str = Left(str, Len(str) - 1) & vbCrLf
Next i
Open strfile For Output As #1
Print #1, str
Close #1
End Sub
请问为什么会差别这么大,有什么可以提速的方法吗?谢谢!下面是生成CSV文件那个过程的代码:
Public Sub createCSVMatch(strfile As String, m As Long)
Dim str As String
Dim i As Long
Dim j As Long
str = str & "车辆ID" & "," & "年" & "," & "月" & "," & "日" & "," & "时" & "," & "分" & "," & "秒" & "," & "经度" & "," & "纬度" & "," & "方向" & ","
str = str & "投影匹配" & "," & "投影点1经度" & "," & "投影点1纬度" & "," & "二次网格号" & "," & "路链号" & "," & "终端距离1" & ","
str = str & "投影点2经度" & "," & "投影点2纬度" & "," & "二次网格号" & "," & "路链号" & "," & "终端距离2" & ","
str = str & "投影点3经度" & "," & "投影点3纬度" & "," & "二次网格号" & "," & "路链号" & "," & "终端距离3" & ","
str = str & "投影点4经度" & "," & "投影点4纬度" & "," & "二次网格号" & "," & "路链号" & "," & "终端距离4" & "," & vbCrLf
For i = 1 To m
str = str & matchPnts(i).car.carid & "," & Left(matchPnts(i).car.time, 4) & "," & Mid(matchPnts(i).car.time, 5, 2) & "," & Mid(matchPnts(i).car.time, 7, 2) & "," & _
Mid(matchPnts(i).car.time, 9, 2) & "," & Mid(matchPnts(i).car.time, 11, 2) & "," & Mid(matchPnts(i).car.time, 13, 2) & "," & matchPnts(i).car.lon & "," & matchPnts(i).car.lat & "," & matchPnts(i).car.direction & "," & matchPnts(i).num & ","
For j = 1 To UBound(matchPnts(i).prjs)
str = str & matchPnts(i).prjs(j).p.x & "," & matchPnts(i).prjs(j).p.y & "," & matchPnts(i).prjs(j).sec.gridid & "," & matchPnts(i).prjs(j).sec.linkid & "," & matchPnts(i).prjs(j).p.s & ","
Next j
str = Left(str, Len(str) - 1) & vbCrLf
Next i
Open strfile For Output As #1
Print #1, str
Close #1
End Sub
str =你可以把所有要连接的字符串放到一个字符串数组,然后用Join函数连接,效率会好一点
Private Sub Command1_Click()
Dim sb As CStringBuffer
Set sb = New CStringBuffer
sb.append "你好,"
sb.append "这个类是字符串缓冲类的演示,用于大量字符串的连接效率会高一些"
MsgBox sb.toString, vbInformation, "呵呵"
Set sb = Nothing
End Sub'类模块CStringBuffer代码
Option Explicit
Private mlngCapacity As Long '缓冲区的当前容量,可用于插入新的字符的存储空间
Private mlngLength As Long '字符个数
Private mlngEOA As Long '数组第一个空元素的索引
Private mabyt() As Byte
Private mlngUb As Long '数组上界Private Sub Class_Initialize()
ReDim mabyt(4095)
End SubPrivate Sub Class_Terminate()
Erase mabyt
End SubPublic Sub append(ByVal str As String)
Dim abyt() As Byte '添加的字符串转为Byte数组
Dim lngUb As Long '添加的字符串转为Byte数组的上界
Dim lngLength As Long '添加的字符串的长度
Dim i As Long
abyt = str
lngUb = UBound(abyt)
lngLength = (lngUb + 1) \ 2
Do While lngLength > mlngCapacity '插入新的字符超出缓冲区容量
mlngUb = mlngUb + 4096
ReDim mabyt(mlngUb) '缓冲区扩容
mlngCapacity = mlngCapacity + 2048
Loop
For i = 0 To lngUb
mabyt(mlngEOA + i) = abyt(i)
Next
mlngEOA = mlngEOA + lngUb + 1
mlngCapacity = mlngCapacity - lngLength
mlngLength = mlngLength + lngLength
Erase abyt
End SubPublic Property Get length() As Long
length = mlngLength
End PropertyPublic Property Get capacity() As Long
capacity = mlngCapacity
End PropertyPublic Function toString() As String
ReDim Preserve mabyt(mlngEOA - 1)
toString = mabyt
ReDim Preserve mabyt(mlngUb)
End Function
Class_Initialize里面加一句
mlngCapacity = 2048
Private Sub Class_Initialize()
mlngUb = 4095
ReDim mabyt(mlngUb)
mlngCapacity = 2048
End Sub