[SOLVED] TSQLite3Dataset: how to get LastInsertRowId fast? lazarus / 16-Databases ---------------------------------------------------------------------- eny · 2011-02-09 09:28 It seems that LastInsertRowId only gives the expected result after 'ApplyUpdates' on a TSQLite3Dataset. This makes database I/O extremely slow when there are a lot of sequential inserts. Is there a faster way to insert e.g. 1000 rows and for each row, after the insert, get the newly autocreated RowId? ---------------------------------------------------------------------- LuizAmérico · 2011-02-10 12:23 Yes by doing insert at 'hand' and using last_insert_rowid on subsequent SQL. You can use also trigger To further help is necessary to know what you are trying to do. ---------------------------------------------------------------------- eny · 2011-02-10 14:28 Thx for helping with this. Quote from: LuizAmérico on February 10, 2011, 01:23:58 pmYes by doing insert at 'hand' and using last_insert_rowid on subsequent SQL.How can one do 'at hand'? What I've done: 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 var ds : TSqlite3Dataset;    i  : integer;    lst: TStringList;begin  // For display purposes create a stringlist  lst := TStringList.Create;  // Create a dataset to insert new records  ds := TSqlite3Dataset.Create(nil);  ds.FileName := 'data.s3db';;  // Use a table with 2 fields: an autoincrease primary key and a Name string field  ds.TableName := 'Names';  ds.Open;  // Insert x number of rows  for i := 1 to 10 do  begin    ds.Append;    ds.FieldByName('Name').AsString := IntToStr(i) + ' inserted';    ds.Post;    // Retrieve last inserted row id    lst.Append('Newly inserted id = ' + IntToStr(ds.LastInsertRowId));  end;  ds.ApplyUpdates;  // Display all retrieved row id's  ShowMessage(lst.Text);end;This doesn't work; only zeroes are returned as last inserted row id's. However if I change the loop to this... 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     //...  ds.Append;    ds.FieldByName('Name').AsString := format('%d. %s',[i,trim(edNaam.Text)]);    ds.Post;    ds.ApplyUpdates;        // <<---- Write all changes to disk    // Retrieve last inserted row id  lst.Append('Newly inserted id = ' + IntToStr(ds.LastInsertRowId));  //...... it does work. But because of the call to ApplyUpdates, the speed decreases to <10 inserts per second. ---------------------------------------------------------------------- LuizAmérico · 2011-02-10 16:39 what will you do with the LastInsertRowID? Store in a List? Save in another Database? Luiz ---------------------------------------------------------------------- eny · 2011-02-10 17:08 The ID's are an essential part of ORM: translating objects to records. The relationships between objects in memory obviously are handled by object references. And when persisted each object gets a unique ID and the relationships between objects/records in the database are guaranteed via those ID's. So afterwards, when reading data from the database the relationships can be restored in memory. The alternative is to 'manually' generate those ID's, which is not difficult at all. But because SQLite already has the mechanism of autogenerating unique ID's I would like to reuse that mechanism hence the use of last inserted rowid. ---------------------------------------------------------------------- LuizAmérico · 2011-02-10 17:48 You will have to use the low level c api: sqlite3_last_insert_rowid sqlite3_prepare sqlite3_step http://www.sqlite.org/c3ref/funclist.html Beside this you may want to use GUID as PK to avoid such round trip as tiOPF does ---------------------------------------------------------------------- eny · 2011-02-10 18:02 Quote from: LuizAmérico on February 10, 2011, 06:48:18 pmYou will have to use the low level c api: sqlite3_last_insert_rowid sqlite3_prepare sqlite3_step Does this translate to: for my purpose TSQLite3Dataset cannot be used? <> 'Manually' calling all sqlite functions (sqlite3_open(), sqlite3_prepare() etc...) combined with a sqlite3 transaction gives the result I was looking for: approx. 25.000 inserts in a second and the correct ID's returned after each insert. Now I only need to write a Lazarus wrapper class... Quote from: LuizAmérico on February 10, 2011, 06:48:18 pmBeside this you may want to use GUID as PK to avoid such round trip as tiOPF doesI need several tens of thousands of records; so the 64 bit resolution in SQLite's autoincrement PK's should be sufficient for my purposes. <> Final words: a working solution is using TSqlite3Dataset and manually specifying the dml within a transaction. So no need to re-invent the wheel. The code below gives me the >20K inserts per second. 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   //...  ds.ExecSQL('BEGIN TRANSACTION');  ds.ExecSQL('INSERT....');  ProcessID( ds.LastInsertRowId );  ds.ExecSQL('INSERT....');  ProcessID( ds.LastInsertRowId );  //....  ds.ExecSQL('COMMIT');