SQLite accesstime to read

Discussion in 'Questions (Windows Mobile)' started by BerndB, May 11, 2008.

  1. BerndB

    BerndB Member Licensed User

    On a 500MHz FS loox 720 Win 2003 SE
    The command
    Code:
    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
    Code:
    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

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

    Erel Administrator Staff Member Licensed User

    You should not use OFFSET (SQLite documentation):
    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:
    Code:
    cmd.CommandText = "Select Alti.* from Alti WHERE ROWID = " & (y-1)
     
  3. BerndB

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

    Erel Administrator Staff Member Licensed User

    I don't think so but it is worth trying.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice