Optimize ADO DataGrid Record Processing

I'm using VB6 against an sql server database.
Can I get some opinions or suggestions on my processing technique?
Am I performing this in the best possible way or is there a better way to populate and process the recordsets retrieved into my datagrid?
Currently, some of the more complex recordsets which are joins on two tables are slow to load into my datagrid.
Is there a better way than what I am doing here:
------------------------------------------------------
A form is launched which contains a datagrid.
In the Form_Activate() procedure, a function is called which populates the grid.
The function, is the following:
------------------------------------------------------
Public Sub DataInquire()
Dim ctr As Integer
    ucDataGrid1.Clear
    Select Case CurrentForm
        Case "ModifyCuts"
              SQLData = "SELECT * FROM Cuts ORDER BY RecordStatus" & SortBy
    End Select
    If ActiveRs Then RsData.Close
    RsData.Open SQLData, Cn, adOpenStatic, adLockBatchOptimistic
    ActiveRs = True
    If Not (RsData.EOF And RsData.BOF) Then
        RsData.MoveFirst
        ctr = 0
         ReDim DataArray(MaxCuts, 12)
        While Not RsData.EOF
            DataArray(ctr, 0) = RsData!ID
            DataArray(ctr, 1) = RsData!appropid
            DataArray(ctr, 2) = RsData!Description
            DataArray(ctr, 3) = RsData!recamt
            DataArray(ctr, 4) = RsData!cashamt
            DataArray(ctr, 5) = RsData!workforce
            DataArray(ctr, 6) = RsData!ftecutCode
            DataArray(ctr, 7) = RsData!subcommcode
            DataArray(ctr, 8) = RsData!NegCode
            DataArray(ctr, 9) = RsData!agencycode
            DataArray(ctr, 10) = RsData!fundtypecode
            DataArray(ctr, 11) = RsData!programcode
            DataArray(ctr, 12) = RsData!purposecode
 
            ucDataGrid1.LoadCutsData RsData!ID, RsData!appropid, RsData!Description, _
                FormatCurrency(RsData!recamt, 0), FormatCurrency(RsData!cashamt, 0), _
                RsData!workforce, RsData!ftecutCode, _
                RsData!subcommcode, RsData!NegCode, RsData!agencycode, RsData!fundtypecode, _
                RsData!programcode, RsData!purposecode
            RsData.MoveNext
            ctr = ctr + 1
        Wend
             ucDataGrid1.SelectFirst
---------------------------------------
In the above, I read the data into an array and then call the LoadCutsData function to populate the grid.
The LoadCutsData function is the following:
-----------------------------------------------------
Public Sub LoadCutsData(IdToAdd, AppropIdToAdd, DescriptionToAdd, _
    RecAmtToAdd, CashAmtToAdd, WorkforceToAdd, FTECutCodeToAdd, _
    SubCommCodeToAdd, NegCodeToAdd, AgencyCodeToAdd, FundTypeCodeToAdd, _
    ProgramCodeToAdd, PurposeCodeToAdd)
Dim NewItem As ListItem
    Set NewItem = LVDataGrid.ListItems.Add(, , IdToAdd)
    NewItem.ListSubItems.Add , , AppropIdToAdd
    NewItem.ListSubItems.Add , , DescriptionToAdd
    NewItem.ListSubItems.Add , , RecAmtToAdd
    NewItem.ListSubItems.Add , , CashAmtToAdd
    NewItem.ListSubItems.Add , , WorkforceToAdd
    NewItem.ListSubItems.Add , , FTECutCodeToAdd
    NewItem.ListSubItems.Add , , SubCommCodeToAdd
    NewItem.ListSubItems.Add , , NegCodeToAdd
    NewItem.ListSubItems.Add , , AgencyCodeToAdd
    NewItem.ListSubItems.Add , , FundTypeCodeToAdd
    NewItem.ListSubItems.Add , , ProgramCodeToAdd
    NewItem.ListSubItems.Add , , PurposeCodeToAdd

    LVDataGrid.ListItems(LVDataGrid.ListItems.Count).Selected = True
End Sub
-------------------------------------------------------
My question is this:
Is there a better or more optimal way to process the datagrid than what I am doing here?

Thanks in Advance
John
jtrapat1Asked:
Who is Participating?
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
Ok it should be something like this:

   RsData.Open SQLData, Cn, adOpenForwardOnly, adLockReadOnly
   ActiveRs = True
   If Not (RsData.EOF And RsData.BOF) Then
       RsData.MoveFirst

'       ReDim DataArray(0 To RsData.Fields.Count - 1, 1 To 12) - not necessary as the getrows call will redim the array anyway
       DataArray = RsData.GetRows
'       While Not RsData.EOF
       For ctr = 0 To Ubound(DataArray,0)
         
           ucDataGrid1.LoadCutsData DataArray( 0,ctr), DataArray(1,ctr), DataArray(2,ctr), _
               FormatCurrency(DataArray(3,ctr)), FormatCurrency(DataArray( 4,ctr)), _
               DataArray(5,ctr), DataArray(6,ctr), _
               DataArray(7,ctr), DataArray(8,ctr), DataArray(9,ctr), DataArray(10,ctr), _
               DataArray(11,ctr), DataArray(12,ctr)
       Next
-------------------------------------------------------
0
 
TimCotteeHead of Software ServicesCommented:
You can directly populate an array from a recordset using :

Dim DataArray()
DataArray = RsData.GetRows()

This array is of the size DataArray(0 to Fields.Count -1,1 To Records)

So DataArray(2,4) returns the third (zero-based remember) field from the fourth record

This method is much quicker than directly populating the array as you currently are.
0
 
Anthony PerkinsCommented:
To improve performance:  
You can also open the recordset as adOpenForwardOnly and adLockReadOnly

Anthony
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
jtrapat1Author Commented:
Tim,
Thanks for pointing me in  the right direction with the getrows() method.
However, I can't figure out how to reference the correct values.

Can you help me out with the logic on this.
I checked the MSDN and this discussion area but I couldn't find an example similar to mine.

I'm trying to loop thru the recordset and pass the correct values to my loadcutsdata function but the values are wrong.
I declared my DataArray() as Dim DataArray() as variant
Here's my code:
-----------------------------------------------------
    RsData.Open SQLData, Cn, adOpenForwardOnly, adLockReadOnly
    ActiveRs = True
    If Not (RsData.EOF And RsData.BOF) Then
        RsData.MoveFirst
        ctr = 0
 
        ReDim DataArray(0 To RsData.Fields.Count - 1, 1 To 12)
        While Not RsData.EOF
            DataArray = RsData.GetRows
           
            ucDataGrid1.LoadCutsData DataArray(ctr, 0), DataArray(ctr, 1), DataArray(ctr, 2), _
                FormatCurrency(DataArray(ctr, 3)), FormatCurrency(DataArray(ctr, 4)), _
                DataArray(ctr, 5), DataArray(ctr, 6), _
                DataArray(ctr, 7), DataArray(ctr, 8), DataArray(ctr, 9), DataArray(ctr, 10), _
                DataArray(ctr, 11), DataArray(ctr, 12)
            RsData.MoveNext
            ctr = ctr + 1
        Wend
-------------------------------------------------------

Thanks
John
0
 
jtrapat1Author Commented:
Tim,
I tried your code and I'm getting an subscript out of range error on this line:
   For ctr = 0 To Ubound(DataArray,0)
I changed the line to:
   For ctr = 0 To Ubound(DataArray) and I get the correct values inserted into my grid but I know this isn't syntactically correct.
It calculates the correct upper bound as 12 and loops thru from 0 to 12.
It's like the upper bound function doesn't calculate the correct value.

Can you see what I'm doing wrong?
Do I have to use other parameters in my GetRows() function; such as:
DataArray = RsData.GetRows(Rows,Start,Fields)

Maybe I'm returning the wrong result.
Reply when you can.

Thanks
John
0
 
Anthony PerkinsCommented:
I believe you need to change:
For ctr = 0 To Ubound(DataArray,0)

to:
For ctr = 0 To Ubound(DataArray, 2)

This will give you the number of rows.

Anthony
0
 
Anthony PerkinsCommented:
I should have explained that the GetRows returns an array composed of Fields * Columns.  Therefore to loop through the rows, you need UBound(DataArray, 2)

Anthony
0
 
jtrapat1Author Commented:
Tim's code plus the change from Anthony was exactly the right code that helped me to populate the grid.

Thanks to Tim, Anthony, and everyone who helped out with this problem.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.