I think the acid test here is to use sqlite3 vs Livecode, since sqlite3 is the official command line tool for sqlite and likely to be more efficient than any other browsing tool. I tested a select * from a table with about 48,000 records in it, using a datagrid versus an LC screen that invoked sqlite3, passing it all the necessary parameters, reading the output and displayed it in a scrolling field. I did not do any coded timings but retrieving and displaying the records in an LC datagrid took around 3-4 times longer than in sqlite3. The datagrid was using the dgNumberOfRecords/GetDataForLine techniques outlined in the datagrid manual and there was no data formatting involved.
The crazy thing about this is that, using those techniques, I believe the datagrid loads only enough database rows to fill the number of rows that are visible in the datagrid, adding more rows as the user scrolls. So the comparison is really between selecting and displaying all 48,000 rows in sqlite3 vs selecting 48,000 rows and displaying only 11 rows (the number of rows in my datagrid). It's pretty clear that using a datagrid to display large datasets is not a good idea if you want to maximize performance unless you really need the the functionality that a datagrid provides. On the other hand, the original post seemed to indicate that, even though the elapsed time using a datagrid was several times longer than the SQLabs tool, it was still less than a second. While it's interesting to figure out why that should be, I'd guess that a response time of less than a second is acceptable from a user's perspective. Pete On Sun, Mar 4, 2012 at 10:22 PM, Kay C Lan <lan.kc.macm...@gmail.com> wrote: > No, it's not the same one. > > Looking at the SQLabs site, SQLabs "developed from scratch a brand new > ListBox that is able to display an infinite number of rows and columns in > just few milliseconds. This is a fundamental base control for the app." > They've been optemising their product since 2003, it would be surprising if > you can match them on your first try. > > I did a few tests with FireFox's SQLite Manager and for 50,000 records > (11MB) it took 504ms to display in a field that could display ALL records. > > Testing with LC I could extract the same records in 409ms but to display > them all in a field took it out to 917ms. By reducing the number of records > displayed to the size of the field, it took 410ms. Clearly LC fields are > the limiting factor here. > > I then noticed that in FireFox, although all the rows could be scrolled > down to the bottom, the columns were all truncated. So I then changed my LC > field to a 'Basic table object', set the 'Maximum editable columns' to > match exactly the number of columns I had, and then set the tab stops so > only a small portion of data of each column was displayed - similar to how > FireFox truncated it's columns. In this way I was able to display ALL the > rows in 491 ms. > > This may not be as fast as SQLabs, but I think they maybe the speed kings. > Compared to another SQLite tool I was able to get LC to perform just as > fast, if not a little faster. > > HTH > > > On Sun, Mar 4, 2012 at 7:12 PM, Jérôme Rosat <jro...@mac.com> wrote: > > > I don't know if SQLiteManager for Firefox is the same one I use (from > > SQLabs) but it display all the records in one time (I can scroll all the > > list). > > > > But thank you for the suggestion, If necessary I'm going to display only > a > > part of the records with a next button, even if I don't like very much > this > > approach. > > > > > > Le 4 mars 2012 à 03:37, Kay C Lan a écrit : > > > > > > > If the 'SQLiteManager' you refer to is the same one I use with FireFox, > > in > > > my case it only displays 100 records at a time, you have to click the > > Next > > > button to see more - so this is a performance trick you can do with LC. > > > Instead of extracting 150000 records in one go, and displaying it in a > > > field in one go( which will take a long time) only extract 100 records > > > (which will take significantly less time) and have a Next button, just > > like > > > SQLIte Manager, to retrieve the next 100 records. > > > > > > If I'm right, the the fact that LC only took 4.4 times longer to > display > > > 1500 times more records would suggest that you'll be able to do a lot > > > better with LC > > > > > > HTH > > > _______________________________________________ > > > use-livecode mailing list > > > use-livecode@lists.runrev.com > > > Please visit this url to subscribe, unsubscribe and manage your > > subscription preferences: > > > http://lists.runrev.com/mailman/listinfo/use-livecode > > > > > > _______________________________________________ > > use-livecode mailing list > > use-livecode@lists.runrev.com > > Please visit this url to subscribe, unsubscribe and manage your > > subscription preferences: > > http://lists.runrev.com/mailman/listinfo/use-livecode > > > _______________________________________________ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > > -- Pete Molly's Revenge <http://www.mollysrevenge.com> _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
