MySQLCommunityServer is too slow in WEB applications.

Databases
1

This is the code for test:

[code]//----------------------------------------------------------------------
dim MyDB as new MySQLCommunityServer
dim Register as RecordSet
dim n as integer, TimeStart as integer, TimeEnd as integer
dim Milliseconds as integer

mydb.UserName=“root”
mydb.Password=“youpassword”
mydb.Host=“127.0.0.1”

if mydb.Connect then

TimeStart = (Ticks/60)*1000
system.DebugLog “Time Start :”+str( timeStart )

for n=1 to 1000
  Register=myDB.SQLSelect( "SELECT * FROM test.test" )
  Register.Close
next

TimeEnd = (Ticks/60)*1000 
Milliseconds = TimeEnd - TimeStart
System.DebugLog "Time End : "+str(TimeEnd)
System.DebugLog "Millseconds: "+ str( Milliseconds )

end if
//----------------------------------------------------------------------
[/code]

If you run this code (with your own server and password) in a WEB Project, the resuls are this:

: Time Start :1110781950
: Time End : 1110798983
: Millseconds: 17033

But if we run this code in an Dekstop Project (or console Project ), the resuls are this:

Time Start :1111084250
Time End : 1111084683
Millseconds: 433

Why in a desktop application the code run in 433 milliseconds, and the same code in a web application the code run in a 17033 millisconds ??

2

I could be wrong. but the Register.Close should not be inside the For Next loop.

see http://documentation.xojo.com/index.php/RecordSet.Close

"If you try to use a RecordSet after it has been closed, an UnsupportedOperationException is raised. "

3

You didn’t say where you are running this code: Is this in the App.Open, Session.Open or simply in a webpage? What platform are you testing on ?

4

Hi:

First of all, please excuse me my very bad english (I’m spanish).

John Hansen: The problem not is the recordset close. If you see the code, this is only for the know time it takes to open and close a recordset 1000 times.

Bob Keeney: The platform is Mac osx. In a Button.action in any web page. Not matter the place of code (this only run one time).

The problem is the platform where this code is executed. If you execute this code in a Desktop application, then the Recordset.SQLSELECT takes 0,89 milliseconds in execute, but if you run the same code in a WEB application, the recordset.SQLSECT takes 17 milliseconds. Many times this operation is very slow.

I made this tests:

REAL STUDIO 2012 Release 1: Desktop (Recordset.SQLSELECT) takes 0.89 milliseconds. WEB application (Recordset.SQLSELECT) takes 0.89 milliseconds.

XOJO 2013 Release 4.1: Desktop (sqlselect) takes 0.89 milliseconds WEB (sqlselect) takes 17 milliseconds

And a curious test

XOJO 2013 Release 4.1 (with MySQLCommunityPlugin.rbx from RealStudio 2012 Release 1) takes the same times of Real Studio 2012 R1

The problem is the Plugin from XOJO only in WEB applications, not in desktop.

5

I tried it. Yes, the web is slower.
That is because it runs on a thread, so it yields CPU time to other threads. For my test it spends 60% waiting.
The desktop runs query on main thread and does not yield.

6

Thanks Christian :wink:

You know any way for reduce this time in web apps ? Maybe other way for call a mysql functions ?

7

Use less queries. You probably do something wrong if you need so many queries that this really matters.

8

We are using MySQL in a fairly large web app and we’ve not noticed any issues. Honestly, the latency of the internet is big enough where we haven’t seen any speed issues.

9

Hi Javier,

and I would like to add that if you are showing huge amount of results like 1000 records in a weblistbox with 5 or more columns in each row you should take into consideration that this data first must be transferred to the users’ web browser. Better work with LIMIT and scrollable Pages to keep your web app fluid.

10

[quote=55018:@Christian Schmitz]I tried it. Yes, the web is slower.
That is because it runs on a thread, so it yields CPU time to other threads. For my test it spends 60% waiting.[/quote]

is a lot. :frowning:

it would be interesting to see if it’s as slow with SQLITE (closer communication with the app), and the MBS SQL plugin.

11

Olivier, please try.

For Tomas I can suggest using a timer on the webpage. do the query, fill the table with 20 record and keep the recordset in a property. Than start a timer and in the timer add 20 more rows every 500 ms. This way your listbox loads smoothly.

12

Many thanks for yout replies, I will optimize my procedure. I dont know why the new mysql plugin is slowest than the older. :-?