Android Question Import or read db with 1.000.000 and more records

svitol

Member
Hi, i have a csv file with 1.000.000 and more records. My initially idea is read and copy the records into my sqlite database, because i want my records locally, but the file is too big and the app crash (450 mb).
What is the best way to copy locally or it's better to read remotely this file? (and in this case how i can make a query on it? it's better to convert into sqlite or another db?)

Thank you.
 

DonManfred

Expert
Licensed User
Longtime User
download the file and then go through the list line by line. You can not load a 450mb file into memory
 
Upvote 0

svitol

Member
Yes, is 465 mb.
It's a good solution if I want to keep it on a server and make queries through webservices?
In this case I can receive response json files even with 1000 or 2000 lines?
 
Upvote 0

Albert Kallal

Active Member
Licensed User
Hi, i have a csv file with 1.000.000 and more records. My initially idea is read and copy the records into my sqlite database, because i want my records locally, but the file is too big and the app crash (450 mb).
What is the best way to copy locally or it's better to read remotely this file? (and in this case how i can make a query on it? it's better to convert into sqlite or another db?)

Thank you.

The trick here? Well, I guess it comes down to how often does the data change - what do you want to do with it?

sqlite on Android? It is VERY fast - in fact sqlite on your phone tends to run faster then the windows version of sqlLite - by GOOD margins!

So, you want to import the csv file - import into sqlite - and do all that work on your pc.

The 1 million row db was 290 megs. You find that pulling csv data into a db?

The db will about double in size (often a bit more).

So my rows were not too large. The csv file was 121 megs. So in your case - gee 600 megs?

So, you also of course would setup indexing on required columns.

Say againts this 1 million row db, I want all sales in Europe, a row count, and total sales value.

So, this code (the "1" is just a spare column - place holder - you see why in a sec.
B4X:
    Dim strSQL As String = "SELECT 1 as pRows,[Units Sold], [Total Cost] from Sales"
    Dim rData As Object = sqlite.ExecQueryAsync("eSQL",strSQL & strWhere, pParms)

    Wait For (rData) eSQL_QueryComplete (Success As Boolean, rst As ResultSet)
    
    If Success = False Then
        Log("error - no read")
    End If

    Do While rst.NextRow
        
        ProductCount = ProductCount + rst.GetLong("Units Sold")
        SalesTotal = SalesTotal + rst.GetDouble("Total Cost")
        RowsTotal = RowsTotal + rst.GetDouble("pRows")
        If rst.Position Mod 1000 = 0 Then
            ' update progress
            ProgressBar1.Progress = (rst.Position / rst.RowCount) * 100
            Sleep(0)
        End If        
    Loop
    
    txtRows.Text = "Rows processed = "  & RowsTotal
    rst.Close
    CallSubDelayed(Me,"BigLoop_Done")
The above took 18 seconds - looped in code 259,000 records.

But!!!!! - that's NOT how you do things. You get the sqlite data engine to do the work!!! - and when you do?
As I often noted here? , the JIT on Android goes to work - I find BETTER performance then EVEN on most desktop computers!!!!

So, lets modify the above - it will return ONE row in place of 250,000 rows.

We now do this:

B4X:
    strSQL = "SELECT count(*) as pRows,Sum([Units Sold]) as [Units Sold], Sum([Total Cost]) as [Total Cost] from Sales"

    Dim strWhere As String = " WHERE Region = 'Europe'"

    Dim rData As Object = sqlite.ExecQueryAsync("eSQL",strSQL & strWhere, Null)
    Wait For (rData) eSQL_QueryComplete (Success As Boolean, rst As ResultSet)
    
    If Success = False Then
        Log("error - no read")
    End If

    Do While rst.NextRow
        
        ProductCount = ProductCount + rst.GetLong("Units Sold")
        SalesTotal = SalesTotal + rst.GetDouble("Total Cost")
        RowsTotal = RowsTotal + rst.GetDouble("pRows")

etc.

The above code runs and we get this result:

1615522012609.png


So that's 250,000 rows. And if you do say only 20,000 rows, Say North America
in Above? it takes 0.28 seconds - it actually instant!!!

So, it really depends on what you need and how you plan to process the data.
but, without question - a database is the way to go.

In fact, a full take row scan against the 1 million rows - touching all rows? 1.5 seconds - but you have to use sql against the table - not row by row processing.

You will find that Android sqlIte runs very fast - easy you get desktop speeds, and often better if you can use sql against the table as opposed to looping code.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada
 
Upvote 0

edgar_ortiz

Active Member
Licensed User
Longtime User
At the end, I don't understand the need to load 100,000 + records into a phone database.

I think it is best to access the database from a server (web service?)
 
Upvote 0

Albert Kallal

Active Member
Licensed User
At the end, I don't understand the need to load 100,000 + records into a phone database.

I think it is best to access the database from a server (web service?)

yes, I 100% think that's the better road. I mean, who has 1 million rows of data in their pocket - and for what? That amount of data is not only large, but that ALSO means that the data becomes out of date very fast. In other words, carrying around that much data makes little sense.

I suppose if it some archive of data?? But the VERY fact of it being so large means that the "chances" dramatic increase that the data becomes "stale" or out of date even faster!!!. So more data = more chance of out of date data!

The fact that Android can shine - and actually deal with 1 million rows?
How cool this is!!!

The REAL issue? Doubt full one needs 1 million rows in their pocket. And bottoms to dollars? That data came from a database source - and thus to get one wee little record and some information? Well, the web is for that! - and data will never be out of date anyway!

I posted above since I did want to point out how absolute crazy good B4A and Android can do these things.

Before the turn of last Century? For fun, British ships would fire off a big cannon in some far away jungle land. Their ONLY reason was to impress the natives on some island.

I kind of think my post was like firing off a cannon ball to impress everyone here - but I can't say that because you can fire that cannon ball, ,and everyone goes oohhh and aahahh - how cool was that?

Well this huge row count ability is really cool! - really impressive! But practical? Just have to think grabbing one wee bit of data from some web service would be far better in near all cases! At one time, 100 rows per second was considered good rate on a computer! Now? Well, we seeing 100,000 rows per second.

But hey, you have to admit - the above was really impressive! I actually found that sqlite on Android runs faster then the same database on my PC - that's got to be impressive - but I fully admit - this is impressive, not all that practical!

And watching a ship fire a cannon ball - and a big cool "boom" for entertainment purposes? That's also cool - but not much else!!!

But, Android phones and B4A are most certainly up to chewing away on these kinds of tasks - even 1 million row databases.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada
 
Last edited:
Upvote 0

edgar_ortiz

Active Member
Licensed User
Longtime User
Why?
If there are free ram availailable, what is max file size limit?

There is a difference between coding a program and making software ...

You can make a program that works but is not functional (optimal) ... or you can sit down and think how a software would work and then code the necessary programs
 
Upvote 0

emexes

Expert
Licensed User
who has 1 million rows of data in their pocket - and for what?
1/ delivery person in a large city can easily have a million potential addresses (although in my case, I just loaded up the 120,000 within the 15km delivery radius)
2/ share trading historical data: even just daily data for charts within the last 10 ten years is thousands of companies x thousands of days

carrying around that much data makes little sense.
Unless fast and reliable access to the data is essential. Wireless networks are the opposite of this. And didn't a server farm go up in smoke yesterday?
 
Upvote 0

amorosik

Expert
Licensed User
There is a difference between coding a program and making software ...
You can make a program that works but is not functional (optimal) ... or you can sit down and think how a software would work and then code the necessary programs

No, there is no difference, they are the exact same thing
Maybe you meant that there are usable programs and unusable programs, both work but only the former can be used effectively
However, this has nothing to do with the question, which is a pure technical question, what is the maximum size limit of a file loaded in ram?
 
Upvote 0

Albert Kallal

Active Member
Licensed User
1/ delivery person in a large city can easily have a million potential addresses (although in my case, I just loaded up the 120,000 within the 15km delivery radius)
2/ share trading historical data: even just daily data for charts within the last 10 ten years is thousands of companies x thousands of days


Unless fast and reliable access to the data is essential. Wireless networks are the opposite of this. And didn't a server farm go up in smoke yesterday?

All true. There are use cases - but then again the point of my post that 1 or even 2 million rows in such a database? Not really a issue or problem - and if you have a limited criteria against that table, then such searching and results will occur in general - < 1 second!

and to be really fair? Only been a relative short number of years that such ideas and concepts are possible and affordable. So to be fair, such a large huge database and ultla fast lookup of information? Sure, you CAN NOW do these kinds of things.

So while in most cases I think such data should be obtained by other means - you HAVE pointed out that legitimate use cases exists.

With such huge memory, power and processing in your hand? Then one would accept and admit that NEW ideas - some that would be crazy nuts a few years ago are now quite easy and a good possibility! -- So while this file seems huge?

Not really - I would as noted transfer the database to the sd card, or external app storage - but other then this concpet? Heck, 5 million + rows should be rather workable.

R
Albert
 
Upvote 0
Top