mysql select search takes too long to display if queried with Excel

I am quite new at MySQL so please bear with me...

I have an excel sheet which queries a MySQL database...here is part the query (run from VBA macro):

...
    sql_query = "SELECT SQL_CACHE g.Id, g.Client, g.text_parsing FROM `global`.`tbl_cots` AS g  WHERE g.Client LIKE '" & strClient & "%' GROUP BY g.Id;"

With rsPubs

      .ActiveConnection = oConn
      .Open sql_query

    ' Copy the records into cell A5
    Range("A5").CopyFromRecordset rsPubs
...

The database has 1.3 million rows.

When querying a certain client (14000 rows found) takes some 10 seconds to display...

If matching records is a small number, it displays faster (<2 seconds)

I take it then, that the higher the number of records returned, the longer it takes to display.

However if I run same query (14000 rows returned) with MySQL Query Browser, the 14000 records are shown instantly.

Any ideas how to make VBA query run just as fast or is this one of the limitations of using Excel/VBA to query MySQL database directly?

I am rather confused as Inserting via VBA (from excel) I manage to insert >10000 records in 2 seconds....I can't understand why SELECT takes so much longer to run...

p.s. field 'client' is indexed

Thanks in advance

UPDATE I was accessing the database via IP (to simulate access to database from 'outside/remote'). Changed DNS to localhost and fetched results almost instant.

So...is there a way to speed up the SELECT when database is accessed remotely ?

Answers


When querying a certain client (14000 rows found) takes some 10 seconds to display...

If matching records is a small number, it displays faster (<2 seconds)

The query will run at the database in the same time no matter what program/tool is sending the request. However, most DB Query applications such as Toad or SQLDeveloper will only return the first xxx number of rows. Your VBA code will require the entire dataset to perform the update, hence it takes a shorter amount of time with a smaller resultset.

VBA is generally terrible in terms of speed (not to mention a CPU HOG), so I would suggest that you try to do all your heavy number crunching in the SQL itself and try to return as few rows as possible. By that I mean if you are returning a bunch of rows to excel, then running them through a pivot or the like to aggregate them, you would be much better off doing the aggregation at the database and returning the smallest dataset possible.


Need Your Help

Updating JFreechart dataset with timer

java timer jfreechart

I found an example about multiple charts here, and try to improve it a little bit. I want these values to be changed with time.