SQLite accesstime to read

BerndB

Member
Licensed User
On a 500MHz FS loox 720 Win 2003 SE
The command
B4X:
Reader.Value = Cmd.ExecuteReader
on a sql table with 1000 lines* 1000 cols

takes 3 to 4 sek :signOops:

is that ok? Or is it possible to optimize?
on the desktop the same takes maximum 0,03 s (30 ms).
So there is a faktor >100 between it.

Is the way through SQL really the quickest?

Thanks in advance
Bernd


context
B4X:
reader.New1   ' SQL-table-read
cmd.CommandText = "Select Alti.* from Alti Limit 1 offset " &y-1
  tStrt =dzHw1.GetTickCount
Reader.Value = Cmd.ExecuteReader 
  tEnd=dzHw1.GetTickCount
  tb1.Text =tb1.Text&CrLf&(tEnd-tStrt)/1000
  tb1.Refresh
CSIAlti=Reader.GetValue(x-1)   
reader.Close

The sql tabel was created in this way

B4X:
  table1.LoadCSV(TmpAltiFile, " ",false, true)
  table1.RemoveCol("Column1001")
   
  DB3File = SubString(open1.File,0,StrLength(open1.File)-3)&"sl3"
  Con.Open("Data Source = " &DB3File)
   
  Con.CreateSQLTable ("Table1","Alti")
   
  con.Close
 

Erel

Administrator
Staff member
Licensed User
You should not use OFFSET (SQLite documentation):
The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set.
Which means that the query first returns all rows and then skips to the required one.

Instead you should use a primary key to identify the required row or you can use the built-in ROWID feature:
B4X:
cmd.CommandText = "Select Alti.* from Alti WHERE ROWID = " & (y-1)
 

BerndB

Member
Licensed User
:sign0060: great,

it's a big difference!

I worked with the built-in ROWID, because there is no need for changing the tabel structure then.

Would there be an additional increase in speed if i would insert a column with row numbers with a primary key on it?

With a big thanks to Erel.

Bernd
 
Top