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 -- _______________________________________________ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus