MySQL Xojo very slow

Databases
1

I have a console application running on Linux (Ubuntu 12.04) that runs an intensive set of queries against a MySQL Server using the MYSQLCommunity plug in. Under RealBasic 2012 2.1 (using the plug in shipped with that version) a typical query and retrieve of data (from a separate server on the same local network) takes c. 10 msec. However, running exactly the same code compiled under Xojo 4.1 (using the plugin shipped with that version) takes on the order of 100 msec. Interestingly, the exact same code runs without a problem if I run it as a MacOS console application and the same general code when compiled as a desktop application runs at the same speed under Windows and MacOS in Xojo or RealBasic 2012. So it appears to be an issue with the Linux MYSQL plugin.

Overall the database operations are generally 10-20 times slower in the code compiled by Xojo than in RealBasic. I saw a recent thread indicating a similar slow down in the web application builds as well that seemed to end with ‘just code around the issue after all the query time is minimal in a Web App’. However, this appears to be a fundamental performance bug with the MySQL plugin. Has anyone else seen this or are there any ideas of what I might be doing that the new MySQL plug in doesn’t like?

[code]
Etime=Microseconds()
Rs = navdb.SQLSelect(“SELECT Ident,Latitude,Lon,State,URN,Zref FROM waypoints WHERE Ident=’”+one.id+"’ AND Type<>‘DME’ ORDER BY connected DESC")

While not rs.eof
Troute = new route_element
Troute.ID = rs.field(“Ident”).stringvalue
Troute.State = rs.field(“State”).stringvalue
Troute.lat = rs.field(“Latitude”).doublevalue
Troute.lon = rs.field(“Lon”).doublevalue
tRoute.z = rs.Field(“Zref”).IntegerValue
tRoute.URN = rs.Field(“URN”).StringValue
Src.append(troute)
rs.MoveNext
Wend
app.errlog(" loading first array “+format(Microseconds()-Etime,”###,###,##0"))
Etime=Microseconds()[/code]

2

I could recommend to try the MBS SQL Plugin and see how it performs compared to official plugin.

3

It appears to me like you are reading data from one table and writing it into another, if that is the case, then do the whole thing on the server and don’t even bring it down to the application…

4

James,

It is just a code fragment. Basically I read possible data points from a table. Evaluate them to a reduced set of points, put them in a queue, do some calculations to identify some more possible data points and repeat until I have a solution. I never write to the database. The solution is then passed to other logic that further processes and displays the result of the analysis.

Christian, I will have a look at your plug in. My main concern is that it appears Xojo may have a serious performance bug in the linux part of their sql plug in.

5

I confirm, I have the very same problem in the WE, I have an application that rely on heavy use of MySql, compiled with RealBasic 2012 2.1 if FAR faster than compiled in Xojo, same server, same application, this is one of the main reasons because I wait to buy new versions (my update plan expired few months ago) until Xojo don’t get decent.
Marco

6

Just out of curiosity, has anyone filed a bug report with all this info?

7

I filed a bug report ages ago, no answer and no action taken by Xojo team

8

Case #?

9

Couple of questions I would have are

  1. How many rows get returned by the query?
  2. Does the table have a Primary Key
  3. Is there a covering index on the connected column
  4. Do you need the Order By clause as that will have a hit as the DB engine needs to sort.

Not saying these will fix your issue but may help from a DB perspective if the indexing on the table isn’t quite right.

BTW - Big +1 on James comments about working directly on the server - get the DB engine to do as much of the work as possible :slight_smile: