SQLite accesstime to read

[BerndB]

On a 500MHz FS loox 720 Win 2003 SE
The command

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

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

  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]

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:

cmd.CommandText = "Select Alti.* from Alti WHERE ROWID = " & (y-1)

 

[BerndB]

: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

 

[Erel]

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

I don't think so but it is worth trying.