Faster Database Access

[sobakava]

I'm trying to plot some data (real number) from my MySQL database using Highchart.

 

 

This is how the database read routine looks like. I'm interested in showing last 100 data points. 

 

So, I'm activating the table, apply a filter to select only some specific data, then get the number of records found matching to the filter.

 

Then, I'm moving database pointer to record number (total_records  - 100)

 

And in a loop, I'm reading the values. Then I'm passing this mydata[] serie to chart plot function.

 

This loop runs for only 100 records but it is incredibly slow. (Loop itself takes ~10 seconds)

 

How can I optimize this?

 

( I tried to fill the mydata with random numbers in a loop instead of reading them from database, it is almost instantaneous.)

  form_dm.MyTable8.Active := true;


  form_dm.MyTable8.Filtered := false;
  form_dm.MyTable8.Filter := '(flt_ddt_id = 22)';
  form_dm.MyTable8.Filtered := true;
  form_dm.MyTable8.First;
  hm1_cnt := form_dm.MyTable8.RecordCount;


  if hm1_cnt > 100 then
     form_dm.MyTable8.RecNo := hm1_cnt - 100;


  if hm1_cnt > 0  then
  begin
      tx := 0;
      while ( not form_dm.MyTable8.Eof ) do
      begin

            tx := tx + 1;
            mydata[ tx ] := form_dm.MyTable8.FieldByName('flt_data').AsFloat;
            form_dm.MyTable8.Next;

      end;
  end;

 

[Oliver Morsch]

Use a query component (instead a table component) and an appropriate SQL Statement (which returns the needed 100 records).

Also be sure your seach columns are indexed columns in the databse.

[sobakava]

Use a query component (instead a table component) and an appropriate SQL Statement (which returns the needed 100 records).

Also be sure your seach columns are indexed columns in the databse.

 

Thanks.

 

I have just realized that, 

  form_dm.MyTable8.Active := true;

takes a lot of time. If I active the dataset at startup, of the application, the graph appears fast.  But the startup of the application takes time.

 

I'm using a server on Contabo running MySQL and MyDAC.

 

What can cause this?

[Oliver Morsch]

A "SELECT flt_data FROM table_name WHERE flt_tdd_id=22 ORDER BY sort_column_name ASC LIMIT 100" is much faster than a "give me 1000000 complete records and i search for the 100 values i need".

[Farshad Mohajeri]

if hm1_cnt > 100 then
     form_dm.MyTable8.RecNo := hm1_cnt - 100;

If your table has many rows above code can be very slow.

 

How many rows your table returns?

 

As Oliver said, stay away from Tables. Use a TXXXQuery and limit your rows by using filters in your SQL.

[adragan]

That's not a UniGui issue. It's a database thing !

Filters go extremly slow on any type of component. I never use filters.

 

My advice :

Create an id ( autogenerate integer ) on every record in the table  .

Put a descending index on that Id.

Use a query as Oliver showed.

Update your graphic with the respective values.

[Farshad Mohajeri]

Yes filters are slow. By saying filters I actually meant parameters in your SQL statement.

[Stanislav]

I have got highchart setup into HTMLframe using one of the Sahrzod example in this forum now trying to find a way to use highchart to populate series data from Adoquery. 

Using latest trial version of UniGui.

Any real example on how to do this is greatly appreciated as i'm very new to JS and Web development.