[SOLVED] TSQLite3Dataset: how to get LastInsertRowId fast?

[eny]

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]

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]

Thx for helping with this.

Yes 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][+][-]

1. var ds : TSqlite3Dataset;
2.     i  : integer;
3.     lst: TStringList;
4. begin
5.   // For display purposes create a stringlist
6.   lst := TStringList.Create;
7.   // Create a dataset to insert new records
8.   ds := TSqlite3Dataset.Create(nil);
9.   ds.FileName := 'data.s3db';;
10.   // Use a table with 2 fields: an autoincrease primary key and a Name string field
11.   ds.TableName := 'Names';
12.   ds.Open;
13.   // Insert x number of rows
14.   for i := 1 to 10 do
15.   begin
16.     ds.Append;
17.     ds.FieldByName('Name').AsString := IntToStr(i) + ' inserted';
18.     ds.Post;
19.     // Retrieve last inserted row id
20.     lst.Append('Newly inserted id = ' + IntToStr(ds.LastInsertRowId));
21.   end;
22.   ds.ApplyUpdates;
23.   // Display all retrieved row id's
24.   ShowMessage(lst.Text);
25. 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][+][-]

1.     //...
2.     ds.Append;
3.     ds.FieldByName('Name').AsString := format('%d. %s',[i,trim(edNaam.Text)]);
4.     ds.Post;
5.     ds.ApplyUpdates;        // <<---- Write all changes to disk
6.     // Retrieve last inserted row id
7.     lst.Append('Newly inserted id = ' + IntToStr(ds.LastInsertRowId));
8.     //...

... it does work. But because of the call to ApplyUpdates, the speed decreases to <10 inserts per second.

[LuizAmérico]

what will you do with the LastInsertRowID?

Store in a List?

Save in another Database?

Luiz

[eny]

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]

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]

You 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?

<<update>>
'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...

Beside this you may want to use GUID as PK to avoid such round trip as tiOPF does

I need several tens of thousands of records; so the 64 bit resolution in SQLite's autoincrement PK's should be sufficient for my purposes.

<<update2>>
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][+][-]

1.   //...
2.   ds.ExecSQL('BEGIN TRANSACTION');
3.   ds.ExecSQL('INSERT....');  ProcessID( ds.LastInsertRowId );
4.   ds.ExecSQL('INSERT....');  ProcessID( ds.LastInsertRowId );
5.   //....
6.   ds.ExecSQL('COMMIT');