Jump to content

Faster Database Access


sobakava

Recommended Posts

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;

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 4 years later...

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.

Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...