On 03/06/2011, at 11:40 PM, Ian Hardingham wrote:
> Hey guys, thank you very much for the help so far.
>
> The list of calls which I make during the "end match section", which can
> take 2 seconds, is:
>
> SELECT * FROM multiturnTable WHERE id=? LIMIT 1
>
> UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?
>
> UPDATE multiturnTable SET complete=1, score=? WHERE id=?
>
> SELECT * FROM userTable WHERE name='?' twice
>
> UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
> dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
> scoreStreak='?', scoreStreakNumber=? WHERE name='?'; twice
If properly constructed, I would expect this to be almost instant.
I suggest:
1. Use integer columns instead of text for id primary key
2. Use foreign keys (ie "references")
3. Atomize your columns, ie only one piece of data per column (eg don't store
record as number tab number in one column).
4. For any column that is multiple choice (eg status), don't store as text,
store as an integer that references another table.
5. In general, properly normalise your tables, which includes the above. It's
hard to be more precise without knowing your data better.
5. Use triggers to update the related user table when a match changes (eg ends).
6. Do all of an event in one transaction.
7. Do it all (or as much as possible) of an event in SQL, rather than passing
values back from SQL into application then back to SQL.
At the basic level, do the foloowing:
Change multiturnTable to this (I've only really changed the first three
columns):
create table multiturnTable
( id integer primary key not null
, player1 integer not null references userTable(id)
, player2 integer not null references userTable(id)
, date text
, rating real
, info text
, complete integer
, currentTurn integer
, p1Submitted integer
, p2Submitted integer
, score real
, gamemode text
, turnLimit integer
, turnTimeLimit integer
, p1SubmitScore integer
, p2SubmitScore integer
, quickMatchId integer
, nComments integer default 0
, p1TurnSubTime real
, p2TurnSubTime float
, nRatings integer
, p1GivenUp integer
, p2GivenUp integer
, dupId integer default -1
, p1Declined integer default 0
, p2Declined integer default 0
, lastP1CommitTime text default '-1'
, lastP2CommitTime text default '-1'
)
;
Change userTable to this:
create table userTable
( id integer primary key not null
, name text not null unique collate nocase
, email text collate nocase
, key text
, status text
, date text
, playedFor integer
, totalScore real default 0
, totalRecordHigh integer default 0
, totalRecordLow integer default 0
, dailyScore real default 0
, dailyRecordHigh integer default 0
, dailyRecordLow integer default 0
, dailyGameRecordHigh integer default 0
, dailyGameRecordLow integer default 0
, dailyGamesPlayed integer default 0
, scoreStreak text default ''
, scoreStreakNumber integer default 0
, noEmail integer default 0
, playedInfIds text default ''
)
;
-- These calculations are guesses. You will need to correct them:
create trigger multiturnEndMatch
after update of complete
on multiturnTable
when new.complete = 1 and old.complete != 1
begin
update userTable
set totalScore = totalScore + new.p1SubmitScore
, dailyScore = dailyScore + new.p1SubmitScore
, dailyGamesPlayed = dailyGamesPlayed + 1
, scoreStreak = scoreStreak + new.p1SubmitScore
, scoreStreakNumber = scoreStreakNumber + 1
where id = old.player1
;
update userTable
set totalScore = totalScore + new.p2SubmitScore
, dailyScore = dailyScore + new.p2SubmitScore
, dailyGamesPlayed = dailyGamesPlayed + 1
, scoreStreak = scoreStreak + new.p2SubmitScore
, scoreStreakNumber = scoreStreakNumber + 1
where id = old.player2
;
-- if player1 won:
update userTable
set totalRecordHigh = new.p1SubmitScore
, totalRecordLow = totalRecordLow + new.p2SubmitScore
where new.p1SubmitScore > new.p2SubmitScore and totalRecordHigh <
new.p1SubmitScore
;
update userTable
set dailyRecordHigh = new.p1SubmitScore
, dailyRecordLow = new.p2SubmitScore
where new.p1SubmitScore > new.p2SubmitScore and dailyRecordHigh <
new.p1SubmitScore
;
update userTable
dailyGameRecordHigh = new.p1SubmitScore
, dailyGameRecordLow = new.p2SubmitScore
where new.p1SubmitScore > new.p2SubmitScore and dailyGameRecordHigh <
new.p1SubmitScore
;
-- if player2 won:
update userTable
set totalRecordHigh = new.p2SubmitScore
, totalRecordLow = totalRecordLow + new.p1SubmitScore
where new.p2SubmitScore > new.p1SubmitScore and totalRecordHigh <
new.p2SubmitScore
;
update userTable
set dailyRecordHigh = new.p2SubmitScore
, dailyRecordLow = new.p1SubmitScore
where new.p2SubmitScore > new.p1SubmitScore and dailyRecordHigh <
new.p2SubmitScore
;
update userTable
dailyGameRecordHigh = new.p2SubmitScore
, dailyGameRecordLow = new.p1SubmitScore
where new.p2SubmitScore > new.p1SubmitScore and dailyGameRecordHigh <
new.p2SubmitScore
;
end
;
Then your end match SQL becomes simply one SQL statement:
update multiturnTable set complete = 1 where id = ?
That will fire the trigger to update the user data, which all occurs in one
transaction.
Another option worth considering is to not store and update the individual user
game history data in the userTable at all. You could simply cross reference and
perform calculations on the multiturn table when needed. This saves you from
having to keep tables in sync and updated, and eliminates redundant data. The
speed is potentially slower, but you probably won't notice with indexes on
player1 and player2.
So, for instance, when wanting the stats for a particular user, you could do
this:
select
userTable.id
, userTable.name
, sum(player1.p1SubmitScore) + sum(player2.p2SubmitScore) as totalScore
, max(max(player1.p1SubmitScore), max(player2.p2SubmitScore)) as
totalRecordHigh
from userTable
left join multiturnTable as player1 on userTable.id =
multiturnTable.player1
left join multiturnTable as player2 on userTable.id =
multiturnTable.player2
where userTable.id = ? and multiturnTable.complete = 1
;
I hope this helps.
Tom
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
