Parametrized queries in sqlite and B4A?

[nachoap10]

Hi!

I have created a little function for my Lipapa Marte for Android app in order to delete all files witch have no entries in the db (orphan books and covers). I navegate for all folders and subfolders, recursively, and all is fine and works well, but I have about 11.000 books, and, it don't mind why, about 8.000 files that the application has to delete.

For every file I first check if it's a folder (if it's, I make a recursive call to the function), and if it's a conventional file, I have to search it in the database, in order to see if it's name appears in a record.

Here I've got a performance problem, because I have to make about 19.000 select calls, and it takes about one hour to execute.

This is the code:

Sub QuitarHuerfanos(Ruta As String)
Dim i As Int
Dim Fich As List
Dim CurHuer As Cursor
Dim Consulta As String
Dim Cadena As String
   
   lbOptHuerfanosC.Text = Ruta
   DoEvents

   Fich = File.ListFiles(Ruta)
   
   For i= 0 To Fich.Size - 1
         
      If File.IsDirectory(Ruta, Fich.Get(i)) Then
         QuitarHuerfanos(Ruta&Fich.Get(i)&"/")
      Else   
         Cadena = Fich.Get(i)
         Consulta = "SELECT NUMERO From LIBROS Where RUTAPAPYRE like '%" & Cadena.Replace("'", "?") & "'"
         CurHuer = BDLIPAPA.ExecQuery(Consulta)
         
         If CurHuer.RowCount = 0 Then
            If File.Delete(Ruta, Fich.Get(i)) Then
               ContaHuerfanos = ContaHuerfanos + 1
            End If
         End If   
         
         CurHuer.Close
      End If
   Next
End Sub

I know it's possible to make parametrized sqlite sentences but, are they accepted in B4A? What's the syntax I've to write in order they work?

Thank you in advance.

 

[Djembefola]

Look here:
ExecQuery2

Dim Cursor As Cursor
Cursor = sql1.ExecQuery2("SELECT col1 FROM table1 WHERE col3 = ?", Array As String(22))

 

[Erel]

Are you sure that the SQLite part is slowing you down? It can be related to the files operation. You should run some tests to check it.

Do you have an index on RUTAPAPYRE columns?
The LIKE function can also be heavy, especially as you have a wildcard at the beginning.

 

[nachoap10]

Thank you, but I think this isn't a parametrizad query. In parametrized queries the app only "compile" the query the first time, so it's a lot faster than ordinal queries if you have to execute it alot of times and the query is always the same, changing only the value of the parameter.

The example you've written is a shortcut to get part of the query from an array, but I think it will be as slow as actual code, because system will compile it every time you execute it.

Anyway, I'll try it.

Thank you.

 

[Erel]

See my above post, you were probably writing while I posted it. I don't think that the parsing step is significant here.

 

[nachoap10]

Are you sure that the SQLite part is slowing you down? It can be related to the files operation. You should run some tests to check it.

Do you have an index on RUTAPAPYRE columns?
The LIKE function can also be heavy, especially as you have a wildcard at the beginning.

Well, in fact the execution isn't slow. It applies more than 18.000 times in one hour, so it's fast, but I think it will be faster if the sqlite core hasn't got to compile the query every time it executes it. And parametrized queries do just this work.

I'll try to put an index over RUTAPAPYRE (I don't create the table Libros myself. A delphi library creates it automatically, exporting from an Access database, so I haven't got control over it, but I can reindex it from B4A).

Thank you!

 

[Erel]

Again, I do not think that the query compilation is slowing your program. Try to run some tests to find the "heavy" operation.

 

[nachoap10]

Again, I do not think that the query compilation is slowing your program. Try to run some tests to find the "heavy" operation.

I'll do