Recent

Author Topic: Trying to speed up the results from a DB to a TAChart  (Read 4624 times)

donnie

  • Jr. Member
  • **
  • Posts: 72
Trying to speed up the results from a DB to a TAChart
« on: February 08, 2017, 11:49:52 am »
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][+][-]
  1. 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.  


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][+][-]
  1. type
  2.  
  3.  TMark = record        
  4.     x: Tfloat;
  5.     y: Tfloat;
  6.   end;    
  7. MarksArray = array of TMark ;
  8.  
  9. ArrayX,ArrayY,...,ArrayMZ : array of MarksArray;
  10. ...
  11.  
  12. procedure TViewChartsfrm.FillMarksArrays;
  13. var
  14.   i: integer;  
  15. begin
  16. for i := 0 to ViewChartQuery.RecordCount - 1 do
  17.   begin
  18.  
  19.     if not VarIsNull(ViewChartQuery.FieldByName('X').Value) then
  20.     begin
  21.       SetLength(ArrayX, Length(MeasArrayX) + 1);
  22.       ArrayX[High(ArrayX)].x := ViewChartQuery.FieldByName('MYDATE').AsDateTime;
  23.       ArrayX[High(ArrayX)].y := ViewChartQuery.FieldByName('X').AsFloat;
  24.     end;
  25.  
  26.     if not VarIsNull(ViewChartQuery.FieldByName('Y').Value) then
  27.     begin
  28.       SetLength(ArrayY, Length(ArrayY) + 1);
  29.       ArrayY[High(ArrayY)].x := ViewChartQuery.FieldByName('MYDATE').AsDateTime;
  30.       ArrayY[High(ArrayY)].y := ViewChartQuery.FieldByName('Y').AsFloat;
  31.     end;
  32.  
  33.    ......
  34.     if not VarIsNull(ViewChartQuery.FieldByName('MZ').Value) then
  35.     begin
  36.       SetLength(ArrayMZ, Length(ArrayMZ) + 1);
  37.       ArrayMZ[High(ArrayMZ)].x := ViewChartQuery.FieldByName('MYDATE').AsDateTime;
  38.       ArrayMZ[High(ArrayMZ)].y := ViewChartQuery.FieldByName('MZ').AsFloat;
  39.     end;
  40.  
  41.     if i < ViewChartQuery.RecordCount then
  42.       ViewChartQuery.Next;
  43.  
  44.   end;
  45.  

3)After filling the arrays I draw as usual...

Code: Pascal  [Select][+][-]
  1.  
  2. for i := Low(ArrayX) to High(ArrayX) do
  3.   begin
  4.     xdate := ArrayX[i].x;
  5.     yfromx := ArrayX[i].y;
  6.     lineX.AddXY(xdate , yfromx , 'Pointname:' + focusChartPointName + ', X(datetime):' + FormatDateTime('dd/mm/yy hh:nn', xdate ) + ', Y(mm):');
  7.   end;
  8. ....
  9.  
  10.  
  11. for i := Low(ArrayMZ) to High(ArrayMZ) do
  12.   begin
  13.     MZdate := ArrayMZ[i].x;
  14.     yfromMZ := ArrayMZ[i].y;
  15.     lineMZ.AddXY(MZdate , yfromMZ , 'Pointname:' + focusChartPointName + ', X(datetime):' + FormatDateTime('dd/mm/yy hh:nn', MZdate ) + ', Y(mm):');
  16.   end;
  17.  
  18.  
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
« Last Edit: February 08, 2017, 12:11:18 pm by donnie »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: Trying to speed up the results from a DB to a TAChart
« Reply #1 on: February 08, 2017, 01:14:22 pm »
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

  • Jr. Member
  • **
  • Posts: 72
Re: Trying to speed up the results from a DB to a TAChart
« Reply #2 on: February 09, 2017, 01:48:33 pm »
Quote
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.
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][+][-]
  1.  ArrayX[High(ArrayX)].x := ViewChartQuery.FieldByName('MYDATE').AsDateTime;

I searched it before the loop eg.

Code: Pascal  [Select][+][-]
  1. var mydatevar : integer;
  2. mydatevar:= ViewChartQuery.FieldByName('MYDATE').FieldNo;
  3.  


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

Code: Pascal  [Select][+][-]
  1.  ArrayX[High(ArrayX)].x := ViewChartQuery.Fields.FieldByNumber(mydatevar).Value;
  2.  
But the speed is the same again.


Quote
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.

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.

Quote
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.

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.
« Last Edit: February 09, 2017, 01:50:30 pm by donnie »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: Trying to speed up the results from a DB to a TAChart
« Reply #3 on: February 09, 2017, 04:44:20 pm »
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][+][-]
  1. procedure TViewChartsfrm.FillMarksArrays;
  2. var
  3.   n, ix, iy, imz: integer;  
  4.   MyDateField: TFIeld;
  5.   XField: TField;
  6.   YField: TField;
  7.   MZField: TField;
  8. begin
  9.   // Get fields
  10.   MyDateField := ViewChartQuery.FieldByName('MYDATE');
  11.   xField := ViewChartQuery.FieldByName('X');
  12.   yField := ViewChartQuery.FieldByName('Y');
  13.   MZField := ViewChartQuery.FieldByName('MZ');
  14.  
  15.   // Set array lengths
  16.   n := ViewChartQuery.RecordCount;
  17.   SetLength(ArrayX, n);
  18.   SetLength(ArrayY, n);
  19.   SetLength(ArrayMZ, n);
  20.  
  21.   // Iterate through dataset
  22.   ViewChartQuery.First;
  23.   ix := 0;
  24.   iy := 0;
  25.   imz := 0;
  26.   while not ViewChartQuery.EoF do begin
  27.     if not XField.IsNull then begin
  28.       ArrayX[ix].x := MyDateField.AsDateTime;
  29.       ArrayX[ix].y := XField.Asfloat;
  30.       inc(ix);
  31.     end;
  32.     if not YField.IsNull then begin
  33.       ArrayY[iy].x := MyDatefield.AsDateTime;
  34.       ArrayY[iy].y := YField.AsFloat;
  35.       inc(iy);
  36.     end;
  37.     if not MZField.IsNull then begin
  38.       ArrayMZ[imz].x := MyDateField.AsDateTime;
  39.       ArrayMZ[imz].y := MZField.AsFloat;
  40.       inc(imz);
  41.     end;
  42.     ViewChartQuery.Next;
  43.   end;
  44.  
  45.   // Fix array lengths
  46.   SetLength(ArrayX, ix);
  47.   SetLength(ArrayY, iy);
  48.   SetLength(ArrayMZ, imz);
  49. end;
« Last Edit: February 09, 2017, 05:37:15 pm by wp »

zeljko

  • Hero Member
  • *****
  • Posts: 1594
    • http://wiki.lazarus.freepascal.org/User:Zeljan
Re: Trying to speed up the results from a DB to a TAChart
« Reply #4 on: February 09, 2017, 05:22:32 pm »
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

  • Jr. Member
  • **
  • Posts: 72
Re: Trying to speed up the results from a DB to a TAChart
« Reply #5 on: February 10, 2017, 01:40:33 pm »
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!  :o :D 8)
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.
« Last Edit: February 10, 2017, 01:45:56 pm by donnie »

 

TinyPortal © 2005-2018