LiveCode Forums.

Hello,

I am new to Revolution and developing in general.

I just purchased a copy of Revolution Studio and am working with large databases.

My databases are managed using Filemaker Pro. After exporting the data to .csv format, I import the data into an SQLite manager in order to generate a .db file to use with an application that was developed in Revolution.

The problem is when the .db file is relatively large (>100MB), Revolution just hangs and I end up having to force quit the application.

Is this normal, or could I be doing something wrong? Should I just allow more time for the .db to connect considering its size?

Your help and insight is well appreciated. :)

Cheers!

savant,

You might want to make sure that the SQLite version used by SQLite manager isn't newer than the SQlite library included with Revolution.

Perhaps you are running into memory problems. Good, efficient databases are split up into tables. You could use multiple db files and make sure that there is an index to connect them.

There are also command line utilities available for SQLite, which you might try instead of the external.

What about MySQL? I am pretty sure that you won't have this problem with MySQL.

Best,

Mark

savant,

You don't say how your application interacts with your SQLite database (what UI elements you use, and what SQL statements you use to access your database).

My guess is that you are trying to load your whole dataset 100MB for each operation, which is tactically poor (eg SELECT * FROM myTable).

Maybe try your existing SQL statements with limits to verify that your app works with a limited datasets first. Try SELECT * FROM myTable LIMIT 0,100 or similar.

If you are using large datasets, you may be using DataGrid controls. In that case strategies for using the DataGrid with large datasets can be found here http://revolution.screenstepslive.com/s ... ts-of-Data with thanks to Trevor Devore.

I recently tested a much larger SQLite db file than the one you describe with Rev with no issues.

My other suspicions are (1) that the data conversion from FMPro to SQLite may have encounter problems - particulaly with the character set (I have certainly encountered problems from MySQL [latin] to SQLite (for example accented characters are a headache), (2) verify that the file created by FMPro did not contain redundant white space, and (3) that the version of SQLite used by Rev and in conjunction with SQLite Manager is appropriate (as per Mark's assertion).

The larger your database is - the more unsuitable, loading the whole dataset at one time becomes.

SQLite is a good choice for standalone apps or for system tables. However as Mark suggested MySQL is a stronger candidate for client-server, web-aware or server apps.

Kind regards, Andrew