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

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 ;-)