mysql - 转置 GetRows 中的行列

Question

下面的 VBA 代码完成了这项工作，但我在转置部分损失了大约 3 秒。

有没有一种方法可以在不损失 3 秒的情况下在 SQL 查询或 getrows 过程中获得相同的结果？

Sub LoadData()
    Dim strCon, srtQry As String, tmpArray, tmpArray2, R As Variant, i, j As Long

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    strCon = "DRIVER={MySQL ODBC 5.2 ANSI Driver};" & _
            "SERVER=localhost;" & _
            "DATABASE=tbname;" & _
            "USER=root;" & _
            "PASSWORD=pass;" & _
            "Port=3306;" & _
            "Option=3"
    
    cn.Open strCon

    srtQry = "SELECT * FROM `tbname` WHERE `FileDay` = 20131220"
    
    Set rs = cn.Execute(srtQry)
    
    tmpArray = rs.GetRows
     
    cn.Close
     
    tmpArray2 = TransposeArray(tmpArray)
     
End Sub

转置数组:

Public Function TransposeArray(InputArr As Variant) As Variant

    Dim RowNdx, ColNdx, LB1, LB2, UB1, UB2 As Long, tmpArray As Variant

    LB1 = LBound(InputArr, 1)
    LB2 = LBound(InputArr, 2)
    UB1 = UBound(InputArr, 1)
    UB2 = UBound(InputArr, 2)

    ReDim tmpArray(LB2 To LB2 + UB2 - LB2, LB1 To LB1 + UB1 - LB1)

    For RowNdx = LB2 To UB2
        For ColNdx = LB1 To UB1
            tmpArray(RowNdx, ColNdx) = InputArr(ColNdx, RowNdx)
        Next ColNdx
    Next RowNdx

    TransposeArray = tmpArray

End Function

Answer 1

您可以应用一些优化

1. 声明:需要指定每个变量的数据类型
2. 去除Redim中的冗余计算
3. 使用更紧凑的For循环结构
4. 将您的变体指定为数组
5. 为了获得最大的影响:使用 Sub 而不是 Function

这些加在一起将使 Transpose 的运行时间减少 50% 以上

Public Sub TransposeArray(ByRef InputArr() As Variant, ByRef ReturnArray() As Variant)
    Dim RowNdx As Long, ColNdx As Long
    Dim LB1 As Long, LB2 As Long, UB1 As Long, UB2 As Long

    LB1 = LBound(InputArr, 1)
    LB2 = LBound(InputArr, 2)
    UB1 = UBound(InputArr, 1)
    UB2 = UBound(InputArr, 2)

    ReDim ReturnArray(LB2 To UB2, LB1 To UB1)

    For RowNdx = LB2 To UB2
    For ColNdx = LB1 To UB1
        ReturnArray(RowNdx, ColNdx) = InputArr(ColNdx, RowNdx)
    Next ColNdx, RowNdx

End Sub

这样称呼

TransposeArray tmpArray, tmpArray2