MySQL - Handling LARGE tables

[hoburne]

Hi,

This may be obvious to most so I apologise if this seems like a basic question!

I have been working on a data analysis application for a while - the application is in 2 parts, the first collects the data in real time and the second processes it and generates reports from a 'client' interface. Its working well but it collects over a 1Gb per month so the tables I am using are getting large, an average of 45,000 records per table.

My question is, is there a guide or best practice to handling large tables? My application often reports (NOT RESPONDING) on the title bar, but LC's message box flickers with processing and debug results - which shows the application is still running. If you leave it for a few minutes then you get the result you were looking for. But as the system collects more data the NOT RESPONDING will become more frequent.

Is there a way to avoid this? I'm sure its down to my inefficient code.

Any pointers would be greatly appreciated.

Thanks in advance!

[shaosean]

Are you requesting all of the data at once or are you asking for it in chunks? Do you yield any CPU time during any loops you may be using? Post some code for better help..

[Mark]

Hi,

Redesign the database to have all data of the same type together. For example, if you have different "types" of customers and it is possible to know which "type" you're looking for, create a table for every type. A type could be e.g. location, gender, individual vs. families, etc. Split the columns of your table and put searchable columns in one table, put less important columns in the seconds table. Use an ID number to indicate which records in the two tables belong together and create an index on this ID number.

Every time you call the database takes time. MySQL is pretty good at optimizing queries by itself. Try to create your queries as long as possible, thereby reducing the number of times you need to query the database. Do as much processing as possible in the database and avoid repeat loops in LiveCode. LiveCode is to MySQL what a snail is to formula 1 racing car. If you have to have repeat loops, make sure to use "repeat ... with messages" and "wait 0 millisecs with messages".

Kind regards,

Mark

[hoburne]

Thank you both for your help.

@Mark - My LC code initially analyses the applied filters and constructs seeminly huge SQL SELECT statements to try and target as specific data as possible. However, after the data is retrieved and placed into a dg the user has the option to further filter this data and therefore "repeat" (in some places) will be unavoidable for me. Thanks for the advice about "with messages". Can you explain how this will affect the repeat process?

Are there any other best practices or ways to manage the "Not Responding" message while processing data?

Again, sorry if this seem quite newb for the intermediate board - I am a step on from "SELECT * FROM mytable" I promise!

Thanks

[bangkok]

Are there any other best practices or ways to manage the "Not Responding" message while processing data?

This is a perfectly normal behavior... When LC is working hard... the interface is not responsive anymore.

If you wish to "refresh" the display, or allow an interaction from the user, as Mark said, you have to use the trick :
"repeat ... with messages" and "wait 0 millisecs with messages".

it will allow LiveCode to "breathe".

But in anycase, is it really necessary ?

If you want your user not to "panick" and thinking that the app has crashed, you can simply display a big "Wait...." field on the screen, before to execute the script.

But in any case, it's better to leave MySQL doing the hard job. So you might change/optimize your SQL queries, in order to avoid LC to do some "post query" work.

My point : if your SQL query fetches let's say 45 000 lines, and that after you make LC processing those 45 000 lines (with a repeat loop), you always have the "not responding" thing (unless you use "repeat ... with messages" and "wait 0 millisecs with messages", but in this case the total processing time will be longer).

[Mark]

Hi,

Look at the following two repeat loops:

Code: Select all

put the secs into mySecs
repeat forever
  if the seconds - mySecs >= 10 then exit repeat
end repeat

put the secs into mySecs
repeat forever with messages
  if the seconds - mySecs >= 10 then exit repeat
  wait 0 millisecs with messages
end repeat

The first example locks up everything while the second example leaves time for updating the interface, changing a progress bar, pressing a cancel button, etc.

You will need to test what works more quickly in your case: do the complete query, including all the filtering, in the database or retrieve huge amounts of data and do the filtering in LiveCode.

Don't forget that MySQL servers are usually very fast while establishing network connections and downloading data causes lots of delays. The smaller the dataset that's actually transferred from the server the better.

Kind regards,

Mark

[hoburne]

@Everyone - Great, thank-you for the explanations.

[phaworth]

I'll add my $0.02 here!

First, 45,000 rows in a table is not a large table. To select all the rows in a table that size should take only milliseconds.

There's two parts to this - the db retrieval and the formatting and display of the data in a card. Assuming your database is approipriately structured, the data retrieval part should take a lot less time than the formatting and display. Without having more info about both those elements, it's hard to know which direction to go but I'll make a few suggestions .

The database retrieval part of this, while it should only take milliseconds, is tied up with the data display as you'll see.

The fastest way to deal with data selection like this is in two stages. First "SELECT rowid FROM myTable WHERE....." and create a cursor. The WHERE clause should determine the structure of your database to a large degree (indexes, foreign keys, etc). Selecting just the rowid is lightening fast. Then use a repeat loop to go through the cursor and for each row, SELECT item1,item2,item3, etc FROM myTable where rowid = <rowid from the current cursor row>. At that point format and display the data for the current row.

This will give you complete control over how many rows of data you present to the user in one iteration before further interaction is possible. So if you are showing, eg 20 rows at a time, you only need to retrieve 20 rows from the cursor and display them until the user requests more. In essence, you're not dealing with formatting and displaying 45,000 rows, just 20.

I don't know how you are displaying the data but if you are using a datagrid, it has a built in mechanism for dealing with this (see the dgNumberOFRecords property). Email me off list of you need more help with that.

In a shameless piece of self promotion, I must also mention that I will shortly be going into beta test for my LiquidSQL product which will take care of all this for you without writing any Livecode scripts. Check out www.lcsql.com for more info.

Pete