Chart Performance Problem with Large Data Set

S

Salil Gaitonde

Hello.

I have a 3D Chart in OWC that is taking a long time to generate
because of the size of the data.

The chart has 6 series, each with about 8500+ data points in it. I've
attached the code below, with some lines snipped out so that's more
readable.

One possible performance issue I can think of is that I have to build
a 2 dimensional array in VB. This is array is about 6 by 8866
elements. (Incidentally, I've found that this step is significantly
faster in VB versus JScript. Has anyone else seen this?) This step
is labeled "PERFORMANCE PROBLEM #1".

The second area of the code I suspect a bottleneck is that I have to
individually set the interior and border color of each data point. I
have to do this because the data is individually color coded, and I
cannot use the "Range" function to set the color en mass. This is
labeled as "PERFORMANCE PROBLEM #2".

Can anyone recommend alternatives to these approaches that could speed
this code up? Or does anyone see other bad practices in my code that
are causing the slowness? I greatly appreciate any help on this.
Thank you very much, in advance.


<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns="http://www.w3.org/TR/REC-html40">

<head>

</style>

<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">


<script language="VBScript">
Function createTimeAxis()



Dim timeAxis( 8866 , 0 )
timeAxis(0 , 0 ) = "Tue Jun 01 00:00:00 CDT 2004"
....snip...
timeAxis(8862 , 0 ) = "Sun Aug 01 13:00:00 CDT 2004"
timeAxis(8863 , 0 ) = "Sun Aug 01 13:10:00 CDT 2004"
timeAxis(8864 , 0 ) = "Sun Aug 01 13:20:00 CDT 2004"

createTimeAxis = timeAxis
End Function

// ----------------PERFORMANCE ISSUE #1 ------------ //

Function createSeries()
Dim seriesData( 8866 , 5 )
seriesData(0 , 0) = "0.0"
....snip...
seriesData(8863 , 0) = "0.0"
seriesData(8864 , 0) = "0.0"

seriesData(0 , 1) = "16.46"
....snip...
seriesData(8862 , 1) = "0.0"
seriesData(8863 , 1) = "0.0"
seriesData(8864 , 1) = "0.0"

seriesData(0 , 2) = "98.10001"
....snip...
seriesData(8862 , 2) = "0.0"
seriesData(8863 , 2) = "0.0"
seriesData(8864 , 2) = "0.0"

seriesData(0 , 3) = "66.0"
....snip...
seriesData(8862 , 3) = "0.0"
seriesData(8863 , 3) = "0.0"
seriesData(8864 , 3) = "0.0"

seriesData(0 , 4) = "79.02001"
....snip...
seriesData(8862 , 4) = "0.0"
seriesData(8863 , 4) = "0.0"
seriesData(8864 , 4) = "0.0"

seriesData(0 , 5) = "95.00501"
....snip...
seriesData(8862 , 5) = "0.0"
seriesData(8863 , 5) = "0.0"
seriesData(8864 , 5) = "0.0"

createSeries = seriesData
End Function
</script>

<script language="JScript">

function genSpreadSheet() {

objNew = document.createElement("OBJECT");

objNew.classid = "clsid:0002E551-0000-0000-C000-000000000046";
x objNew.id = "sp";
objNew.Visible = true;

// Get a new workbook.
var oWB = objNew.ActiveWorkbook;
var oSheet = oWB.ActiveSheet;

range = objNew.ActiveCell;
cellRange = "A2:A" + 8866;
range.Range( cellRange ).NumberFormat = "General";
range.Range( cellRange ).Font.Size = "8";
range.Range( cellRange ).Value = createTimeAxis();


cellRange = "B2:B4000";
cellRange = "B2:G8866";

range.Range( cellRange ).NumberFormat = "Standard";
range.Range( cellRange ).Font.Size = "8";
range.Range( cellRange ).Value = createSeries();



return objNew;
}

function genGraph( objNew ) {
objNew2 = document.createElement("OBJECT");
objNew2.classid = "clsid:0002E556-0000-0000-C000-000000000046";
objNew2.id = "chsp";

var c = objNew2.Constants;
//load some sample data
var ch = objNew2.Charts.Add();
objNew2.Charts(0).Type = c.chChartTypeColumn3D;
objNew2.Charts(0).ProjectionMode =
c.chProjectionModeOrthographic;
objNew2.Charts(0).AmbientLightIntensity = 0.75;
objNew2.Charts(0).DirectionalLightInclination = 60;
objNew2.Charts(0).DirectionalLightIntensity = 1.0;
objNew2.Charts(0).DirectionalLightRotation = 90;
objNew2.DisplayToolbar= false;
objNew2.DisplayPropertyToolbox = false;
objNew2.DataSource = objNew;
objNew2.HasPlotDetails = false;
var timeAxisConfig =
objNew2.Charts(0).Axes(c.chAxisPositionTimescale);
var yAxisConfig = objNew2.Charts(0).Axes(1);
yAxisConfig.HasTitle = true;
yAxisConfig.Title.Caption = "RAM utilization %";
yAxisConfig.Title.Font.Size = 8;




timeAxisConfig.Font.Size = 8;
timeAxisConfig.Font.Color = "black";
timeAxisConfig.MajorTickMarks = c.chTickMarkAutomatic;
timeAxisConfig.HasTickLabels = true;

timeAxisConfig.TickLabelSpacing = 443;

timeAxisConfig.GroupingType = c.chAxisGroupingNone

categs = new Array();
var i = 0;

categs = "ultra1id";
i = i + 1;

categs = "ultra2id";
i = i + 1;

categs = "linuxp2id";
i = i + 1;

categs = "linuxdp3id";
i = i + 1;

categs = "hp712id";
i = i + 1;

categs = "ibm43pid";
i = i + 1;


// set the categories (series ;-)
ch.SetData( c.chDimSeriesNames, c.chDataLiteral, categs );

cellRange = "A2:A" + 8866;


ch.SeriesCollection(0).SetData( c.chDimCategories,
c.chDataBound, cellRange )
ch.SeriesCollection(0).SetData( c.chDimValues,
c.chDataBound,"B2:B8866");

ch.SeriesCollection(0).Interior.Color="White";
ch.SeriesCollection(0).Border.Color="White";

ch.SeriesCollection(1).SetData( c.chDimCategories,
c.chDataBound, cellRange )
ch.SeriesCollection(1).SetData( c.chDimValues, c.chDataBound,
"C2:C8866");

ch.SeriesCollection(2).SetData( c.chDimCategories,
c.chDataBound, cellRange )
ch.SeriesCollection(2).SetData( c.chDimValues, c.chDataBound,
"D2:D8866");

ch.SeriesCollection(3).SetData( c.chDimCategories,
c.chDataBound, cellRange )
ch.SeriesCollection(3).SetData( c.chDimValues, c.chDataBound,
"E2:E8866");

ch.SeriesCollection(4).SetData( c.chDimCategories,
c.chDataBound, cellRange )
ch.SeriesCollection(4).SetData( c.chDimValues, c.chDataBound,
"F2:F8866");

ch.SeriesCollection(5).SetData( c.chDimCategories,
c.chDataBound, cellRange )
ch.SeriesCollection(5).SetData( c.chDimValues, c.chDataBound,
"G2:G8866");

// ----------------PERFORMANCE ISSUE #2 ------------ //

ch.SeriesCollection(0).Points(1).Interior.Color="Green";
....snip...
ch.SeriesCollection(0).Points(6805).Border.Color="DarkGreen";
ch.SeriesCollection(0).Points(6834).Interior.Color="Green";
ch.SeriesCollection(0).Points(6834).Border.Color="DarkGreen";

ch.SeriesCollection(1).Points(1).Interior.Color="Green";
....snip...
ch.SeriesCollection(1).Points(6834).Border.Color="DarkGreen";
ch.SeriesCollection(1).Points(6835).Interior.Color="Green";
ch.SeriesCollection(1).Points(6835).Border.Color="DarkGreen";

ch.SeriesCollection(2).Points(0).Interior.Color="Red";
....snip...
ch.SeriesCollection(2).Points(8306).Border.Color="DarkRed";
ch.SeriesCollection(2).Points(8307).Interior.Color="Red";
ch.SeriesCollection(2).Points(8307).Border.Color="DarkRed";

ch.SeriesCollection(3).Points(0).Interior.Color="Green";
....snip...
ch.SeriesCollection(3).Points(8306).Border.Color="DarkGreen";
ch.SeriesCollection(3).Points(8307).Interior.Color="Green";
ch.SeriesCollection(3).Points(8307).Border.Color="DarkGreen";

ch.SeriesCollection(4).Points(0).Interior.Color="Yellow";
....snip...
ch.SeriesCollection(4).Points(8306).Border.Color="DarkRed";
ch.SeriesCollection(4).Points(8307).Interior.Color="Red";
ch.SeriesCollection(4).Points(8307).Border.Color="DarkRed";

ch.SeriesCollection(5).Points(0).Interior.Color="Red";
....snip...
ch.SeriesCollection(5).Points(8278).Border.Color="DarkRed";
ch.SeriesCollection(5).Points(8306).Interior.Color="Red";
ch.SeriesCollection(5).Points(8306).Border.Color="DarkRed";



// set the style
objNew2.style.width = "98%";
objNew2.style.height = "98%";
// objNew2.Border.Weight = chConstants.owcLineWeightThick;

document.all.graphContainer.appendChild(objNew2);
document.all.chsp.attachEvent("CommandExecute", CmdExec);
document.all.chsp.attachEvent("BeforeContextMenu", CtxMenu);

return;
}


function init() {

var o = genSpreadSheet();
genGraph( o );

return;
}
</script>

</head>


<body onload="init()" link="#0000CC" alink="#0000CC" vlink="#0000CC">
<center>
<br/>
<div align="center">

<table id="graphView" border="1" cellpadding="0" cellspacing="0"
style="border-collapse: collapse" bordercolor="#111111" width="100%"
height="100%">
<tr>
<td align="left" width="100%" height="3">
<p>
<br/>
<font class="basicText">
&nbsp;&nbsp;Graph for Query: <b>"Show graph of RAM
utilization "</b><br>


&nbsp;&nbsp;Zoom:&nbsp;&nbsp;1


&nbsp;&nbsp;<a target="_top"
href="/itmc/basicAction.do?link=/itmc/resultAction.do&zoom=140&reuseSheet=true&key=2004-08-01
13:22:14.673Report">2</a>



&nbsp;&nbsp;<a target="_top"
href="/itmc/basicAction.do?link=/itmc/resultAction.do&zoom=180&reuseSheet=true&key=2004-08-01
13:22:14.673Report">3</a>



&nbsp;&nbsp;<a target="_top"
href="/itmc/basicAction.do?link=/itmc/resultAction.do&zoom=220&reuseSheet=true&key=2004-08-01
13:22:14.673Report">4</a>


</font>
<br/>
<br/>
</p>
</td>
</tr>
<tr>
<td align="center" id="graphContainer" width="100%" height=500">
</td>
</tr>

</table>
</div>
</center>
<table border="0" cellpadding="0" cellspacing="0"
style="border-collapse: collapse" width="100%" height="2%">
<tr>
<td align="center" valign="top" width="100%" height="100%">
</td>
</tr>
</table>
</body>



</html>
 
S

Sascha Sertel

Salil,

first of all, I'm not sure if there is a way to make your code significantly
faster, since even if you put 50,000+ data points in an Excel sheet and
generate a chart from it, it will take some time. However, you did not
specify how much time "long time" actually is, so I will try to give you my
2 cents for performance optimization:

1. I looked through your code and the first thing I saw is that everything
seems to be hardcoded. Did you do that only for readability in the posting
or is this really done this way? Especially for all those lines like

seriesData(0 , 0) = "0.0"
....snip...
seriesData(8863 , 0) = "0.0"
seriesData(8864 , 0) = "0.0"


it would be much better to have FOR loops running over your dataset to
assign 0 as initialization value. Not only will this be optimized for
execution by the interpreter, but it also makes your code a lot shorter,
because you get from 50,000+ lines down to just a couple. Remember that
Internet Explorer also has to load your whole file and parse it before
executing it, and having 50,000+ lines just for initialization and another
100,000+ lines for setting the colors and borders makes a HUGE file to
parse, which already takes a significant amount of time.

2. The same thing applies to your colors. Even though you are using
different colors, from what I can tell from your code example it seems that
there is a certain pattern, so if there is one, use FOR loops and
conditional statements to assign your colors and borders instead of
hardcoding everything, which really creates a lot of code, and you already
identified this as a potential performance bottleneck. Have you tried
building your graph without that code and letting the OWC use its standard
colors? How does this affect the execution time?

3. The next thing I saw in your code is that you are using a Workbook to
supply your graph with data. That seems like a unnecessary detour to me.
Since your data is in an array already, why don't you just use the array
data directly to set the data for your Series with c.chDataLiteral instead
of c.chDataBound? I think you are losing time by recreating your whole data
in the Workbook again and then creating the graph from that data. I have to
admit, though, that using the array directly requires some code changes,
mainly that you split your two-dimensional array in 6 one-dimensional
arrays, so that you can use each array for one Series in your graph.
However, eventually it might speed up things.

4. This just popped into my head, it might not be an issue at all, but I
still want to mention it: Sometimes drawing routines can significantly slow
down such a process, since in a worst case scenario the graph or visible
object is updated for every change to the data sets, every color you set
etc., and therefore causes huge slowdowns for large amounts of data. A
simple example for that is a ListView. Anyone who has ever populated a
ListView dynamically has probably experienced that updating the scrollbar
for every added item takes up 90% of the time, and what took 30 seconds
before can be done in a fraction of a second when hiding the control, adding
the data, and making it visible then. Maybe the same scheme could do some
magic for you, you could for example try hiding the div your are drawing
your graph in and display it after the graph creation is finished, and you
could also swap around some function calls and first populate and initialize
your data arrays before you create the Workbook and Chart objects. As I
said, this might not do anything at all in this case, but it's always good
to keep every possiblity in mind!


Hope that helps,
Sascha
 
S

Salil Gaitonde

Thank you very much for the time, Sascha. I timed my coloring
routines and they are taking over 30 seconds. Optimizing this portion
alone would greatly speed things up.

I cannot allow OWC to use it's standard colors because the graph is
actually color coded. I also can't use a for loop because I don't
know beforehand which points should be colored what. Most of this
code is generated by the backend That's why it looks ugly.

If you have any other suggestions on speeding up the coloring, please
let me know.

Thanks again.

--Salil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cannot set HasPlotDetails to TRUE 1

Top