On 19 Jun 2013, at 6:41am, jhnlmn <jhn...@yahoo.com> wrote: > Simon Slavin <slavins@...> writes: > >> Do you have an index on T(C1) ? >> That should dramatically reduce the search time. > > I tried adding index. > It caused slow down of the original insert of records to the table by about > 25%, which is unacceptable to me. > I also slows down the update because it has to update index as well. > Note that C1 is not the only column, on which I would like to make updates. > So, I will have to add several indexes, which will be even worse.
Nevertheless, this is the way the problem should be solved according to the design of SQLite. What you are doing is searching for NULL entries in a table. The way you speed up a search is to create an index ideally suited to the search. And as you can see, it works: time for your update command is reduced from 'seconds or even minutes' to 2 seconds. Is the increase in input/time really unacceptable to you ? Updates that don’t change the value of the fields in the index should not take any longer. The index is updated only if the values in it change. The only thing that should take longer is inserting the row in the first place. Is a slowdown of even 100% on inputting new data really unacceptable ? It should prove, in the long run, less inconvenient than the long locked period you are currently trying to solve. You can, of course, do your inputting without the extra index existing, then create the index later, at a time suitable for you. But the job of creating the index will block other processes from accessing the table just like the UPDATE command you currently do does. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
