I typed the command into my console - not doing it once per row. Doing it again, it was more like one second. No other SQLite commands should have been happening near the time of execution.
I'm not entirely sure what "schema" means in this context. The definiton of userTable is: CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT '0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0, noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '') I haven't changed any of the default PRAGMAs, although I suspect it might help me. This is a game server which generally has 10 - 1000 users, each sending requests every 5-10 seconds. Every 30 minutes there's a big operation which re-calculates the leaderboards, and that can take up to 20 seconds. I'm considering doing that on a separate thread, maybe even with a duplicate database. Your help us really appreciated. Ian On 05/10/2010 12:22, Drake Wilson wrote: > Quoth Ian Hardingham<i...@omroth.com>, on 2010-10-05 12:16:11 +0100: >> Your query, >> >> UPDATE userTable SET playedInfIds = '' >> >> Still took two seconds actually... but significantly better than what I >> was doing. > You're doing this only once rather than once per row, right? On a > table with around 3k rows, it seems a little odd that it would take > that long, even if updating every row tends to be expensive in > general. What does your schema look like, if I might ask? Is there > significant concurrent access with that giant update? > >> Ian > ---> Drake Wilson > _______________________________________________ > 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
