Trying to speed up the results from a DB to a TAChart
lazarus / 55-TAChart

----------------------------------------------------------------------
donnie · 2017-02-08 10:49

I have a database with a table MarkPoints. From this table I need 7
fields to bring to my chart (Date, X,Y,Z,MX which is (StartX-X), MY
which is(StartY-Y) ,MZ which is (StartZ-Z) in cm).
This table has many records let's say 20.000.
And I have many MarkPoints max 10 min 5 in every TAChart.
I draw 5 charts at once and every chart has at the bottom axis the
date of every record.
So to sum up I have 6 Charts X 5 PointMarks in every Chart X 20.000
records in every MarkPoint . Which means 600.000 points to draw.
I start with every point as I bring it from the Query and I draw that
point to all Charts and after that I continue with the rest points.
For example let's say that I have point1, point2...point5. I start
with point1 and I draw the (Date bottom axis -X in cm in the left
axis) X diagram first, after that the (Date to the bottom axis / Y in
cm to the left axis) Y diagram,....Z diagram,....MZ diagram. After
that it comes point 2, point 3, point 4 and point 5.
The problem is that it takes to much time. I mean about 7 to 10
minutes!!!Is that logical???Shouldn't it be faster?The thing is that I
have tested it to two laptops one fast enough and one much more
slower. The speed hasn't change too much. Maybe one or two minutes at
most.
(1st laptop: core i7, 16GB RAM, SSD 250GB // 2nd laptop: centrino duo,
4GB RAM, HARD DISK 5.200rpm)
The routine that I use to draw the first MarkPoint is the following:

1)I call the SQL Query(firebird 2.5) to bring the Data:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
ViewChartQuery.SQL.Text:='SELECT  X, Y, Z, MX, MY, MZ, MYDATE  FROM
POINTMARKS WHERE   POINTMARKS .pointid = :chartpointid AND  AND MYDATE
 >= :FILTERSTARTDATE AND MYDATE <= :FILTERENDDATE ORDER BY MYDATE
 ASC'; 

2)After that I bring all these data to arrays. I create six arrays (as
many as my fields are) where I collect my data.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type
  TMark = record            x: Tfloat;    y: Tfloat;  end;    
MarksArray = array of TMark ; ArrayX,ArrayY,...,ArrayMZ : array of
MarksArray;... procedure TViewChartsfrm.FillMarksArrays;var  i:
integer;  beginfor i := 0 to ViewChartQuery.RecordCount - 1 do 
begin     if not VarIsNull(ViewChartQuery.FieldByName('X').Value)
then    begin      SetLength(ArrayX, Length(MeasArrayX) + 1);     
ArrayX[High(ArrayX)].x :=
ViewChartQuery.FieldByName('MYDATE').AsDateTime;     
ArrayX[High(ArrayX)].y := ViewChartQuery.FieldByName('X').AsFloat;   
end;     if not VarIsNull(ViewChartQuery.FieldByName('Y').Value) then 
  begin      SetLength(ArrayY, Length(ArrayY) + 1);     
ArrayY[High(ArrayY)].x :=
ViewChartQuery.FieldByName('MYDATE').AsDateTime;     
ArrayY[High(ArrayY)].y := ViewChartQuery.FieldByName('Y').AsFloat;   
end;    ......    if not
VarIsNull(ViewChartQuery.FieldByName('MZ').Value) then    begin     
SetLength(ArrayMZ, Length(ArrayMZ) + 1);      ArrayMZ[High(ArrayMZ)].x
:= ViewChartQuery.FieldByName('MYDATE').AsDateTime;     
ArrayMZ[High(ArrayMZ)].y := ViewChartQuery.FieldByName('MZ').AsFloat; 
  end;     if i < ViewChartQuery.RecordCount then     
ViewChartQuery.Next;   end; 
3)After filling the arrays I draw as usual...

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  for i :=
Low(ArrayX) to High(ArrayX) do  begin    xdate := ArrayX[i].x;   
yfromx := ArrayX[i].y;    lineX.AddXY(xdate , yfromx , 'Pointname:' +
focusChartPointName + ', X(datetime):' + FormatDateTime('dd/mm/yy
hh:nn', xdate ) + ', Y(mm):');  end; ....  for i := Low(ArrayMZ) to
High(ArrayMZ) do  begin    MZdate := ArrayMZ[i].x;    yfromMZ :=
ArrayMZ[i].y;    lineMZ.AddXY(MZdate , yfromMZ , 'Pointname:' +
focusChartPointName + ', X(datetime):' + FormatDateTime('dd/mm/yy
hh:nn', MZdate ) + ', Y(mm):');  end;   I use arrays as in same cases
I pass them through filters like Moving Average(where the user defines
the number of measurements of the filter).
The drawing speed without filter and with filter(moving average) is
almost the same which means the filter isn't responsible for the
speed.
Maybe the drawing speed is normal to such ammount of data or maybe
this can be faster.
Thanks in advance

----------------------------------------------------------------------
wp · 2017-02-08 12:14

Handling 5 charts with 20000 data points each is no problem, I guess
you won't notice it.

At first I see the usual datafield errors propagated by too many bad
books and tutorials: Don't call FieldByName within a loop iterating
over a large number of records. Having to find the same fields over
and over again is a speed killer. Calculate the fields before the loop
and store them in corresponding variables.

Next: are any visual controls linked to the dataset? While you iterate
through the dataset the visual controls synchronously update, and this
is a REAL speed killer. Either separate the dataset from the visual
controls, or call Dataset.DisableControls before, and
Dataset.EnableControls after iterating through the dataset - you will
be surprised.

Less important for speed, but important for memory performance: You
store the data extracted from the database in arrays, but then you use
Series.AddXY to add the data to the series. This will store the data
again in an internal chartsource. You should add a
TUserDefinedChartSource for each series and link it to the Source
property of the series. After your data have been read into the arrays
you set the PointsNumber of each userDefinedChartSource to the Length
of the corresponding array. Then you write an event handler for
OnGetChartDataItem which tells the series where it finds x and y
values in the array for every point index. There's a tutorial (http://
wiki.lazarus.freepascal.org/TAChart_Tutorial:_Userdefined_ChartSource)
describing exactly this situation step by step.

----------------------------------------------------------------------
donnie · 2017-02-09 12:48

QuoteAt first I see the usual datafield errors propagated by too many
bad books and tutorials: Don't call FieldByName within a loop
iterating over a large number of records. Having to find the same
fields over and over again is a speed killer. Calculate the fields
before the loop and store them in corresponding variables.
I need to pass these variables as I run the Query. I stored the index
of these fields at the begining before the loop and I gave it into the
query like this.
instead of this into the loop:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 ArrayX[High(ArrayX)].x :=
ViewChartQuery.FieldByName('MYDATE').AsDateTime;
I searched it before the loop eg.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var
mydatevar : integer;mydatevar:=
ViewChartQuery.FieldByName('MYDATE').FieldNo; 

and into the loop I give the value like this in order not to search
all the time.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 ArrayX[High(ArrayX)].x :=
ViewChartQuery.Fields.FieldByNumber(mydatevar).Value;  But the speed
is the same again.


QuoteNext: are any visual controls linked to the dataset? While you
iterate through the dataset the visual controls synchronously update,
and this is a REAL speed killer. Either separate the dataset from the
visual controls, or call Dataset.DisableControls before, and
Dataset.EnableControls after iterating through the dataset - you will
be surprised.

No visual controls are connected. My lines are drawned live by the
arrays. The dataset is working from behind and gives the values to the
arrays which gives them to the lines. When I have the values of a line
I draw it live(at the backround) so that the user won't think that
something is going wrong.
When I draw a line the dataset is working from behind to continue with
the other MarkPoint.

QuoteLess important for speed, but important for memory performance:
You store the data extracted from the database in arrays, but then you
use Series.AddXY to add the data to the series. This will store the
data again in an internal chartsource. You should add a
TUserDefinedChartSource for each series and link it to the Source
property of the series. After your data have been read into the arrays
you set the PointsNumber of each userDefinedChartSource to the Length
of the corresponding array. Then you write an event handler for
OnGetChartDataItem which tells the series where it finds x and y
values in the array for every point index. There's a tutorial (http://
wiki.lazarus.freepascal.org/TAChart_Tutorial:_Userdefined_ChartSource)
describing exactly this situation step by step.

Yes you are right about the memory perfomance. This was the next step.
I hadn't done it yet as I was working at the speed. In big ammount of
data TUserDefinedChartSource is necessary. The ammount of data is
200.000 points to every chart and i have 5 charts. It is almost one
million points sometimes.

----------------------------------------------------------------------
wp · 2017-02-09 15:44

At first, find in the attachment a project to demonstrate the speed of
TAChart. It contains 6 charts, each one with a series to display
200.000 data points (x increasing in equidistant steps, y = x + some
noise). There are two basic scenarios: Store the values in the builtin
listsource of each series, or store the values in arrays and plot them
using UserDefinedChartSources. The latter method is the fastest, but
the first one gets close if chart updates are inhibited during adding
data by using the BeginUpdate/EndUpdate mechanism of the listsource.
In each case, 200.000 x 6 data points are processed within a few
tenths of a second. Not to speak of the 10 minutes that you mention.

Another issue in the code of your first post: you iterate the database
records in a "for" loop until it reaches the dataset's RecordCount,
and you query the RecordCount even inside the loop. This is a very
expensive operation. Use the plain old "while not Dataset.EoF do"
which you find in every text book.

Also you redimension the arrays inside the loop. Every time the
already occupied array is copied to a new location. This is VERY
expensive. Dimension the array before the loop to enough or more than
enough elements, use a counter inside the loop, and fix the array
length at the end according to the counter value.

Regarding my first item you did not catch the point because you sill
call FieldByName inside the loop. The loop runs more than 1 million
times and in every loop you search for a few of the database fields.
Find the fields before the loop. Something like this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TViewChartsfrm.FillMarksArrays;var  n, ix, iy, imz: integer;   
MyDateField: TFIeld;  XField: TField;  YField: TField;  MZField:
TField;begin  // Get fields  MyDateField :=
ViewChartQuery.FieldByName('MYDATE');  xField :=
ViewChartQuery.FieldByName('X');  yField :=
ViewChartQuery.FieldByName('Y');  MZField :=
ViewChartQuery.FieldByName('MZ');    // Set array lengths  n :=
ViewChartQuery.RecordCount;  SetLength(ArrayX, n);  SetLength(ArrayY,
n);  SetLength(ArrayMZ, n);   // Iterate through dataset 
ViewChartQuery.First;  ix := 0;  iy := 0;  imz := 0;  while not
ViewChartQuery.EoF do begin    if not XField.IsNull then begin     
ArrayX[ix].x := MyDateField.AsDateTime;      ArrayX[ix].y :=
XField.Asfloat;      inc(ix);    end;    if not YField.IsNull then
begin      ArrayY[iy].x := MyDatefield.AsDateTime;      ArrayY[iy].y
:= YField.AsFloat;      inc(iy);    end;    if not MZField.IsNull then
begin      ArrayMZ[imz].x := MyDateField.AsDateTime;     
ArrayMZ[imz].y := MZField.AsFloat;      inc(imz);    end;   
ViewChartQuery.Next;  end;   // Fix array lengths  SetLength(ArrayX,
ix);  SetLength(ArrayY, iy);  SetLength(ArrayMZ, imz);end;

----------------------------------------------------------------------
zeljko · 2017-02-09 16:22

Try to avoid DataSet.FieldByName('xxx').AsYYYY , use
DataSet.Fields[0].AsYYYY where Fields is exactly what you set in query
eg in your example:
'SELECT  X, Y, Z, MX, MY, MZ, MYDATE  FROM POINTMARKS WHERE ...';
X = Fields[0]
Y = Fields[1]
Z = Fields[2] ...etc

----------------------------------------------------------------------
donnie · 2017-02-10 12:40

Thanks wp and zeljko,
I change my query and the results were amazing. I was bringing in my
query not only the fields I needed, but 10 more fields (just in case I
need them). I changed that and I brought only the fields I needed. I
also changed the FieldByName expression.
The drawing speed for 600.000 points was reduced from 13 minutes to 1
minute! 
And I haven't finished all the changes. I haven't changed the Arrays.
Not even use UserDefinedChartSources!!!!
I couldn't believe it myself. (Ofcourse I double checked the results
to be sure that had the same results).
Thanks once again.