Slow Excel data populating

[Andrzejek]

Excel 2010 with Oracle as my DB.

I have this data in a recordset (recSet) coming from my DB that may look like:

[pre]
AMT
5 201206 $32,436.80
5 201207 $28,227.00
5 201208 $242.50
6 201204 $33.22
7 201204 $36.04
7 201205 $16,199.00
7 201206 $363,427.45
7 201207 $34,015.24
7 201208 $3,364,965.85[/pre]

And I put it into Excel that looks like this
[pre]
Z AA AB AC AD
201204 201205 201206 201207 201208
Apr-12 May-12 Jun-12 Jul-12 Aug-12
5 $32,436.80 $28,227.00 $242.50
6 $33.22
7 $36.04 $16,199.00 $363,427.45 $34,015.24 $3,364,965.85
8 $8,212.19 $142,881.46 $391,247.88 $374,317.21
9
10
11 $1,649.00 $77,315.08
[/pre]

I have some other information in columns A-Y, but this part takes the most of time to populate.
I read the data record by record from my recordset, match date from my record (YYYYMM, like 201206) with the cell in row 1 that has the same value (201206) and populate Excel one cell at the time with the code like this:
[tt]
Cells(intCFr, intS).Value = recSet!AMT.Value[/tt]
(where intCFr and intS are variables to point to the right cell )

Dates in cells in row 1 depend on dates seleted by user.

Since I have to deal with about 1000-2000 records for the time span of 5 years (5 years x 12 months = 60 columns in Excel) that takes time to populate.

Is there any faster way to do it?

Most of the time I use:
[tt]Range(“A1515”).Value = Array(...)[/tt]
to populate whole row of cells in one line of code, which is a lot faster, although my preferred way is to use
[tt]Range("A2").CopyFromRecordset recMyRst[/tt]
which is the best/fastest.


Have fun.

---- Andy

 

[SkipVought]

andy,

What DB is the source data for your recordset? Oracle, Access, DB2, Excel....?

I'd be apt to grab it into a separate sheet and then use SUMIFS() or SUMPRODUCT() to populate the pivot, unless your source is Access/Excel, where you could use a CrossTab (Transform) query to, BOOM, drop the entire pivot.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Skip.
The data comes from ORACLE 11.
Not all records will have data in them, see my example for 9 and 10 (those are, let's say: Projects) And Project 5 will have some data for some months (201206, 201207, 201208), but no data for other months (201204, 201205)
I hope that makes sense


Have fun.

---- Andy

 

[SkipVought]

well the ultimate question revolves around the actual code loop to process the recordset and translate to your Excel sheet.

secondly, how many rows & columns in the target sheet?

thirdly, you have 5-7 in your recordset, but 5-11 in your sheet? is that just a matter of an incomplete recordset in your example?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

The actual code is in VB 6, getting data from Oracle and placing it into Excel:

 recSet.Filter = stuff to filter data for 1 Project

 If recSet.RecordCount > 0 Then
     For intM = 1 To recSet.RecordCount [green]'go down the records[/green]
         For intS = intColIndxStart To intColIndxStop - 1[green]
             'intColIndxStart is Col Z, intColIndxStop is AD in this example[/green]
             If recSet!MyDate.Value = xlApp.Cells(1, intS).Value Then[green]
		'If data in rec = date in row 1 in Excel[/green]
                 xlApp.Cells(intCFr, intS).Value = recSet!MYAMT.Value
                 Exit For
             End If
         Next intS
         recSet.MoveNext
     Next intM
 End If

 recSet.Filter = ADODB.FilterGroupEnum.adFilterNone

If user want to process 5 years of data, that may be beween 1000 and 2000 records
And like I said: 5 years x 12 month per year is 60 columns of data

thirdly, you are right, an incomplete recordset in my example. Sorry for the confusion. Just wanted to show some records with no data.

Have fun.

---- Andy

 

[SkipVought]

This may help, but I don't see much else

    With recSet
        If .RecordCount > 0 Then
            Do While Not .EOF
                For intS = intColIndxStart To intColIndxStop - 1
                    If recSet!MyDate.Value = xlApp.Cells(1, intS).Value Then
                        xlApp.Cells(intCFr, intS).Value = recSet!MYAMT.Value
                        Exit For
                    End If
                Next
                .MoveNext
            Loop
        End If
    End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Thank you Skip,
When I created this report in Excel, I tested it with a couple of dates, so a few seconds to generate it was not a big deal. Then after a few years in use I was informed that normally user runs this report for a span of 4 years, and it takes him about an hour and a half (!) I wouldn’t be happy with it either, but you can’t fix the problem if nobody tells you there is one, right? So I found this ‘bottle-neck’ in the code, re-arranged the code and changed it to what I showed you. Now this 4 years of data takes about 3 minutes 15 seconds. Now they want to process 5 years of data, but user is happy to have it in less than 4 minutes. To me 4 minutes is kind of long, I would like to have it a lot faster, but will let it go for now.

Maybe someday an idea will come to me and I will re-do it to less than a minute…..


Have fun.

---- Andy

 

[SkipVought]

So its a go-geta-cupa-coffee code. ;-)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

Andy, what about the following logic:
- use a helper worksheet with a .CopyFromRecordset method to pick all data,
- copy it to variant array,
- process array in memory to output array,
- assign output array to report worksheet.
Maybe it will be a minute faster...

combo

 

[Andrzejek]

They are happy now with "a go-geta-cupa-coffee code".
It used to be 'you better start it before lunch because it will be a while' code

Thanks combo for the suggestion, but I am afraid is not the part of accessing the data from the recordset (which it is like an array anyway). It is putting the data on ‘cell-by-cell’ basis that makes it preform so slow.

Have fun.

---- Andy

 

[HughLerwill]

I usually try to apply With to the slowest object in loops. You may get a marginal improvement if you apply With to the xlApp object if that is the bottleneck. I would also be tempted to be more specific in you reference to the Cells in such a With, i.e. via XlApp.WorkBooks(MyWorkBook).WorkSheets(MySheet).Cells rather than relying on the defaults (ie. ActiveWorkBook.ActiveSheet)with you are via XlApp.Cells; as in...

With XlApp.WorkBooks(MyWorkBook).WorkSheets(MySheet)
'code
.Cells(r,c).Value = "blah"
'code
End With

 

[combo]

My intention was to spped up filling the worksheet, so the idea to transfer a recordset to variant array, process into output array, and finally fill the worksheet with it (single line of code for properly sized range). In fact, GetRows will do the first two instead.

combo