Optimizing SQLite lazarus / 16-Databases ---------------------------------------------------------------------- odvesims · 2016-11-23 01:06 Hey There!! I'm using SQLite to store data in my application. This is an example on what I'm doing for EVERY Select/Insert/Delete procedures... Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SomeProcedure();var  SQLite3Connection2: TSQLite3Connection;  SQLQuery2: TSQLQuery;  SQLTransaction2: TSQLTransaction;  DataSource2: TDataSource;begin  SQLite3Connection2 := TSQLite3Connection.Create(Application);  SQLTransaction2 := TSQLTransaction.Create(Application);  SQLQuery2 := TSQLQuery.Create(Application);  Datasource2 := TDataSource.Create(Application);  SQLite3Connection2.DatabaseName := 'myDB.db';  SQLite3Connection2.Connected := True;  SQLTransaction2.Database := SQLite3Connection2;  SQLQuery2.Database := SQLite3Connection2;  SQLQuery2.Close;  SQLQuery2.SQL.Text :='Some query';  SQLQuery2.ExecSQL;  SQLTransaction2.commit;      end;       Is this the right approach? I have noticed the queries take a while to complete (insert ones) and I wish I could speed things up. ---------------------------------------------------------------------- Akira1364 · 2016-11-23 02:18 The reason it's slow is because you're creating a brand new Connection, Query, Transaction, and DataSource on every procedure call. This is completely unnecessary. If your app is a GUI application then what you should do is drop an actual TSQLite3Connection, TSQLQuery, TSQLTransaction, and TDataSource component on your form and use the IDE to set up their properties. Then the only code that needs to go into the procedure call is the code that specifically relates to executing your SQL statement. If your App is NOT a GUI application then the same idea applies, but you will need to create a single TSQLite3Connection, TSQLQuery, TSQLTransaction, and TDataSource instance "in-code" when it first starts up which can then be re-used the same way. ---------------------------------------------------------------------- vrull · 2016-11-27 19:53 TDataModule is the place designed specifically for this purpose. You can keep your data components there and call them from different forms. You can also open the connection at the program start. If something went wrong there you would know this in advance, so you won't lose data because you discovered the problem too late. ---------------------------------------------------------------------- Bofinken · 2016-11-29 12:36 SQLite is very fast, I use ZEOSLIB and 1000 inserts take about one second. You have to turn off AutoCommit and make your own commits after about 1000 inserts. ---------------------------------------------------------------------- odvesims · 2016-12-06 18:48 Quote from: Akira1364 on November 23, 2016, 03:18:40 amThe reason it's slow is because you're creating a brand new Connection, Query, Transaction, and DataSource on every procedure call. This is completely unnecessary. If your app is a GUI application then what you should do is drop an actual TSQLite3Connection, TSQLQuery, TSQLTransaction, and TDataSource component on your form and use the IDE to set up their properties. Then the only code that needs to go into the procedure call is the code that specifically relates to executing your SQL statement. If your App is NOT a GUI application then the same idea applies, but you will need to create a single TSQLite3Connection, TSQLQuery, TSQLTransaction, and TDataSource instance "in-code" when it first starts up which can then be re-used the same way. This is precisely what I assumed was causing the delay, however I really don't know how to properly do it. I have a multi-form app and I'd like to know how could I open the connection on application start and close it on application end. Can i declare the Connection/Query/Transaction/DataSource on the MainForm and use it on all others form? Any example on how to approach this? ---------------------------------------------------------------------- molly · 2016-12-06 19:11 Quote from: odvesims on December 06, 2016, 07:48:29 pmThis is precisely what I assumed was causing the delay, however I really don't know how to properly do it. I have a multi-form app and I'd like to know how could I open the connection on application start and close it on application end. Can i declare the Connection/Query/Transaction/DataSource on the MainForm and use it on all others form? Any example on how to approach this? If using from different forms then imho the easiest would be to use a datamodule. You can use the create and free events to setup and close close down your connection from there (just make sure your datamodule is created _before_ any actual further actions on the db's take place). To answer your question, yes. You can declare that inside a datamode and use it in every form that has that datamodule unit included. In case you wish to connect other db aware control directly/visually you need to include that datamodule unit from the interface section, otherwise (in case of manual connections) you can use the implementation section.