Tom, thank you so much for the extensive advice. Ian
On 04/06/2011 15:27, BareFeetWare wrote: > 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
