Android Question SqlLite slow?

G

GaryK4

Member
Licensed User
Longtime User
I am playing around with a Jumble solver program as a learning exercise. I wrote the program in Visual studio first. A word with 7 characters is solved in .02 seconds and 8 characters in .14 seconds.
On Android, 5 char = 3 sec, 6 = 16, 7 = 50 and 8 characters, I gave up after 10 minutes.
The program uses a one field database containing a 60K word dictionary and indexed. It then takes all the permutations of the jumble (length factorial) and queries the database. The found, non-duplicate words are then displayed.
PC ~ 250,000 records / second.
Sqllite ~ 100 records / second.

Does this seem correct or an I doing something wrong?
 
Sort by date Sort by votes
G

GaryK4

Member
Licensed User
Longtime User
SQL1.ExecNonQuery("CREATE INDEX words_idx ON word_list (word collate nocase)")

_________________________

Sub AddItem
Dim tmpStr, ltr As String
tmpStr = ""
For kk = 0 To jumLen
ltr = selWord.Substring2(Value(kk), Value(kk) + 1)

tmpStr = tmpStr & ltr ' Value(kk).ToString
Next

Cursor1 = SQL1.ExecQuery("SELECT word FROM word_list WHERE word = '" & tmpStr & "'")
Cursor1.Position = 0
If Cursor1.RowCount <= 0 Then
Return
End If

DispWord(tmpStr)
End Sub

______________________________


AddItem is call for each permutation of the jumbled letters.
 
Upvote 0
F

fatman

Active Member
Licensed User
Longtime User
Hi GaryK4,

I added something like this to increase the speed of sqlite:

Dim c As Cursor
c = SQL1.ExecQuery("PRAGMA synchronous=0")
c.Close

Maybe this helps.

Fatman
 
Upvote 0
warwound

warwound

Expert
Licensed User
Longtime User
Is your database file located on internal memory, built in external memory or external SD card?

If it's located on an SD card and that SD card is slow that might explain the poor performance.
I'd benchmark again with the database file located on internal memory if that's not where it's already located.

Martin.
 
Upvote 0
R

rboeck

Well-Known Member
Licensed User
Longtime User
A question to memory locations: Most tablets have a virtual sdcard and maybe a real sd card. If the database is stored at the virtual sd card, is a speed difference possible? SQLite has an in-memory option; can there be a speed difference, because we dont use hard discs?
 
Upvote 0
warwound

warwound

Expert
Licensed User
Longtime User
rboeck said:
A question to memory locations: Most tablets have a virtual sdcard and maybe a real sd card. If the database is stored at the virtual sd card, is a speed difference possible? SQLite has an in-memory option; can there be a speed difference, because we dont use hard discs?
Click to expand...

Have a read here: http://stackoverflow.com/questions/6351808/bad-sqlite-performance-on-external-storage-in-android.
That page implies that database performance is much faster when the database is NOT located on external SD card.

But now look here: http://stackoverflow.com/questions/3501451/sqlite-is-extremely-slow-in-samsung-galaxy-gt-i9000.
Performance of the built in external memory on the Galaxy S is very slow, due to the file system that Samsung have used.

So i'd say there's no definite answer - performance will vary from device to device and from SD card to SD card.
I'd expect performance of internal memory (where the app is installed) to be better than either built in external memory or SD card external memory - but that is just an assumption.

https://www.google.co.uk/search?q=android+database+performance+external+memory&ie=UTF-8&oe=UTF-8

Martin.
 
Upvote 0
G

GaryK4

Member
Licensed User
Longtime User
Attached is the code.

I created a directory on the sdcard "Jumble" for the db location.
The first time the program runs, it creates the db from the wordList.txt file.

I tried the PRAGMA suggestion and it did not help.
Also, I tried a memory database and it worked for small words. but crashed for 5+ char words.

_______________
Wrong source was uploaded :oops: Sorry!!
 

Attachments

  • Word List.zip
    158.2 KB · Views: 322
  • Jumble Solver.zip
    21.4 KB · Views: 298
Last edited:
Upvote 0
Erel

Erel

B4X founder
Staff member
Licensed User
Longtime User
A few questions / comments:

1. You should use File - Export as zip when uploading files.
2. Why didn't you use HttpUtils2?
3. Why did you copy Table class code to the main activity???
4. There seems to be a missing module (StrLib).
5. I don't see any reference to SQL.

I'm probably missing something...
 
Last edited:
Upvote 0
G

GaryK4

Member
Licensed User
Longtime User
Erel said:
A few questions / commanes:

1. You should use File - Export as zip when uploading files.
2. Why didn't you use HttpUtils2?
3. Why did you copy Table class code to the main activity???
4. There seems to be a missing module (StrLib).
5. I don't see any reference to SQL.

I'm probably missing something...
Click to expand...

I am an experienced Visual Studio programmer, but a rookie at b4a.

1. OK. Do I need to re-upload for you?
2.Use HttpUtils2 for what? The database is local. No internet access is required.
3. I don't understand this question. Are you referring to Sub Activity_Create(FirstTime As Boolean) ? I don't see any table class in my program.
4. Missing module (StrLib1) is my own lib that I thought I might need for this project. You can remove the reference because I don't use it.
5. The AddItem sub has the main sql command (line 206) sqlcmd = "SELECT word FROM word_list WHERE word = '" & tmpStr & "'"

I should also note, the performance is worse than I originally stated. My time message code was incorrect. I thought that period.seconds would give me the total seconds. However as you know it only give me the seconds portion of the period. It was taking a minute + the seconds.
In and event, the real throughput is about 45 records / second vs 250,000 records / second on a PC with visual studio.
It is hard to believe tat it could be that slow.
 
Upvote 0
G

GaryK4

Member
Licensed User
Longtime User
rboeck said:
A question to memory locations: Most tablets have a virtual sdcard and maybe a real sd card. If the database is stored at the virtual sd card, is a speed difference possible? SQLite has an in-memory option; can there be a speed difference, because we dont use hard discs?
Click to expand...

The Nexus 10 32GB of build in memory with NO sd card expansion.
I tried the in memory method (SQL1.Initialize("", ":memory:", True)) and it only worked for small words. Larger words, the program crashed.
 
Upvote 0
G

GaryK4

Member
Licensed User
Longtime User
LucaMs said:
maybe I'm wrong, but you've attached the wrong source (Stock Activity) :)
Click to expand...

:oops:Wow, how embarrassing. I edited the original post and uploaded the correct program.

Thanks for the catch.
Now I understand why I confused Erel!
 
Upvote 0
Erel

Erel

B4X founder
Staff member
Licensed User
Longtime User
1. You should change the insert code to:
B4X: 
SQL1.BeginTransaction
For i = 0 To WordList.Size -1
   word = WordList.Get(i)
   
   SQL1.ExecNonQuery2("INSERT INTO word_list VALUES (?)", Array As Object(word))
   
Next
SQL1.TransactionSuccessful
SQL1.EndTransaction
It will be about 100 times faster.

2. You have added CustomListView code to the main activity. This is not needed. See CustomListView class example.

The algorithm is the king :)

My algorithm solves 8 characters in 10ms (10 milliseconds):
B4X: 
Private Sub CalcWordHash(w As String) As Int
   Dim h As Int
   For i = 0 To w.Length - 1
     h = h + Asc(w.CharAt(i))
   Next
   Return h
End Sub

Sub FillSQLTable
   Dim i As Int
   Dim word As String
   
   WordList.Clear
   WordList = File.ReadList(File.DirRootExternal, "Jumble/WordList.txt")
   Log("create")
SQL1.BeginTransaction
For i = 0 To WordList.Size -1
   word = WordList.Get(i)
   
   SQL1.ExecNonQuery2("INSERT INTO word_list VALUES (?, ?)", Array As Object(word, CalcWordHash(word)))
   
Next
SQL1.TransactionSuccessful
SQL1.EndTransaction
   lb_results.Text = "Database Created"
   
End Sub

Sub SolveJumble
   Dim start As Long = DateTime.Now
   Dim w As String = tx_word.Text
   Dim crsr As Cursor = SQL1.ExecQuery2("SELECT word FROM word_list WHERE hash = ?", _
     Array As String(CalcWordHash(w)))
   For i = 0 To crsr.RowCount - 1
     crsr.Position = i
     Dim test As String = crsr.GetString("word")
     If test.Length = w.Length Then
       Dim chars As List
       chars.Initialize
       For c = 0 To w.Length - 1
         chars.Add(w.CharAt(c))
       Next
       For c = 0 To test.Length - 1
         Dim charPos As Int = chars.IndexOf(test.CharAt(c))
         If charPos = -1 Then Exit
         chars.RemoveAt(charPos)
       Next
       If c = test.Length Then
         Log("Found: " & test)
       End If
     End If
   Next
   crsr.Close
   Log(DateTime.Now - start)
End Sub
 
Upvote 0
LucaMs

LucaMs

Expert
Licensed User
Longtime User
Erel said:
1. You should change the insert code to:
B4X: 
SQL1.BeginTransaction
For i = 0 To WordList.Size -1
   word = WordList.Get(i)
  
   SQL1.ExecNonQuery2("INSERT INTO word_list VALUES (?)", Array As Object(word))
  
Next
SQL1.TransactionSuccessful
SQL1.EndTransaction
It will be about 100 times faster.

2. You have added CustomListView code to the main activity. This is not needed. See CustomListView class example.

The algorithm is the king :)

My algorithm solves 8 characters in 10ms (10 milliseconds):
B4X: 
Private Sub CalcWordHash(w As String) As Int
   Dim h As Int
   For i = 0 To w.Length - 1
     h = h + Asc(w.CharAt(i))
   Next
   Return h
End Sub

Sub FillSQLTable
   Dim i As Int
   Dim word As String
  
   WordList.Clear
   WordList = File.ReadList(File.DirRootExternal, "Jumble/WordList.txt")
   Log("create")
SQL1.BeginTransaction
For i = 0 To WordList.Size -1
   word = WordList.Get(i)
  
   SQL1.ExecNonQuery2("INSERT INTO word_list VALUES (?, ?)", Array As Object(word, CalcWordHash(word)))
  
Next
SQL1.TransactionSuccessful
SQL1.EndTransaction
   lb_results.Text = "Database Created"
  
End Sub

Sub SolveJumble
   Dim start As Long = DateTime.Now
   Dim w As String = tx_word.Text
   Dim crsr As Cursor = SQL1.ExecQuery2("SELECT word FROM word_list WHERE hash = ?", _
     Array As String(CalcWordHash(w)))
   For i = 0 To crsr.RowCount - 1
     crsr.Position = i
     Dim test As String = crsr.GetString("word")
     If test.Length = w.Length Then
       Dim chars As List
       chars.Initialize
       For c = 0 To w.Length - 1
         chars.Add(w.CharAt(c))
       Next
       For c = 0 To test.Length - 1
         Dim charPos As Int = chars.IndexOf(test.CharAt(c))
         If charPos = -1 Then Exit
         chars.RemoveAt(charPos)
       Next
       If c = test.Length Then
         Log("Found: " & test)
       End If
     End If
   Next
   crsr.Close
   Log(DateTime.Now - start)
End Sub
Click to expand...


I have not tried it, but I trust, so ... good job!
 
Upvote 0
eps

eps

Expert
Licensed User
Longtime User
I was going to suggest looking at the DB structure (I use Firefox SQLite add on to do this) and an Explain Plan, but see that Erel has already provided a potential solution, which should work well enough for you! :)
 
Upvote 0
G

GaryK4

Member
Licensed User
Longtime User
Erel,

Looks very interesting. I don't totally understand it yet.

I did no bother with the transaction originally being it was only a test and the db is only created once.
It originally failed. Had to add the hash field to the db.
This is a great algorithm!

This solves this test program, however it is only a workaround for the slow SqlLite.

I will check out CustomListView as you suggested.

Great job.
Thanks for the help.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

Paolo Pini
  • Question
Android Question Access to field type in SqlLite database
Replies
8
Views
2K
Paolo Pini
Paolo Pini
Paolo Pini
  • Question
Android Question How to manage null values in SqlLite GetString?
Replies
6
Views
1K
Mahares
M
tigrot
  • Question
Android Question Strange crash on a SqlLite query,.
Replies
14
Views
2K
tigrot
tigrot
D
  • Question
Android Question sqlLite journal
Replies
4
Views
2K
DPaul
D
Erel
  • Locked
  • Article
Android Tutorial SQL tutorial
7 8 9
Replies
161
Views
284K
luciano deri
luciano deri
Top