Xojo Conferences
XDCMay2019MiamiUSA

MySQL and MySQL plugin performance (Real Studio getting started Mailinglist archive)

Back to the thread list
Previous thread: Problems putting a string in EditField
Next thread: Coding Button


RB Database   -   tom.russell transport.alstom.com
  MySQL and MySQL plugin performance   -   Will Brokenbourgh
    RE: MySQL and MySQL plugin performance   -   POIDINGER, Michael [JJRAU]
     Re: MySQL and MySQL plugin performance   -   Will Brokenbourgh
    RE: MySQL and MySQL plugin performance   -   POIDINGER, Michael [JJRAU]
     Re: MySQL and MySQL plugin performance   -   Will Brokenbourgh

MySQL and MySQL plugin performance
Date: 31.01.05 03:28 (Sun, 30 Jan 2005 18:28:53 -0800)
From: Will Brokenbourgh
I'm porting a pretty large medical accounting app to Windows using VB 6 and MS Access. I had an opportunity to evaluate RB 5.5.4 and I am very impressed...so impressed that after I release this final VB build to our clients, I want to port everything to RB.

Obviously, with RB, I will no longer be using Access, so I have researched a few different cross-platform DBs. MySQL seems to be a good balance of everything my app needs and the price is right. I converted the Access DB to MySQL and tested RB's MySQL performance with the MySQL plugin and found that, on the same machine, RB takes around 3 seconds to select a single row in a large table and MS Access through VB performs the same query in about 700 milliseconds. Also, using the data control, if I move to the previous record in RB, it takes about 2.5 seconds, in VB/Access it's instantaneous. Quite a difference!

My questions are:

* Is there something I can do to increase RB's MySQL performance (besides the obvious things like RAM, faster machine, correct column indexing)?

* If not, are there plans to fix this soon?

Because of some complexities, I'd rather stay away from the ODBC plugin.

Thanks!

Will
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives of this list here:
<http://www.realsoftware.com/listarchives/lists.html>

RE: MySQL and MySQL plugin performance
Date: 31.01.05 03:45 (Mon, 31 Jan 2005 13:45:13 +1100)
From: POIDINGER, Michael [JJRAU]
>
> I'm porting a pretty large medical accounting app to Windows
> using VB 6 and MS Access. I had an opportunity to evaluate
> RB 5.5.4 and I am very impressed...so impressed that after I
> release this final VB build to our clients, I want to port
> everything to RB.
>
> Obviously, with RB, I will no longer be using Access, so I
> have researched a few different cross-platform DBs. MySQL
> seems to be a good balance of everything my app needs and the
> price is right. I converted the Access DB to MySQL and
> tested RB's MySQL performance with the MySQL plugin and found
> that, on the same machine, RB takes around 3 seconds to
> select a single row in a large table and MS Access through VB
> performs the same query in about 700 milliseconds. Also,
> using the data control, if I move to the previous record in
> RB, it takes about 2.5 seconds, in VB/Access it's
> instantaneous. Quite a difference!
>

No insult intended at the simpleness of this question, but have you indexed
the fields you select on?

Is your database remote or local, and was it remote or local when using
access?

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives of this list here:
<http://www.realsoftware.com/listarchives/lists.html>

Re: MySQL and MySQL plugin performance
Date: 31.01.05 03:55 (Sun, 30 Jan 2005 18:55:27 -0800)
From: Will Brokenbourgh
I'm not at all insulted, just happy to get some advice.

Yes, the fields selected were indexed, for example 'LastName', 'FirstName', 'SystemID', etc. I even optimized the database beforehand, just to make sure everything was okay.

Regarding remote and local, I tried both. The times I originally posted were for local. For remote (100 megabit through switch) it was about 1 second longer in each case.

Thanks!

Will
----- Original Message -----
From: POIDINGER, Michael [JJRAU]
To: 'Getting Started'
Sent: Sunday, January 30, 2005 6:45 PM
Subject: RE: MySQL and MySQL plugin performance

>
> I'm porting a pretty large medical accounting app to Windows
> using VB 6 and MS Access. I had an opportunity to evaluate
> RB 5.5.4 and I am very impressed...so impressed that after I
> release this final VB build to our clients, I want to port
> everything to RB.
>
> Obviously, with RB, I will no longer be using Access, so I
> have researched a few different cross-platform DBs. MySQL
> seems to be a good balance of everything my app needs and the
> price is right. I converted the Access DB to MySQL and
> tested RB's MySQL performance with the MySQL plugin and found
> that, on the same machine, RB takes around 3 seconds to
> select a single row in a large table and MS Access through VB
> performs the same query in about 700 milliseconds. Also,
> using the data control, if I move to the previous record in
> RB, it takes about 2.5 seconds, in VB/Access it's
> instantaneous. Quite a difference!
>

No insult intended at the simpleness of this question, but have you indexed
the fields you select on?

Is your database remote or local, and was it remote or local when using
access?

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives of this list here:
<http://www.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives of this list here:
<http://www.realsoftware.com/listarchives/lists.html>

RE: MySQL and MySQL plugin performance
Date: 31.01.05 04:14 (Mon, 31 Jan 2005 14:14:46 +1100)
From: POIDINGER, Michael [JJRAU]
> -----Original Message-----
> From: <email address removed>
> [mailto:<email address removed>] On
> Behalf Of Will Brokenbourgh
> Sent: Monday, January 31, 2005 1:55 PM
> To: Getting Started
> Subject: Re: MySQL and MySQL plugin performance
>
> I'm not at all insulted, just happy to get some advice.
>
> Yes, the fields selected were indexed, for example
> 'LastName', 'FirstName', 'SystemID', etc. I even optimized
> the database beforehand, just to make sure everything was okay.
>
> Regarding remote and local, I tried both. The times I
> originally posted were for local. For remote (100 megabit
> through switch) it was about 1 second longer in each case.
>

It's odd. I connect from Australia to a MySQL database in Europe, and for
the query

"select * from table where id < 10"
it takes 400 millisecs. table has 52000 entries

I don't have a similar query on my local systems, but it does take 6 secs to
retrieve 10000 records and instantiate each one as an object

I have not optimixed any tables nor done anything special with the plugin
(they are all indexed though)

I never use datacontrols, but I am wondering if your system has to reconnect
to the DB everytime it does something? I always use a persistent
MysqlDatabase object connected at app.open attached as an attribute to app.
It might be your lag is due to object creation/garbage collection

I also find my "local" remote db fater than localhost. Dunno why.

Mike
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives of this list here:
<http://www.realsoftware.com/listarchives/lists.html>

Re: MySQL and MySQL plugin performance
Date: 31.01.05 04:23 (Sun, 30 Jan 2005 19:23:41 -0800)
From: Will Brokenbourgh
That's amazing that the round trip from Australia to Europe is so fast!

The particular table I am referring to has around 120,000 entries and each row has about 650 bytes.

I guess I'll try testing without using the data control and connect at app.open as you do.

Thanks!

Will
----- Original Message -----
From: POIDINGER, Michael [JJRAU]
To: 'Getting Started'
Sent: Sunday, January 30, 2005 7:14 PM
Subject: RE: MySQL and MySQL plugin performance

> -----Original Message-----
> From: <email address removed>
> [mailto:<email address removed>] On
> Behalf Of Will Brokenbourgh
> Sent: Monday, January 31, 2005 1:55 PM
> To: Getting Started
> Subject: Re: MySQL and MySQL plugin performance
>
> I'm not at all insulted, just happy to get some advice.
>
> Yes, the fields selected were indexed, for example
> 'LastName', 'FirstName', 'SystemID', etc. I even optimized
> the database beforehand, just to make sure everything was okay.
>
> Regarding remote and local, I tried both. The times I
> originally posted were for local. For remote (100 megabit
> through switch) it was about 1 second longer in each case.
>

It's odd. I connect from Australia to a MySQL database in Europe, and for
the query

"select * from table where id < 10"
it takes 400 millisecs. table has 52000 entries

I don't have a similar query on my local systems, but it does take 6 secs to
retrieve 10000 records and instantiate each one as an object

I have not optimixed any tables nor done anything special with the plugin
(they are all indexed though)

I never use datacontrols, but I am wondering if your system has to reconnect
to the DB everytime it does something? I always use a persistent
MysqlDatabase object connected at app.open attached as an attribute to app.
It might be your lag is due to object creation/garbage collection

I also find my "local" remote db fater than localhost. Dunno why.

Mike
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives of this list here:
<http://www.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives of this list here:
<http://www.realsoftware.com/listarchives/lists.html>