LiveCode Forums.

Hi,
I need to extract 400.000-800.000 of record from a few oracle tables and put all of them into a datagrid.
Each record should have a total of 40 characters in 15 columns...

I can extract them and, if the number of lines if low enough,everything works fine, just using But with more then 300 thousands of records, the allocated memory goes over 2GB and livecode stops working (I get some C runtime error on my windows xp 32 bit machine).

Therefore I've read about large amount of data and datagrid, so I've decided to put all the data into a custom property and through I show only the records needed at time. Well, now it wokrs, but it is too much slow.

Is there anything more I can do to get higher efficiency?

Fisrt of all, there is this script: http://livecodeshare.runrev.com/stack/7 ... administer
It shows you some hints about SQL and Livecode.
Your only problem is line number, you should test the maximum allowed, and extract no more than 32'000 (or less) rows at time. Does it any sense to see on the screen more than 30'000 rows?

MaxV wrote:Fisrt of all, there is this script: http://livecodeshare.runrev.com/stack/7 ... administer
It shows you some hints about SQL and Livecode.
Your only problem is line number, you should test the maximum allowed, and extract no more than 32'000 (or less) rows at time. Does it any sense to see on the screen more than 30'000 rows?

Hi,
that's an italian script

I've read it, but it doesn't show a way to handle hundreds of thousands of records on a datagrid .
My query extracts data from a very big table (millions of records) and it already takes quite some time to extract what we need, which is about 400.000 records.
The user will have to see all of those record, sort and filter them in many different ways.
I'd like to do it without making more queries to the Oracle DB because it would take too much time.
That's why I'd like to have the whole 400.000 records on the datagrid.

Is it impossible to achieve?

400,000 records is a lot to ask the user to sift through. If you have enough memory it should work as far as the DG is concerned, but it may strain memory.
Might there be some way to further filter that to the info the user's looking for?

My experience is: database softwares usually never show so many rows to the user. You should split answer in pages made of less data, try to limit query result to 10'000 rows, then add navigation arrows to next page with the further 10'000 rows.
If you insert the LIMIT keyword in your query, all operations are faster.

The Datagrid provides a way to handle large numbers of records. Essentially, it calls a handler in your script whenever it needs more data to display and it's up to you to provide them.

You can check it out at http://lessons.runrev.com/m/datagrid/l/ ... ts-of-data. It is quite complex and disables some. other features of the datagrid.

Pete

phaworth wrote:The Datagrid provides a way to handle large numbers of records. Essentially, it calls a handler in your script whenever it needs more data to display and it's up to you to provide them.

You can check it out at http://lessons.runrev.com/m/datagrid/l/ ... ts-of-data. It is quite complex and disables some. other features of the datagrid.

Pete

Hi Pete,
as I wrote on my first post, what I've done is:
set the dgNumberOfRecords Property and put the GetDataForLine command
It works, but not for 400 thousands of rows.

I will try to show only 32.000 rows at time and add a button to see the next records, but this way, I have no idea how to let the user order all the data by clicking on column names.

Thanks

Sorry, must have missed that in your original post.

One technique I've used in the past is to call revQueryDatabase to create a cursor containing only the rowids of the rows you want to present to the user. In your getDataForLine handler, use the revMoveTo.... functions to get hold of the required rowid, then revQueryDatabase to return the data for that rowid. Even though that is more db accesses over all, it appears to happen more quickly to the user.

Another possibility might be to write the Oracle rows into a temporary local SQLite database then retrieve the the data from there in chunks using LIMIT and OFFSET to get the next n rows you need. You'd have to experiment to see if the overhead of writing the SQLite database is worth the effort but SQLite is very fast at this sort of thing especially if you enclose the INSERTs in BEGIN/END statements and make sure there are no indexes to update.

As others have said though, I think you will inevitably have user interface issues trying to present that many rows, for example the sorting issue you mentioned.

Pete

D4vidrim wrote:
I will try to show only 32.000 rows at time and add a button to see the next records, but this way, I have no idea how to let the user order all the data by clicking on column names.

Thanks

Just change the On MouseUp message of the order button. It will perform another query with ORDER BY ... (ASCENDING / DESCENDING ) LIMIT 32000 and then will display the result.

Because I don't want to make too many query to the Oracle DB (for example just to sort rows), I've decided for a different solution:
I'm using a default table field instead of data grid. It requires much less memory and it can handle hundreds of thousands of records.
To sort for each field I' added a button for each field, when I click one of them, a sort command is called.

The only disadvantage I've found so far is that I cannot set different sizes for each column. But this is less important than achieving the result I wanted!

I don't agree with you. I think that you should work with queries, and improve your queries. You can speed up your result to milliseconds just optimizing your requests.
For example, if you use the BEGIN TRANSACTION and COMMIT TRANSACTION, you speed up thousand times a result. Moreover using the LIMIT keyword, the server stops the result reaching your limit. These are the keys to really works with tons of data.

The only disadvantage I've found so far is that I cannot set different sizes for each column.

If you use a Datagrid (the button under the basic table), you'll obtain a table complete with sort buttons; where you can set a different size for each column.

See also: http://livecode.wikia.com/wiki/Data_grid and http://livecode.wikia.com/wiki/Datagrid_API

I'll look into "Begin transaction" and "commit transaction", because I don't know them.

D4vidrim wrote:The only disadvantage I've found so far is that I cannot set different sizes for each column. But this is less important than achieving the result I wanted!

Just set the tabstops property of the table to make the columns whatever width you want. You'll find them in the Table pane of the Inspector palette, or you can set them by script.

Also, BEGIN and COMMIT won't help you with the speed of SELECTs unfortunately. They only make a performance difference if you are executing many INSERT/UPDATE/DELETE statements.

phaworth wrote:Also, BEGIN and COMMIT won't help you with the speed of SELECTs unfortunately. They only make a performance difference if you are executing many INSERT/UPDATE/DELETE statements.

Theoretically you are right, practicality not. I have tested on my skin with request of just 1 SELECT of thousands of data an impressive time difference.
I really don't know the reason, but the BEGIN TRANSACTION seldom activates some internal database optimization. So it's always an excellent choice when the database takes too time.

That's very interesting. I've never bothered with BEIGN/END around a SELECT but I may have to try it. Which version of SQL are you using?