Stress Testing Excel

[LufiaMaxim]

Background: I have a database with values for a few different things that is recorded every 5 minutes. I import all the data into Excel and do some reformatting to be able to chart it the way I want.

I've made a chart in Excel with all this data that shows a timeline of all these things. So far it works great with "little" amounts of data (~10k rows).

Doing some stress testing, I've made a sample of made up values with a timeline that equals 200k rows (starts at 8/25/2009 and ends at 7/20/2011).

Chances of my end-user using this graph with ~2 years of data is slim to none, but I still want to be able to support it, at least somewhat.

Of course, when dealing with this amount of data (the grand total of data points, oddly enough, ended up being 222,223 rows), you can't expect it all to work in a split second. I will even mention that my CreateGraph() sub took a full 9 minutes to load my graph, where it's a sub-second procedure with 10k rows, lol.

So let's assume I can refactor my loading graph to get it under 5 minutes, which might be more acceptable. Next problem.

I have a scrollbar on this graph. It allows you to scroll through the entire timeline of the data provided. Again, works flawlessly and with no lag with my initial test data of 10k rows. With my stress test data, it takes a full 3 seconds to scroll in any direction.

Now here comes the question (finally): Why does it take 3 seconds to scroll in any direction? Before you come up with the (somewhat obvious) answer, read on please

The ONLY thing I do in my code is change the min and max value of the x-axis. The entire graph is "available" upon creation (hence the 9 minutes), but I only display anywhere from 1day to 31 days at most.

Here's my only code for scrollGraph():

Public Sub scrollGraph()
    Dim ws As Worksheet
    Dim cht As Chart
    Dim scrl As Object
    Dim startValue As Double
    Dim endValue As Double
    Dim interval As Double

    Set ws = ActiveSheet
    Set scrl = ws.scrlXAxis
    Set cht = ws.ChartObjects("DiabetesChart").Chart
    startValue = CLng(CDate(ws.OLEObjects("txtStartDate").Object.value))
    endValue = CLng(CDate(ws.OLEObjects("txtEndDate").Object.value)) + 1
    
    Select Case endValue - startValue
        Case 1
            interval = 1
        Case 2 To 3
            interval = 2
        Case 4 To 5
            interval = 4
        Case 6 To 9
            interval = 6
        Case 10 To 16
            interval = 10
        Case 17 To 30
            interval = 17
        Case 31 To 48
            interval = 31
    End Select

    interval = propertyValue(interval & ".interval")
    
    With cht.Axes(xlCategory)
        .MinimumScale = startValue + (scrl.value * interval)
        .MaximumScale = endValue + (scrl.value * interval)
    End With
End Sub

I've bolded the only part (in my opinion) that actually does ANYTHING to my graph.

Seeing as all the data is graphed, and the only thing I'm changing is the min and max value of the x-axis scale, is it normal behaviour to have a 3 second lag on each scroll?

Also, I should mention that propertyValue is a simple case statement similar to the one already used in this sub (but still different enough to warrant it's own), so I find it hard to believe the lag is coming from that point.

Other random facts that might need to be known: Currently on a laptop with 1.8ghz dual core, 4gb RAM. When having this graph open, it uses up 1.2 gb of my available RAM. I think this might be a factor, which is why I mention it.

I hope the answer is simply not "Excel isn't meant to support something of this size"

Any insight is appreciated.