Android Question SqlLite slow?

GaryK4 · Dec 1, 2013

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?

Erel · Dec 1, 2013

Are you testing it in Release mode?

GaryK4 · Dec 1, 2013

Erel said:
Are you testing it in Release mode?

I tested it in both debug and Release. The numbers that I posted were in the release mode.
I am running the program on a Nexus 10 tablet.

eps · Dec 1, 2013

Indexes?

Plus, what does the SQL look like?

GaryK4 · Dec 1, 2013

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.

fatman · Dec 2, 2013

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

Erel · Dec 2, 2013

Can you upload the complete project (File - Export as zip)?

warwound · Dec 2, 2013

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.

rboeck · Dec 2, 2013

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?

warwound · Dec 2, 2013

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?

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.

GaryK4 · Dec 2, 2013

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

Sorry!!

Erel · Dec 2, 2013

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

GaryK4 · Dec 2, 2013

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

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.

GaryK4 · Dec 2, 2013

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?

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.

LucaMs · Dec 3, 2013

maybe I'm wrong, but you've attached the wrong source (Stock Activity)

GaryK4 · Dec 3, 2013

LucaMs said:
maybe I'm wrong, but you've attached the wrong source (Stock Activity)

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

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

Erel · Dec 3, 2013

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

LucaMs · Dec 3, 2013

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

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

eps · Dec 3, 2013

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!

GaryK4 · Dec 3, 2013

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.

Android Question SqlLite slow?

Member

B4X founder

Member

Expert

Member

Active Member

B4X founder

Expert

Well-Known Member

Expert

Member

Attachments

B4X founder

Member

Member

Expert

Member

B4X founder

Expert

Expert

Member