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
endend
excerpts from main form:
object dsClients: TDatasource
DataSet = qClients
OnStateChange = Sync
OnDataChange = dsClientsDataChange
OnUpdateData = Sync
left = 552
top = 64
endobject 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