I'll try taking out the ANALYZE and VACUUM and see if it helps. I'm using transactions and I am creating the index after the update (and dropping it before). So, the only 2 possible delays are in those two commands, from what I can see...
The problem is that it's actually inside a Windows service, so it has limited time in which it has to start, otherwise Windows complains that it took too long and aborts starting the service altogether. I wouldn't care about this time otherwise. Dennis -----Original Message----- From: P Kishor [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 11:17 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is it possible to do this using only SQL? On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > Hmm, strange - my testing produces very slow results (it took over a > minute to update 120K rows). well, for one, you are adding different stuff from me. > > Maybe it's because I'm also creating an index on the new row and doing > an ANALYZE - do these 2 operations take considerable amount of time? yes, you want to suspend indexing while doing all this mucking around. Indexing is used in searching, so do all your data entry and lowercasing and then create the index later. > > There's also a VACUUM later on, which might be slow? (The database is > around 900 MBs) sure, keep adding tasks and the task will take more time. Vacuum is only useful to reclaim space from deleted records. If you are not deleting anything, why vacuum? Finally, the most important factor -- use transactions. You are using transactions, no? What! No! Why "no"? Go use transactions. > > Dennis > > > -----Original Message----- > From: P Kishor [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 05, 2008 11:04 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Is it possible to do this using only SQL? > > On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > > Oh, that looks simpler than I thought. Thank you for the reply! > > not only is it simple... > > .... > > > > On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote: > > > > > Is that possible? If not, I'll have to do it in the code, but that > > > will > > > probably be slower and I'm expecting to have tens of thousands of > > > rows. > > > it is also very fast. On my laptop it takes 7 secs to add a million > upcased strings, and 8 secs to lowercase them. > > > > > > Sure: > > > > sqlite> create table x(a); > > sqlite> insert into x(a) values('ABC'); > > sqlite> insert into x(a) values('DEF'); > > sqlite> alter table x add column b; > > sqlite> update x set b=lower(a); > > sqlite> select * from x; > > ABC|abc > > DEF|def > > sqlite> > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
