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