Android Question Can B4XTable handle a large SQLite table?

[Diceman]

I'm playing around with B4XTable and it looks very capable if I want cross platform support (B4A,B4i,B4J). I was going through the code from B4XPages_Sqlite2.zip and discovered it loads all of the rows from the SQLite table into a List. I'm wondering if what happens with a very large SQLite table that has 10k rows in it. Do we really need to load all 10k rows into a List called "Data" then execute "bxtTable.SetData(Data)" to load the list into bxtTable? Why not load only the 10 rows that are being displayed on the screen into the List (or buffer it with the first 100 rows)? This of course means the PgDn/PgUp navigation will have to use "Select ... from table ... limit 10 offset 9" and a filter will use the SQLite where clause and the SQLite "Order By" clause will be used to sort it.

Here is the code from the example app that I downloaded:

'Shows the database in a table in a B4XTable
Public Sub ShowTable
    Private Query As String

    ReadDataBaseRowIDs           'All RowIds are loaded from the SQLite table
    
    Query = "SELECT FirstName, LastName, City FROM persons"
    'depending if the filter is active or not we add the filter query at the end of the query
    'the filter query is defined in the Filter Activity
    If Filter.flagFilterActive = False Then
        btnFilter.Text = "Filter"
    Else
        btnFilter.Text = "UnFilter"
        Query = Query & Filter.Query
    End If

    Dim Data As List
    Data.Initialize
    Dim rs As ResultSet = SQL1.ExecQuery(Query)
    Do While rs.NextRow    'All data rows are added to the Data list.
        Dim row(3) As Object
        row(0) = rs.GetString("FirstName")
        row(1) = rs.GetString("LastName")
        row(2) = rs.GetString("City")
        Data.Add(row)
    Loop
    rs.Close
    bxtTable.SetData(Data)   'Load the List into bxTable
    
    UpdateSelectedEntryDisplay
End Sub

Am I on the right track if I want to handle a large SQLite table? Or did I miss something? I don't have the code for B4XTable so I can't see what it is doing internally.

TIA

 

[LucaMs]

I'm wondering if what happens with a very large SQLite table that has 10k rows in it.

I would fire the programmer

We have to get used to loading a limited number of records (and also to not create tables with 100 fields).

 

[Albert Kallal]

Hum, what to do , what to say?

This all comes down to how big something is relative to how big the bucket you are working with!

The problem here is human cost time vs that of hardware time.

I recall when a 10,000 row database was quite large - you had to be carefull.

Now? Well, I am betting that data pull and load takes well under 1 second. And you can elminate some of your loops!

eg:

    Dim strSQL As String = "SELECT ID, HotelName, City, FirstName from Hotels WHERE " & _
                           "ID <= " & EditText1.Text
        
    B4XTable1.Clear
    
    B4XTable1.AddColumn("ID", B4XTable1.COLUMN_TYPE_NUMBERS)
    B4XTable1.AddColumn("HotelName", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("City", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("FirstName", B4XTable1.COLUMN_TYPE_TEXT)

    B4XTable1.SetData(DBUtils.ExecuteMemoryTable(sqlite,strSQL,Null,0))

So above will eliminate "your" loop, and use the internal lib code. But then again, I don't know how well DBUtils is optimized.

Not sure - but is there a list data page library handing around some place?

So, right now? Because you have GOBS of memory and processing? Well, 10k rows is approaching crazy, but it runs in a flash. So, I guess we don't care all that much.

At one time? Well, we would call the Town Sherriff , form a posse, and ask you to leave town at sundown for abuse of computers.

So 10k is workable without doing this the right way.

But, as always, this comes down to how big?

Lets assume your table is 100k rows!!!!
(there is always larger tables hiding in some cave somewhere on planet Earth!!)

Well, now, we back to square one. In other words, how big is too big?

So I think 10k or less? You can get away with that crazy big table load. It is "crazy" but we have those resources right now.

But what about 100k rows?

Well, then I would suggest that you build a data pager, and thus avoid the cruelty to computers society getting on your back.

So, yes, I think a data pager - start, end makes the most sense. However, such a data pager can cause issues if the database engine does not support this ability.
FYI: (sqlite does support "limit like : LIMIT [no of rows] OFFSET [row num])

So a data pager could be built here. Then even if your table has 100k rows, then performance would be fantastic.

So just about any approach that pulls only 10 or say 20 rows for the display? You going to save tons of memory and resources. You will trade this against hitting the database more often - that will be more CPU but then again, we have more CPU then we do memory resources right now.

And I 100% accept that your question and point of "Why not page the data - seems crazy?"

yes, we all agree that's how this should work.

But, given the elephant size memory and processing we have? You can likely get away working with a 10k table.

but, anything much larger? it will come down to how hard you want to press the memory stack on the given device.

Busy these days - but someone here must have built a data pager "thingy".

So, should we call the Society for abuse and mistreatment of computers? Well, perhaps - but if what you have runs ok, then go with what you have, but do keep a eye on these boards and consider some kind of data pager system (but that is human cost + time - something we are all short of!!).

Good luck. I wish I had better advice. But your question + instincts to page the data? yes, a very good idea.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Erel]

You can see the code of B4XTable inside the b4xlib file (it is a zip file).

Once you set the data into B4XTable, everything is paged of course.
However B4XTable doesn't work with your specific database. You need to pass the data to B4XTable which then builds an in-memory database. Note that B4XTable does all the work asynchronously so it shouldn't affect the app performance. You should also read the data asynchronously before you call SetData.

 

[Diceman]

You can see the code of B4XTable inside the b4xlib file (it is a zip file).

Once you set the data into B4XTable, everything is paged of course.
However B4XTable doesn't work with your specific database. You need to pass the data to B4XTable which then builds an in-memory database. Note that B4XTable does all the work asynchronously so it shouldn't affect the app performance. You should also read the data asynchronously before you call SetData.

A lot of developers use SQLite and would like to display and update their data using B4XTable directly on their own SQLite table. Unfortunately there are a couple of problems if they simply copy their SQLIte table contents to a List and import that to B4XTable which creates a memory table out of the data. This removes the intelligence from the table and it's like working with a plain flat file CSV file.

1. The B4XTable will ignore constraints and referential integrity that may have been defined in the original SQLite table. This will introduce bad data into the memory table and the application.
2. For every update that B4XTable makes to your memory table, the developer is going to have to perform the same operation to his SQLite table using the B4XTable event DataUpdated. This creates a lot more work for the developer. He also has to handle the exception in this event when the update causes a constraint violation or referential integrity violation to his database, and then reverse the changes to your B4XTable memory table that were just made.
3. Exporting the contents of an existing SQLIte Table into another SQLite table is ok for small to medium size tables only. But if the table is large or the record has blobs, it can consume a lot of extra memory because B4XTable is currently using a memory table.

If the developer wants write access to his SQLite table using B4XTable then it is going to get complicated, fast.

Why can't B4XTable have a sub where the developer passes an SQL, TableName, Where, Params(), and Sort parameters for B4XTable to use, like:

          B4XTable1.ConnectSQLiteTable(Sql1, "Invoices", "Cust_Id=?", array as String("123"), "Inv_Date Desc")

This way B4XTable will connect to the table and update the table directly without going through a memory table. Any constraint violation or referential integrity violation will be handled internally by B4XTable and the data integrity of the table will be preserved while making it efficient on large tables. The SQL Where clause and Order By clause will display only those records that are necessary and in the order the user wants.

B4XTable can still have the SetData() for loading CSV or readonly data, but for write access to SQLite tables I feel direct access to the table is the only way.

TIA

 

[Diceman]

Hum, what to do , what to say?

This all comes down to how big something is relative to how big the bucket you are working with!
The problem here is human cost time vs that of hardware time.
I recall when a 10,000 row database was quite large - you had to be carefull.

...
Lets assume your table is 100k rows!!!!
(there is always larger tables hiding in some cave somewhere on planet Earth!!)

B4XTable also runs on Windows using B4J where SQLite can easily have 1 million rows in a single table. If I had to copy 1 million rows from my SQLite table to a List then import it into B4XTable every time the app ran, I might need a bigger bucket.

There are indeed bigger tables lurking out there. The largest table I ever created was 100 million rows in MySQL. That was fairly large. It still pales by comparison of what the big MySQL server farms were doing at the time.

 

[Erel]

Why can't B4XTable have a sub where the developer passes an SQL, TableName, Where, Params(), and Sort parameters for B4XTable to use, like:

B4XTable is not a SQLite browser. It is a sophisticated table interface. The data can come from million types of sources.
You are describing an edge case. Take the source code and modify it to work with the database directly.

 

[Albert Kallal]

Take the source code and modify it to work with the database directly.

I had no idea that the source was in that gem stone - WILL take a lookey!!!

I don't think making a editor out of it is all that great as you note.

However, adding a data pager that limits data pulls? For sure! And I will consider doing so!

But as I noted? It runs so bloody fast - 10k rows just loads under 1 second. It just don't matter. So, until that bridge needs to be crossed?

I see no need for such changes.

As for the other comments about data binding? About data editing by Diceman? Oh yes - but I don't have the time here to write a REALLY long post - that subject about data bound controls is dear to me..

I have a few forms, and then thought? Why write code to load up the controls on the form? Looking around? Under the rugs here?
Grabbed the state manager - that's been around these forum for what - 9 years? What a little gem stone!

So, now I have a routine called FLoadMap, and FWrite Map. I just call it to save data from a view/form back to the database.
(don't even have to declare the control names in the activity module).


So, to load my form, I do this:

strSQL = "SELECT IDA, Date, Task, [Start Time], [End Time], [Gross Hours], [Net Hours], Rate, Total," & _
             "Note, InvoiceIDA, LastUpdate " & _
             "FROM InvoiceDetail WHERE IDA = " & gID
    
    oldRecord = DBUtils.ExecuteMap(Main.sqlite,strSQL,Null)

    MyCode.Floadermap(Activity,oldRecord)

And I get the form with data filled for me:

However, boy - am I hurting for time - I am oh so eager to jump over to B4X pages - EVERY one of the bullet points outlined in your video.

Sad part? I writing lots of code to do above things - and not the road I should be on - but I'll get there!

What was my real pain point in above - given this thread now talking about data bound controls?

Missing the concept of internal vs external formats for controls.

So, I miss some "system" that does the formatting from external display, to internal. And this is not limited to date/date + time, but numbers also.
I had to write a wee bit too much code to deal with date formats (and shuffle from internal to external).

So my "loader" did load up "most" controls on that page. But I did have to double up some controls - (hidden) since the formats the data comes in (from database) is not what I want for external display.

Anyway - my post is already too long - my very sorry - I could go on and on!!!

But, I do and will "transfer" my data binder routines to B4x pages - just looking for time to make this jump.

Regards,

Albert D. Kallal
Edmonton, Alberta Canada

 

[Diceman]

B4XTable is not a SQLite browser. It is a sophisticated table interface. The data can come from million types of sources.

You are describing an edge case. Take the source code and modify it to work with the database directly.

Ok, no problem. I'll modify the source.

You are describing an edge case.

That is where we have a difference of opinion. SQLite.org states " SQLite is used by literally millions of applications with literally billions and billions of deployments. SQLite is the most widely deployed database engine in the world today."
SQLite is the primary database for cell phones, tablets and personal databases on Windows and Linux so I'd hardly call it an "edge case".

 

[KMatle]

See my example relating to db-sizes: https://www.b4x.com/android/forum/threads/b4x-sqlite-with-6-million-rows.91563/

Summary:

1. Size doesn't matter (if you have a good db-design with indexes, etc.)
2. On a phone it doesn't make sense to have millions of rows (more a "server-thing").
3. As a user sits behind a device, don't show more rows than he/she can handle (e.g. 50 and load another 50 and so on). Example: Select ... from ... where ... limit 50
4. As always: Only use resources you/the user really needs at that time
5. Split data and UI like B4xTable is a helper to show any grid-formed data (can be data from SQLite of course). Loading 100K of rows never makes no sense for a user.
6. If you need almost all db-entries (e.g. send emails for all 500K customers) you won't do this from a phone. This would be a server-task.
7. My example can access any row in 0.003 secs or less. No magic but a straight db-design (ok, just two tables so it's no real "design").
8. If you have a multi-user system with 1000 users accessing the same db you need to use less resources as possible (see #4)
9. Even on a standalone system you get into trouble als the app will get slow or has memory problems
10. Design the db as you will need it later to have fast access and not as the data looks like.
11. See any data as an objects (like customers, invoices, items, etc.) and their relations. This makes it easy to handle the data.

 

[Diceman]

See my example relating to db-sizes: https://www.b4x.com/android/forum/threads/b4x-sqlite-with-6-million-rows.91563/

Thanks for your comments. Let's review them one by one which should help to clear things up.

1. Size doesn't matter (if you have a good db-design with indexes, etc.)
2. On a phone it doesn't make sense to have millions of rows (more a "server-thing").

But it's not my SQLite table that I'm displaying. It belongs to the client and they are using the phone or B4J to view and edit it. I have little control over how they designed it. It could be a 5k row table with large blobs in it, or a 100k row table with just text. I thought B4XTable would be ideal for that because the grid pages the data and displays only 10 rows or so at a time. A grid displaying 10 rows shouldn't create memory problems, should it? Let's find out.

3. As a user sits behind a device, don't show more rows than he/she can handle (e.g. 50 and load another 50 and so on). Example: Select ... from ... where ... limit 50
4. As always: Only use resources you/the user really needs at that time

I agree. But to use B4XTable I have to load all the data from the client SQLite table into a List and then use SetData() to import it into B4XTable which uses a memory table to store the data. It would be far more efficient to have B4XTable access the SQLite table directly to conserve memory.

5. Split data and UI like B4xTable is a helper to show any grid-formed data (can be data from SQLite of course). Loading 100K of rows never makes no sense for a user.

We are both in agreement here. As far as the grid is concerned, the grid in B4XTable displays only approximately 10 rows at a time from the table which is great. The problem is B4XTable uses a SQLite memory table and a List which consumes even more memory to import ALL the data into the B4XTable. If I had 100k rows in a SQLite table, then I would have to load twice that amount data into memory. This is what doesn't make sense to me. I want to be frugal with memory, as you do.

I want to use the SQLite's Where Clause to display 50 rows at a time from the table. There is no need to load 100k rows into memory to do that. The standard SQLite table can be accessed 10 rows at a time, from a 100k table or a 1 million row table and that will consume around 2k of memory. The rest of the table stays on the SD card. Memory consumption will be kept low if the table can be accessed directly because only 10 rows is displayed at a time. But for B4XTable to load all of the table rows into memory plus the memory the List consumes, is extravagant to say the least. I'd much rather access the original SQLite table directly and consume 2k rather than load everything into memory and consume 1500k.

6. If you need almost all db-entries (e.g. send emails for all 500K customers) you won't do this from a phone. This would be a server-task.

B4XTable is cross platform tool and will run using B4J on a Windows computer. If a single user would be accessing the database on their desktop computer, then they are not going to want to set up a server. That is overkill. SQLite will handle 1 million to 10 million row tables just fine on a desktop computer with the proper indexes. But I would never want to load 10 million rows or even 1 million rows into memory. That would be crazy, yet that is what B4XTable demands. Do you see my point now?

7. My example can access any row in 0.003 secs or less. No magic but a straight db-design (ok, just two tables so it's no real "design").
8. If you have a multi-user system with 1000 users accessing the same db you need to use less resources as possible (see #4)

That's not the case here. It is single user accessing the database at a time. I can access 10 rows from a 1 million row SQLite table in the same amount of time and use very little memory. But not if I'm using B4XTable.

9. Even on a standalone system you get into trouble als the app will get slow or has memory problems

I access large database tables all the time in Delphi. Not a problem because it doesn't try to load the entire table into memory. Memory consumption is relatively the same when accessing a 5k row table or a 500k table when the table is kept on the disk and not in memory. The database is optimized to do the filtering and sorting with very little memory. That is not the case with B4J and B4XTable because it insists on loading the entire table into memory. I already have an SQLite table, B4XTable should use it directly instead of creating a copy of it in memory.

12. Memory consumption problems aside, maybe you can answer this question. How do you use B4XTable to handle the constraints and referential integrity of an existing SQLite table to prevent bad data from getting entered by the user? I don't see any easy way of doing that.

I'll end up modifying B4XTable like Erel suggested to access the SQLite table directly and it will solve all of the problems I've listed.

One of the features of Delphi that I miss are the TDatasource and TDataset components that hook up the views (Edit, CheckBox etc.) to the database table. All of the operations involved in retrieving the database table values, formatting them, and updating the views with them then saving updated view values back to the table is done by these components regardless of database (SQLite, MySQL, Access etc). No coding necessary. You just tell the view what Datasource and Field to use. It's been gnawing on me for a while. One of these days I'll have to find enough time to write it.

TIA

 

[Mahares]

But to use B4XTable I have to load all the data from the client SQLite table into a List and then use SetData() to import it into B4XTable which uses a memory table to store the data

No. You do not have to store all the table data into memory and into a B4XTable. Just fill the B4XTable table with only the records you desire from the WHERE clause. B4XTable is not looking for the entire SQLite table. It is looking for a list from whatever query you build. For example:

Dim MyQuery As String=$"SELECT * FROM Invoices WHERE Cust_Id = ? ORDER BY Inv_Date Desc"$
    Dim rs As ResultSet =Starter.SQL1.ExecQuery2(MyQuery, Array As String("123"))

You create the list from that query, not from the table. Once done loading the B4XTable, clear the list.
Once you are done with that set of data, you can clear the table and reload it with a new criteria and the same list object that you cleared earlier. In addition, you know it has search feature . You can also limit the search to a given column(s). It also has a query builder and a createDtaView feature to filter.
You can also save the list to a file and reload the B4XTable from the list if you want without rebuilding the list. Clear it after reload.

 

[udg]

I quickly read trough the whole thread and surely I missed the obvious.
Anyway, why can't we use Sqlite's LIMIT/OFFSET feature to select a relatively small set of records to initialize the table's internal in-memory db. Then, on user clicking the Prev/Next buttons we evaluate if we need to load new data and eventually we do it, passing the new bunch of records to the table and refreshing it.

Sorry if I missed the point or if I'm suggesting something that is not allowed by the component (I never used it).

 

[Mahares]

why can't we use Sqlite's LIMIT/OFFSET feature to select a relatively small set of records

You missed it big time: Post#2, Post#3 and post#10. Even OP mentioned it in post#1

 

[udg]

Bear with me, I'm browsing the Forum while in bed suffering some fever..nothing to worry about, anyway.
I should keep from posting when my mind is not 100% ready. Be it fever, sleepiness or caffeine-lack..

Edit: (rest time) On next awakening I'll read the whole thread again. Slowly. Promised. zzzzzzzzzzzzzzzzzzzzzz

 

[Diceman]

Bear with me, I'm browsing the Forum while in bed suffering some fever..nothing to worry about, anyway.
I should keep from posting when my mind is not 100% ready. Be it fever, sleepiness or caffeine-lack..

Edit: (rest time) On next awakening I'll read the whole thread again. Slowly. Promised. zzzzzzzzzzzzzzzzzzzzzz

It is a good question. I did consider moving the sorting and filtering out of B4XTable and into my own views on the form, or even modify B4XTable and throw in some events to re-execute the SQL on my table and re-import the data into the B4XTable.

But the big problem that remains is my question #12 which is:

12. How do you use B4XTable to handle the constraints and referential integrity of an existing SQLite table to prevent bad data from getting entered by the user? I don't see any easy way of doing that.​

If I can't solve that problem, then the other solutions are moot. The SQLite table used by B4XTable is in a memory database so it won't have access to my database tables for referential integrity constraints. That's why I think replacing the memory table with my SQLite table is the only viable solution if I want integrity constraints to work.

BTW, if you have a cold or flu, or want to prevent one, taking 4000 to 5000 IU of vitamin D3 a day will help knock it down.
I've been following Dr. Shiva on YT and he has come up with a vitamin protocol to defend against the Wuhan Wiggle. He has 4 degrees from MIT and specializes in the body's immune system. You can catch his videos here: Dr. Shiva "Boosting Immunity"