Re: [sqlite] Long update times
On Thu, Jun 19, 2008 at 09:20:24AM -0700, Jason Tudor scratched on the wall: > The blob sizes are about 24KB. You do understand the command "UPDATE Objects SET Lock = 1" will update all 12,243 rows, right? And that requires the whole table to be sucked in off disk, modified, and written back out? Even without the full sync, that's going to take some time... if the blobs are around 24K you're moving at least ~300MB off disk and writing it back. How fast do you expect that to be? > Also, the time I gave was wrong, it was closer to 55 seconds. I set the > synchronous flag to 0 (NONE) and the time decreased to 30 seconds. Problem > was that the synch flag did not stick. When I reopened the database, the > flag was set to 2 (FULL) Many (most?) of the PRAGMAs don't stick. They're designed that way. -j > On Thu, Jun 19, 2008 at 7:21 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Jun 19, 2008, at 9:17 AM, Jason Tudor wrote: > > > > > Hello Everyone, > > > > > > I was hoping that someone could explain why my updates are taking so > > > long. > > > My schema is as follows: > > > > > > CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type > > > INTEGER, > > > Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL) > > > > > > My table has 12,243 rows. > > > > > > when I execute the following: "UPDATE Objects SET Lock = 1" > > > > > > I wait for about 20 seconds. I would think that the update should > > > be much > > > faster. > > > I tried wrapping the statement with a transaction, but no luck. > > > > > > I routinely do such operations in milliseconds. Dunno what you are > > doing wrong. What hardware are you running on? How big are the BLOBs > > in your table. Can you post the output from running the > > sqlite3_analyzer utility (available from > > http://www.sqlite.org/download.html) > > on your database file so that we have a better idea of what kind of > > data we are dealing with? > > > > D. Richard Hipp > > [EMAIL PROTECTED] > > > > > > > > ___ > > 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 -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long update times
The blob sizes are about 24KB. I downloaded the sqlite3_analyzer, is there any documentation on this? It's just an executable and when I dbl click I get a console window that disappears. Also, the time I gave was wrong, it was closer to 55 seconds. I set the synchronous flag to 0 (NONE) and the time decreased to 30 seconds. Problem was that the synch flag did not stick. When I reopened the database, the flag was set to 2 (FULL) Thanks again for the help On Thu, Jun 19, 2008 at 7:21 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Jun 19, 2008, at 9:17 AM, Jason Tudor wrote: > > > Hello Everyone, > > > > I was hoping that someone could explain why my updates are taking so > > long. > > My schema is as follows: > > > > CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type > > INTEGER, > > Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL) > > > > My table has 12,243 rows. > > > > when I execute the following: "UPDATE Objects SET Lock = 1" > > > > I wait for about 20 seconds. I would think that the update should > > be much > > faster. > > I tried wrapping the statement with a transaction, but no luck. > > > I routinely do such operations in milliseconds. Dunno what you are > doing wrong. What hardware are you running on? How big are the BLOBs > in your table. Can you post the output from running the > sqlite3_analyzer utility (available from > http://www.sqlite.org/download.html) > on your database file so that we have a better idea of what kind of > data we are dealing with? > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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
Re: [sqlite] Long update times
On Jun 19, 2008, at 9:17 AM, Jason Tudor wrote: > Hello Everyone, > > I was hoping that someone could explain why my updates are taking so > long. > My schema is as follows: > > CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type > INTEGER, > Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL) > > My table has 12,243 rows. > > when I execute the following: "UPDATE Objects SET Lock = 1" > > I wait for about 20 seconds. I would think that the update should > be much > faster. > I tried wrapping the statement with a transaction, but no luck. I routinely do such operations in milliseconds. Dunno what you are doing wrong. What hardware are you running on? How big are the BLOBs in your table. Can you post the output from running the sqlite3_analyzer utility (available from http://www.sqlite.org/download.html) on your database file so that we have a better idea of what kind of data we are dealing with? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Long update times
Hello Everyone, I was hoping that someone could explain why my updates are taking so long. My schema is as follows: CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type INTEGER, Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL) My table has 12,243 rows. when I execute the following: "UPDATE Objects SET Lock = 1" I wait for about 20 seconds. I would think that the update should be much faster. I tried wrapping the statement with a transaction, but no luck. Thanks for your help. -Tudor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
