Why does SQLyog returns MySQL query results 10x faster than my Delphi program?

Question

select rectype,jobid,jobrecid,template,assignedto,entereddt,enteredby,ref1,processed,processeddt,
processbydt,title,description,connectlanhandle,finished,updateddt,ref2,cancelled,
requireaccept,acceptrejectstate,acceptrejectbydt,alert1dt,alert2dt,alert3dt,despatchallowed,
flag,ref3,projectid,duration,skillset,postcode,prefschedulefrom,prefscheduleto,customdata1,
customdata2,customdata3,hasnotes,displayjobtype,createdby,createddt,colour
 from jobs
 where updateddt >= '1982-02-05 17:25:38'
 or (processed = 'N' and
     cancelled = 'N')
 order by jobid, jobrecid

This query returns ~80000 results. SQLyog (a MySQL gui) can return the results in a visible grid in ~600ms. My Delphi program, which connects using ODBC (latest MyODBC drivers), takes ~6000ms just to do the query, without even starting to look at the results.

Any ideas on what I can do to make my program faster?

Answer 1

My guess is that SQLyog hasn't actually displayed all 80,000 results in 600ms - it may still be loading the later ones while it displays the first ones. (In particular, most GUI frameworks I've seen can't fill 80,000 rows that quickly even without a database being involved.)

You could try doing the same thing, assuming the API you're using lets you get at results in a streaming fashion (instead of transferring everything into memory before the call returns).

Answer 2

In my opinion you should think twice about your application and/or database architecture - it's no good, in dealing with 80K records. Try to narrow your query - if you wouldn't make your life simpler - nobody would $)

Answer 3

You can use LIMIT 0,1000 on query, then change it when user reaches far end - by checking in OnAfterScroll event.

Answer 4

I guess it is because SQLyog uses native MySQL C API (direct connection) whereas you are using an ODBC connector. Did you tried 3rd party connectors like MyDAC from Devart ? You can grab a free trial from there and test your application with it.

FWIW I'm using MyDac for years now and I'm really pleased with (performance / prize / support)

Answer 5

SQLyog won't probably load all 80000 rows at once, at least some db tools which I use do "load on demand" when scrolling. If you need absolutely to get all the records at once, considering using thread to execute the query and populate the internal array.

Answer 6

I have tried this my self on basic table (no join), and found even if you click on "Show All" checkbox, SQLyog will not bring all the results on grid immediately, try it your self by moving scroll button to lowest area, you will notice sqlyog will slow down for few moments, and bring more result to show.

also ODBC known to be slower because it add more layer to the native access,so try with MyDac from DevArt which is using direct access to mysql (even without mysql client library).

and as most of all said, never try to show the user 80,000 record by one time.

BTW, the official MySql GUI tools from sun built using Delphi ;-)

Answer 7

As others have said, it's likely because SQLyog isn't loading all the records up, and probably limiting it to 200 or so.

In order to resolve this performance issue, you can use sneaky tricks. Since you're basically loading a cache of the dataset into your application at startup, you could thread this. It would still take 6 or 8 seconds to load, but your application would still start up and the UI would be usable. If someone did something that required the cache before it was loaded, you could simply display the hourglass cursor, or a message saying "one moment please..." until the cache was ready.

One thing to be aware of when doing data access in a thread is that you usually will need to create a separate database connection in your thread. Something like this:

type
  TLoadCacheThread = class(TThread)
  private
    FConnection : TODBCConnection; // Or whatever, I don't use ODBC :-)
    FQuery : TODBCQuery;
    FMemData : TkbmMemTable; // This is what I use, YMMV
  protected
    procedure PopulateCachedDataset;
  public
    constructor Create; override;
    procedure Execute; override;
  end;

constructor Create;
begin
  inherited Create(True); // create suspended thread
  FConnection := TODBCConnection.Create(nil);
  // Set any properties for the connection here.
  FQuery := TODBCQuery.Create(nil);
  // Set any properties for the query here.
  FQuery.SQL.Text := 'select * from mytable';
  Resume;
end;

procedure Execute;
begin
  FQuery.Open;
  FMemTable.LoadFromDataset(FQuery);
  Synchronize(PopulateCachedDataset);
end;

// The idea here is that you're loading into a mem dataset, which can then
// quickly be copied to another memory dataset, rather than loading the
// cached data directly from FQuery, which is slow and why we're threading
// in the first place. This assumes you have some kind of globalsettings unit
// or class, and it has a cacheddataset variable or property.
procedure PopulateCachedDataset;
begin
  GlobalSettings.CachedDataset.LoadFromDataset(FMemTable);
end;

That's the basic idea, anyway. There are other ways that are more complex but technically superior, like making the GlobalSettings.CachedDataset load the data on-demand, so the first time you access it it'll be slow, but subsequent times will take longer, etc. However, it'll depend on your needs.

HTH