[Lazarus] SQLite performance problem. Simple query takes too long.

[Howard Lee Harkness]

I am doing a conversion of a small database application from
PostgreSQL to SQLite.

I have gotten the program to work, but it it about 2 orders of
magnitude slower than the PostgreSQL version. In particular, a query
like

    select distinct clients.* from clients where  lastname like
'Hark%' order by lastname, firstname, middlename;

...take nearly 3 seconds, whereas in the PostgreSQL version, it was
done instantly. Before I indexed the clients table on lastname, it was
something like 6 seconds. This is for a table with about 8000 records,
and the above query returns 3 rows. I tried reducing the "order by"
clause to just lastname, but that made no difference. I even removed
the "order by" clause completely, and that made no difference. I tried
without "distinct" and that made no difference. What did make a
difference was running the above query using sqlite3.exe from the
command line -- which was instantaneous. This implies that the LCL
components are taking 3 seconds to do some sort of setup for the
query.

This makes no sense to me. The LCL looks fairly straightforward. Here
is the call:

 qClients.Close;
 qClients.SQL.Text:=sql; // where sql contains the above select statement
 qClients.Open;

The Open step is the one taking the time. The database setup is below,
since I suspect that may be where the problem is. I hope this is
enough info.

Any clue about what is taking so long?

Environment:
Windows 7
SQLite3.dll
Lazarus 9.30 using TSQLite3Connection, TSQLTransaction, TSQLQuery,
TDataSource, TDBGrid

object FormDatabase: TFormDatabase
  Left = 473
  Height = 240
  Top = 169
  Width = 184
  Caption = 'FormDatabase'
  OnDestroy = FormDestroy
  LCLVersion = '0.9.30'
  object SQLite3Connection: TSQLite3Connection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'DB.s3db'
    KeepConnection = True
    Password = 'test'
    Transaction = SQLTransaction
    UserName = 'user'
    Options = []
    left = 53
    top = 123
  end
  object SQLTransaction: TSQLTransaction
    Active = True
    Action = caNone
    Database = SQLite3Connection
    left = 53
    top = 67
  end
end


excerpts from main form:

  object dsClients: TDatasource
    DataSet = qClients
    OnStateChange = Sync
    OnDataChange = dsClientsDataChange
    OnUpdateData = Sync
    left = 552
    top = 64
  end

  object qClients: TSQLQuery
    IndexName = 'DEFAULT_ORDER'  // what is this, and why can't I
change it?  I tried IndexFieldNames = 'lastname' but that was EVEN
SLOWER
    AutoCalcFields = False
    Database = FormDatabase.SQLite3Connection
    Transaction = FormDatabase.SQLTransaction
    ReadOnly = False
    SQL.Strings = (
      'select * from clients'
      'order by lastname,firstname,middlename'
    )
    Params = <>
    left = 504
    top = 64
  end

-- 
Howard Lee Harkness