On 13 Mar 2013, at 3:29am, David King <dk...@ketralnis.com> wrote: > At first I was just doing something like this pseducode: > > update_counter(k1, k2, count=count+1, expires=now+count*1day) > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now+1day)
Might I suggest that instead of trying to store an ever-changing value, you simply figure it out when it's needed ? I don't quite understand the logic you're applying to calculate your 'expires' value but I think it could be calculated with a SELECT whenever you needed it rather than being stored. > Now the code that inserts all of the rows into the memory table executes > nearly instantly, but the big INSERT takes 15+ minutes. Meanwhile the journal > (in either rollback or wal mode) balloons to over 300mb in size. You don't list any indexes which would help with your WHERE clauses, so I suspect SQLite is having to look through all the records in 'counters' in order to find the rows it needs for each COALESCE. The large size of the journal is because you are replacing every row in the databases. > So I feel like something about what I'm doing is fundamentally flawed given > something about sqlite's performance model. All I want is a count of the > number of times that I've seen each pair (k1, k2), is there a better way to > do this without storing them all individually and grouping them later? If you have a table with two columns k1, k2 in, and you want to count the times each pair occurs, you can do it in software far faster by having this index and using this SELECT CREATE INDEX myTable_keypair ON myTable (k1,k2) SELECT k1,k2 from myTable ORDER BY k1,k2 you might even use one of the following if you know it will always return a unique value for unique keys SELECT k1||k2 from myTable ORDER BY k1,k2 SELECT k1||':'||k2 from myTable ORDER BY k1,k2 Just count the unique values in your programming language as they go past. Yes, you can use horrendous complication to make SQLite present a neatly formatted return with the counts included, but defining that in SQL makes SQLite do more work than your programming language would need to do. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
