DataGrid and large amount of data

LiveCode is the premier environment for creating multi-platform solutions for all major operating systems - Windows, Mac OS X, Linux, the Web, Server environments and Mobile platforms. Brand new to LiveCode? Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

D4vidrim
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 61
Joined: Fri May 31, 2013 9:32 am

DataGrid and large amount of data

Post by D4vidrim » Mon Jul 14, 2014 11:13 am

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

Code: Select all

set the dgdata of grp "grSwitchAgg" of cd "Main" of stack "ExtSwitch" to theSwitchAgg
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

Code: Select all

command GetDataForLine pLine, @pDataA
   set itemDelimiter to tab
   put pLine into pDataA["#"]
   put line pLine of the uFlussiXmlDel65 of stack "ExtSwitch" into theRecord
   put item 1 of theRecord into  pDataA["#"]
   put item 2 of theRecord into  pDataA["POD"]
   put item 3 of theRecord into  pDataA["GDL"]
   put item 4 of theRecord into  pDataA["MESE_RIF"]
   put item 5 of theRecord into  pDataA["PNO"]
   put item 6 of theRecord into  pDataA["RNO"]
   put item 7 of theRecord into  pDataA["SNM"]
   put item 8 of theRecord into  pDataA["SNF"]
   put item 9 of theRecord into  pDataA["SNS"]
   put item 10 of theRecord into  pDataA["PDO"]
   put item 11 of theRecord into  pDataA["RFO"]
   put item 12 of theRecord into  pDataA["SOF"]
   put item 13 of theRecord into  pDataA["SOS"]
   put item 14 of theRecord into  pDataA["TOT"]
end GetDataForLine
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?

MaxV
Posts: 1580
Joined: Tue May 28, 2013 2:20 pm
Contact:

Re: DataGrid and large amount of data

Post by MaxV » Fri Jul 18, 2014 9:49 am

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?
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

D4vidrim
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 61
Joined: Fri May 31, 2013 9:32 am

Re: DataGrid and large amount of data

Post by D4vidrim » Tue Jul 22, 2014 3:23 pm

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?

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 10052
Joined: Sat Apr 08, 2006 7:05 am
Contact:

Re: DataGrid and large amount of data

Post by FourthWorld » Tue Jul 22, 2014 5:52 pm

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?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

MaxV
Posts: 1580
Joined: Tue May 28, 2013 2:20 pm
Contact:

Re: DataGrid and large amount of data

Post by MaxV » Wed Jul 23, 2014 2:02 pm

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.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: DataGrid and large amount of data

Post by phaworth » Wed Jul 23, 2014 8:26 pm

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

D4vidrim
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 61
Joined: Fri May 31, 2013 9:32 am

Re: DataGrid and large amount of data

Post by D4vidrim » Thu Jul 24, 2014 4:24 pm

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. :oops:

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

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: DataGrid and large amount of data

Post by phaworth » Thu Jul 24, 2014 7:25 pm

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

MaxV
Posts: 1580
Joined: Tue May 28, 2013 2:20 pm
Contact:

Re: DataGrid and large amount of data

Post by MaxV » Fri Jul 25, 2014 10:34 am

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. :mrgreen:
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

D4vidrim
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 61
Joined: Fri May 31, 2013 9:32 am

Re: DataGrid and large amount of data

Post by D4vidrim » Sun Jul 27, 2014 10:01 am

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!

MaxV
Posts: 1580
Joined: Tue May 28, 2013 2:20 pm
Contact:

Re: DataGrid and large amount of data

Post by MaxV » Mon Jul 28, 2014 11:50 am

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
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

D4vidrim
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 61
Joined: Fri May 31, 2013 9:32 am

Re: DataGrid and large amount of data

Post by D4vidrim » Mon Jul 28, 2014 5:17 pm

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

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: DataGrid and large amount of data

Post by phaworth » Tue Jul 29, 2014 12:45 am

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.

MaxV
Posts: 1580
Joined: Tue May 28, 2013 2:20 pm
Contact:

Re: DataGrid and large amount of data

Post by MaxV » Tue Jul 29, 2014 2:18 pm

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. :lol: 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.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: DataGrid and large amount of data

Post by phaworth » Tue Jul 29, 2014 4:49 pm

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?

Post Reply