How to properly keep open and check a database connection?

[Monox18]

Hi forum,

I posted another problem before but I found exactly the problem, deleted it and made this one. I've been struggling with managing database connections. What is the best way to manage database connections?

Option 1 is Opening a database connection, running a query and closing the connection. That each time with each particular query. Opening a database connection with each query is particular expensive, it takes 500ms just the opening part.

Option 2 is Opening a database connection only once, then running a fast "ping" query about each 30s to keep the connection open on the server side. So it means I can just run the query all the time and its faster. I use "Select version()" as the ping query and it takes just around 200ms each ping to keep it alive. It works fine until certain point but for some reason it messes up all my code. I have to use "send KeepItAlive to me in x seconds" and of course I check the pending messages but eventually it becomes a mess. I dislike the idea of having a permanent query running all the time. It's messing it all.

Option 3 is checking the connection status before running a query. If it is not open then open it. If it is open then just run the query. Checking the status is the tricky part. How can I do that? I mean a real FAST check. Many queries take long and make the program crash. Also, if the connection is closed and I run a query it takes way tooo long and almost crashes.

I use this to check internet connections, it works fine and takes just 1ms or less. (better than pinging google for sure!)

Code: Select all

function db.CheckInternet
   local mySock, myIP1
   put "255.255.255.255:44444" into mySock
   open datagram socket to mySock
   put hostAddress(mySock) into myIP1
   close socket mySock
   if myIP1 is "127.0.0.1" then
      return false
   else
      return true
   end if
end db.CheckInternet

So, any ideas how to quickly check database connection status? best way to manage it?

[Dixie]

I guess this is one of those questions that depends on your opinion more than anything else... I am firmly in the camp that you should open a connection to the database, run your query and then close the database...

To do this I use an .lc script on the server... Why ?.. because it is an awful lot faster than running the query from your stack..
So, an example of running an .lc script

the query from the stack to the .lc script...

Code: Select all

   put "http://www.xxxxxx.xxx/rugbyapp/rugby.lc?A=fixtures&thediv=" & theLeague into theURL
   put URL theURL into temp

the .lc script...

Code: Select all

<?lc
put $_GET["A"] into tAction

   get revOpenDatabase("mysql", "localhost", "xxxxxx_rugby", "xxxxxx_boss", "xxxxxxxxx")
   put it into theDataRef
   	
if tAction = "fixtures" then
   put $_GET["thediv"] into thedivision
   
   get revDataFromQuery( tab, return, theDataRef, "SELECT * FROM fixtures where league='" & thedivision & "' ;")
   put it into temp
   put temp
   revCloseDataBase theDataRef		
   quit
end if	
?>

This example is to get all the fixtures and results for the English premiership rugby competion... the whole query, from sending it, getting the data back and laying it out in the stack (as shown below) takes an average of 60 ms... .. and this is running on an iPhone... ( I have just run it a few times right now as I had no idea before reading your post how long it was taking as it was not a concern )

but as I said earlier, my opinion is that you should open the connection to the database, do your query, get out and close it... but hey... What do I know ?... others may have different opinions about this... the last thing I can tell you though, is that using a method like, employing an .lc script, is 'so' much faster than querying the database from the desktop...

[Monox18]

Hi Dixie,

Thanks for the response. I didn't think about actually running the script on the server rather than on the desktop. The speed sounds pretty good. However I don't know much about how to do that. I am using GoDaddy's servers to store my info so I don't know if I can actually put a script over there. So, I will start reading about that.

And that option it's not faster but at least it is safer. to open the connection, run query and close connection. So the solution is not how I manage the connection but more where I manage it. Cool. Thank you.

[FourthWorld]

I guess this is one of those questions that depends on your opinion more than anything else... I am firmly in the camp that you should open a connection to the database, run your query and then close the database...

To do this I use an .lc script on the server... Why ?.. because it is an awful lot faster than running the query from your stack.

For any shared resource I'm also strongly in favor of using a server to intermediate transactions, mostly for security and robustness.

But as for performance, assuming the network latency is equivalent whether using HTTP over port 80 or any other socket comms, I would imagine the tool chain of Internet + Apache + LC Server + MySQL would take at least slightly more time (though probably just milliseconds) than just Internet + MySQL.

What accounts for the performance loss you've seen with calling the DB directly?

[dave.kilroy]

Yup - use a web service, open the database, do what you have to do and close it quick...

Also, consider minimising the number of times you have to hit the server by setting up a syncing arrangement so that where possible your app calls a local sqlite database and only gets new data from the remote server when required.

Of course whether this approach is suitable depends on the purpose of your app, and it does take more time to develop than connecting to a remote database each time, but this method does bring it's own benefits

[Monox18]

FourthWorld, after I finish the app I will then move to the intermediary web app for the purposes you mentioned. For now i'm running it straight from desktop. The performance loss is opening the connection with each query instead of just keeping it open, mostly because it is a shared database. It seems it is more a resources problem than LiveCode problem. I will move it to a dedicated server if it pays off, hopefully should be faster with more bandwidth also.

dave.kilroy, I get the idea but I don't want to manage local information. It can easily be "lost" without me even noticing. Supose I work all day, make changes locally and the when I try to upload it the internet goes down... things start to get a bit complicated to manage. If I upload and download straight to the server, getting error messages and such, I can be 100% sure that the info got where it is supposed to be.

Thank you guys!

[dave.kilroy]

Monox18, a syncing approach should of course keep data safe - otherwise it's not much use! The advantage you get is the extra responsiveness on your app and less traffic to the server, the disadvantage is the extra work involved in setting things up. So in some scenarios it's a good choice whilst in others not (and it sounds like it's right for you at this time)

EDIT: I managed to leave out a critical word from that last sentence "(and it sounds like it's NOT right for you at this time)"

[FourthWorld]

FourthWorld, after I finish the app I will then move to the intermediary web app for the purposes you mentioned. For now i'm running it straight from desktop. The performance loss is opening the connection with each query instead of just keeping it open, mostly because it is a shared database. It seems it is more a resources problem than LiveCode problem.

Was that performance difference measured?

I ask because LC Server is a CGI, so that everything in its runtime cycle, including connecting to the database, is done each time a request is handled. In most cases this is pretty fast (all straight CGIs operate this way; FastCGI and Apache modules offer persistent connections), suitable for moderate traffic. But I would expect that the additional overhead of instantiating an Apache session and invoking a CGI which then connects to the DB to execute would take at least a few more milliseconds than just connecting to the DB directly.

[Monox18]

True, dave. It sounds I gotta start doing that, just that the amount of extra work is fearful xD.

Was that performance difference measured?

Yes, I checked the times from within LiveCode using "the milliseconds" in a command I wrote, I call the command before with a True and a key to initialize it (put the milliseconds in an Array) and after the handler with a false and the same key to finish it (substract the milliseconds from it), so that I can keep track of the handlers I want to check. It reports me that just the command "put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tDatabaseError" usually takes 450-500ms.

For sure I will implement an Apache later, but for now I have to run it directly.

[dave.kilroy]

Monox18 - apologies, I managed to miss out an important word from my last posting which should have finished "(and it sounds like it's not right for you at this time)" I didn't mean to presume I knew what was right for your situation!

Only you know all the variables involved for your situation, your previous postings seemed to show you leaning towards a different solution - and as you say syncing involves more work...

Kind regards

Dave

[AxWald]

Hi Monox,

The best practice may be dependent of how your database is configured ...
Let's look at MySQL - your tables could be a lot of possible formats, mainly InnoDB or MyISAM.

Depending of what they are, MySQL will act differently:

• Case MyISAM: Often used in databases for web sites. Rather fast, but no transactions, and usually configured for many, small, fast queries. Connecting to such for a longer time will often shower you with timeouts.
• Case InnoDB: Slower, bigger tables, but has transactions and is much more safe for longer connections.

(Of course this is most simplified ...)

I'd still regard the SQLite proposal - nothing faster than a local SQLite! And I do this quite often - it's a bit more work, but once done you'll enjoy lightening fast response times. One way to do such:

At startup time, while I'm showing a splash screen
(for sure, this is pseudo code!)

• DELETE * FROM MyLocalSQLiteTable
• INSERT * FROM MyRemoteTable INTO MyLocalSQLiteTable

and if you have to update a field:

• UPDATE MyLocalSQLiteTable SET MyField to [MyNewValue]
  UPDATE MyRemoteTable SET MyField to [MyNewValue]

Depending of the size of the tables this may be a bad idea, but nicely designed tables (not too big!) and a "WHERE" clause in the INSERT statement (to ignore irrelevant data) can speed this up a lot.

Hint: A faceless LC standalone that accepts certain command line arguments and runs the SQL in the background all alone can help, too ...

I don't know if this is of help to you, I just wrote it to show some ideas, and some ways to handle the problem. I'm doing heavy database work (using Access & LifeCode combined), and I found out that often, when Access hits the fan, LC & SQLite will save my day ;-)

Have a good time!

[Monox18]

Monox18 - apologies, I managed to miss out an important word from my last posting which should have finished "(and it sounds like it's not right for you at this time)" I didn't mean to presume I knew what was right for your situation!

Sure man, no problem. I'm open to ideas. I'm actually thinking about it.

I'd still regard the SQLite proposal - nothing faster than a local SQLite! And I do this quite often - it's a bit more work, but once done you'll enjoy lightening fast response times. One way to do such:

Yes, actually it makes sense. Thank you for the idea of the splash screen.

[Saman Sjr.]

Hi Monox18,

I have a same situation like yours and here's what i do:
1. set mysql server to use SSL for security reason
2. open database at openStack "autoReconnect" option set to "true"
3. close database when exit program

regard's
SS