B BerndB Member Licensed User May 11, 2008 #1 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
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 B4X founder Staff member Licensed User Longtime User May 11, 2008 #2 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. Click to expand... 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)
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. Click to expand... 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)
B BerndB Member Licensed User May 11, 2008 #3 :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
: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 B4X founder Staff member Licensed User Longtime User May 12, 2008 #4 Would there be an additional increase in speed if i would insert a column with row numbers with a primary key on it? Click to expand... I don't think so but it is worth trying.
Would there be an additional increase in speed if i would insert a column with row numbers with a primary key on it? Click to expand... I don't think so but it is worth trying.