Efficient way to compute accumulating value in sqlite3

Question

I have an sqlite3 table that tells when I gain/lose points in a game. Sample/query result:

SELECT time,p2 FROM events WHERE p1='barrycarter' AND action='points' 
ORDER BY time; 

1280622305|-22 
1280625580|-9 
1280627919|20 
1280688964|21 
1280694395|-11 
1280698006|28 
1280705461|-14 
1280706788|-13 
[etc]

I now want my running point total. Given that I start w/ 1000 points, here's one way to do it.

SELECT DISTINCT(time), (SELECT 
1000+SUM(p2) FROM events e WHERE p1='barrycarter' AND action='points' 
AND e.time <= e2.time) AS points FROM events e2 WHERE p1='barrycarter' 
AND action='points' ORDER BY time

but this is highly inefficient. What's a better way to write this?

MySQL has @variables so you can do things like:

SELECT time, @tot := @tot+points ...

but I'm using sqlite3 and the above isn't ANSI standard SQL anyway.

More info on the db if anyone needs it: http://ccgames.db.94y.info/

EDIT: Thanks for the answers! My dilemma: I let anyone run any single SELECT query on "http://ccgames.db.94y.info/". I want to give them useful access to my data, but not to the point of allowing scripting or allowing multiple queries with state. So I need a single SQL query that can do accumulation. See also:
Existing solution to share database data usefully but safely?

@Barry: Welcome to the site. This question unfortunately is off-topic, and probably belongs on StackOverflow. Questions for this site should fit the criteria in the FAQ: http://codereview.stackexchange.com/faq — Mark Loeser, Jan 30 '11 at 15:45
This may be a case where you are better off keeping a running total as you are processing the resulting query in your code rather than trying to get the database to do it. — BMitch, Feb 21 '11 at 00:58

score 0 · Answer 1 · answered Feb 21 '11 at 02:29

SQLite is meant to be a small embedded database. Given that definition, it is not unreasonable to find many limitations with it. The task at hand is not solvable using SQLite alone, or it will be terribly slow as you have found. The query you have written is a triangular cross join that will not scale, or rather, will scale badly.

The most efficient way to tackle the problem is through the program that is making use of SQLite, e.g. if you were using Web SQL in HTML5, you can easily accumulate in JavaScript.

score 0 · Answer 2 · answered Feb 21 '11 at 03:22

There is a discussion about this problem in the sqlite mailing list.

Your 2 options are:

Iterate through all the rows with a cursor and calculate the running sum on the client.
Store sums instead of, or as well as storing points. (if you only store sums you can get the points by doing sum(n) - sum(n-1) which is fast).

Efficient way to compute accumulating value in sqlite3

2 Answers2