Query a ExecuteMemoryTable

tcgoh · Apr 26, 2012

Hi,

I have a very large database which I would like to cut down the query time by:

1st Creating a table by ExecuteMemoryTable

Dim table1 As List
table1 = DBUtils.ExecuteMemoryTable(SQL1,"SELECT * FROM tablename where name ='john'",Null,0)

then
Dim M as Map
m = DBUtils.ExecuteMap(SQL1,"SELECT col FROM table1",Null)

I tried the above but it didn't work. Can it be done? Because I've many "m" to query.

Thanks for your help.

klaus · Apr 26, 2012

Try to use:
m = DBUtils.ExecuteMap(SQL1,"SELECT col FROM tablename where name ='john'",Null)

table1 = DBUtils.ExecuteMemoryTable(SQL1,"SELECT * FROM tablename where name ='john'",Null,0)
loads the whole database to table1, I think it's more effitient do filter directly the queries in the database.

Best regards.

tcgoh · Apr 26, 2012

klaus said:
Try to use:
m = DBUtils.ExecuteMap(SQL1,"SELECT col FROM tablename where name ='john'",Null)

table1 = DBUtils.ExecuteMemoryTable(SQL1,"SELECT * FROM tablename where name ='john'",Null,0)
loads the whole database to table1, I think it's more effitient do filter directly the queries in the database.

Best regards.

Thanks Klaus,

In this case, I have NO use of Table1. All my queries will come from m. exsample:

m1 = DBUtils.ExecuteMap(SQL1,"SELECT col1 FROM tablename where name ='john'",Null)
m2 = DBUtils.ExecuteMap(SQL1,"SELECT col2 FROM tablename where name ='john'",Null)
m3 = ......etc.
....
m60 = ...

will this be very slow? Thanks

keirS · Apr 26, 2012

How large is large? How will using ExecuteMemoryTable make your queries faster? ExecuteMemoryTable is quite inefficient. You pass it a limit and then it goes and gets the whole result set and only adds a certain number of records to the list of arrays. It would be far more efficient to add a LIMIT clause to the SELECT statement.

tcgoh · Apr 26, 2012

The database itself is about 3.4mb. Right now the query will take up to 20 sec to display the results.

With your suggestion of setting more limit, it will still search the whole database, don't they?

Thanks

keirS · Apr 26, 2012

Just did a bit of testing it appear LIMIT just limits the return of records to the cursor. SQLite still generates the whole result set. So in terms of query time it won't help.

There are several ways to speed up queries though. A favorite one is to use a hash function to generate an integer that represents the string data. CRC32 or CRC64 (I think both are implemented in android libraries somewhere) is a good one to use as the chance of a duplicate key is very small.
The theory behind using hashes is that sting comparison is far more costly than integer comparison so the queries that base their selection criteria on integers are far quicker. I have played with a quick and dirty hash function that is far more prone to duplicates. Even so I would be surprised if you found any duplicates using it.

B4X:

Sub Process_Globals
   'These global variables will be declared once when the application starts.
   'These variables can be accessed from all modules.

End Sub

Sub Globals
   'These global variables will be redeclared each time the activity is created.
   'These variables can only be accessed from this module.
   Dim StrToHash As EditText
   Dim HashOfStr As Label
   Dim HashMe As Button
   



End Sub


Sub Activity_Create(FirstTime As Boolean)
   StrToHash.Initialize("")
   HashOfStr.Initialize("")
   HashMe.Initialize("HashMe")
   HashMe.Text =  "Hash Me!"
   HashOfStr.TextSize = 14
   Activity.AddView(StrToHash,5,5,250,50)
   Activity.AddView(HashMe,5,60,100,40)
   Activity.AddView(HashOfStr,5,120,250,40)
End Sub


Sub HashMe_Click
     Dim HashValue As Long
    
    HashValue = Generate_Hash(StrToHash)
    HashOfStr.Text = HashValue
    

 

End Sub
Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub Generate_Hash(Hashit As String)
  Dim Prime As Long : Prime = 1125899906842597
  Dim cntr As Int 
  Dim ReturnHash As Long
  Dim c As Char
  
  
  For cntr = 0 To Hashit.Length - 1
     ReturnHash = 31*Prime + Asc(Hashit.CharAt(cntr))
  
   Next
   
    
  Return ReturnHash
  
  End Sub

tcgoh · Apr 29, 2012

Can I use a RondomAccessFile method to save a listView as a data file and open this data file later for quick query?

If so, can I have a exsample on coding the ListView into a data RondomAccessFile.

Thanks and regards:sign0104:

Erel · Apr 29, 2012

You will need to first add the items to a List and then save the list.

rboeck · Apr 29, 2012

Hi,

in my test of sqlite i was very impressed of the speed. My sample database is 37 mb in size, it uses two tables, one adress table with 135.000 adresses, and a second one with related financial data.
When i make a search for 'johannes' as part of the name i get the result and the listview filled in 366 ms. The result set ist 461 records. I think, maybe you did not use indexes for the mostly used search fields?

Greetings

Reinhard
Austria

tcgoh · Apr 30, 2012

Thanks Erel and Rboeck for your reply.

Yes maybe I'm doing the wrong things. Here's my problem, my database has about 25k of lines and each line has about 10 fields.
I am trying to extract 1 or 2 data from each line for about 60 times and place them in a panel. Maybe using DBUtils.ExecuteMap is not very efficient. Right now with only 5x DBUtils.ExecuteMap queries, i have to wait about 15sec.

Thanks for your help.

Query a ExecuteMemoryTable

tcgoh

Active Member

klaus

Expert

tcgoh

Active Member

keirS

Well-Known Member

tcgoh

Active Member

keirS

Well-Known Member

tcgoh

Active Member

Erel

B4X founder

rboeck

Well-Known Member

tcgoh

Active Member

Similar Threads

Query a ExecuteMemoryTable

Active Member

Expert

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

B4X founder

Well-Known Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency