On 21/06/2013 8:41 PM, Keith Medcalf wrote:
No, in my case user does not touch the DB - he can only add or delete
items,
but all DB modifications are done by my app and I have a full control over
all the values. All I need is to find the most efficient way to do that.
Therefore, I do not believe that I need any triggers. I guess they will
simply slow down insertion and update (however I did not measure this
yet).
In those rare cases when there is a need to reset attributes on an already
existing item, I would rather delete the entire row and insert a new one
and
treat it as a completely new item.
Perhaps you should reconsider using triggers. For example, you can create a
trigger that adds the rowid of rows inserted (ie, that you need to update) into
another table. Periodically from your application process the rows indicated
in this table to do your updates, and delete the rowids for the rows you have
updated. This will add almost no discernible overhead to your table updates,
plus it will give you a table with the rowids of the rows you need to visit and
update. If you wish to do them in small batches then you can do so.
I considered that as well, but it's not clear how much benefit you get
over the autoincrement scheme: the PK-index is there either way, so
that's not a slowdown. The split table approach also makes
query-writing and indexing more complex, so at a minimum you'd probably
want to make a view that runs a UNION ALL on the two tables.
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
