speed up fill listview

sal21 · March 29th, 2009, 09:00 AM

Here my code to fill listview.
When the rset are a few the code work fats but with 154.478 rset the code use a very very time, have asuggestion?
A frined suggest me to loop all rset and fill array next loop the array to fill listview. Other frind suggest to use rsetrow, pther friend suggest use geststring rset...
I dont know the right way....

Code:

Sub FILL_LISTVIEW_2()
'SI
    Dim TOT As Double, TOT1 As Double, CONTA_RECORD As Long, TOT2 As Double
    Dim X

    On Error GoTo errore

    CONTA_REC = Empty

    strSQL1 = "SELECT PROVA2,PROVA1,PROVA3,PROVA9,PROVA11,PROVA12,PROVA17,PROVA13,PROVA14,PROVA18  FROM DATI WHERE PROVA16 = '" & Left(Me.COMBO_AREA.Text, 4) & "' ORDER BY PROVA2"
    Set RSSQLD = CNSQL.Execute(strSQL1)

    While Not RSSQLD.EOF
        CONTA_REC = CONTA_REC + 1
        RSSQLD.MoveNext
    Wend

    RIGA = Empty

    'Me.ProgressBar.Visible = True
    'Me.ProgressBar.value = Empty

    Me.ListView.Visible = False
    Me.ListView.ListItems.Clear
    LockWindow (SELEZIONE.hwnd)

    With RSSQLD

        RSSQLD.MoveFirst

        While (Not .EOF)

            Set X = ListView.ListItems.Add(, , .Fields!PROVA2)
            X.SubItems(1) = .Fields!PROVA1
            X.SubItems(2) = .Fields!PROVA3
            X.SubItems(3) = .Fields!PROVA9
            X.SubItems(4) = .Fields!PROVA11
            X.SubItems(5) = .Fields!PROVA12

            If IsNull(.Fields!PROVA17) Then
                X.SubItems(6) = Format(0, "#,##0.00")
                'Debug.Print .Fields!PROVA17
            Else
                X.SubItems(6) = Format(.Fields!PROVA17, "#,##0.00")
                TOT = TOT + X.SubItems(6)
            End If

            If IsNull(.Fields!PROVA13) Then
                X.SubItems(7) = Format(0, "#,##0.00")
            Else
                X.SubItems(7) = Format(.Fields!PROVA13, "#,##0.00")
                TOT1 = TOT1 + X.SubItems(7)
            End If

            If IsNull(.Fields!PROVA14) Then
                X.SubItems(8) = Format(0, "#,##0.00")
                'Debug.Print .Fields!PROVA14
            Else
                X.SubItems(8) = Format(.Fields!PROVA14, "#,##0.00")
                TOT2 = TOT2 + X.SubItems(8)
            End If

            If Not IsNull(.Fields!PROVA18) Then
                X.SubItems(9) = Format(.Fields!PROVA18, "#,##0")
            Else
                X.SubItems(9) = Format(0, "#,##0")
            End If

            RIGA = RIGA + 1

            .MoveNext

            'DoEvents
            'Me.ProgressBar.value = (RIGA / CONTA_REC) * 100

        Wend

        Me.ListView.Visible = True
        UnlockWindow

        'AltLVBackground ListView, vbWhite, &HC0FFFF
'Call fill_alternate
        'SetAlternateItemColours

        'Me.ProgressBar.Visible = False

    End With

    Me.Label4.Caption = Format(TOT1, "#,##0.00")
    Me.Label9.Caption = Format(TOT, "#,##0.00")
    Me.Label14.Caption = Format(TOT2, "#,##0.00")
    Me.Label6.Caption = Format(ListView.ListItems.Count, "#,##0")
    
   Set TT = New CTooltip
   TT.Style = TTBalloon
   TT.Icon = TTIconInfo

    Exit Sub

errore:
    MsgBox "Errore Numero: " & CStr(Err.Number) & vbCrLf & _
           "Descrizione: " & Err.Description & vbCrLf & _
           "Sorgente dell'Errore: " & Err.Source

    Err.Clear

End Sub

gbianchi · March 29th, 2009, 10:32 AM

Hello.. You are showing 154k+ register on a list view?? Wow. Well, IMHO, you don't show so many rows!
WHy? the user will never see that much quantity of records, there is no point in showing that much.
There is no way you can speed up loading of so much records on any system. How much time it takes??
The only advice I can see, if you need to know before hand how many records you will loop, you have a record count property (doesn't always work like it should), and you can COUNT (in sql!) the quantity of records (you avoid the first loop, it will really save you a lot of time.
Anyway, can you tell us why show so many records??