U++ forum: Welcome to the forum

Re: Doubts on a sqlite query [message #38620 is a reply to message #38618]

Sat, 29 December 2012 22:40

dolik.rce
Messages: 1789
Registered: August 2008
Location: Czech Republic

Ultimate Contributor

forlano wrote on Sat, 29 December 2012 21:27

TIMING teams : 56.00 ms - 56.00 ms (56.00 ms / 1 ), min: 56.00 ms, max: 56.00 ms, nesting: 1 - 1
TIMING players-update : 21.90 s - 139.46 ms (21.90 s / 157 ), min: 46.00 ms, max: 646.00 ms, nesting: 1 - 157
TIMING player-select : 177.97 ms - 1.13 ms (178.00 ms / 157 ), min: 0.00 ns, max: 2.00 ms, nesting: 1 - 157

Much better but still too high. Anyway I saw the things can improve rearranging the query. I'll think about it.

Thats great, so the last problematic thing is the players update... Let's take it a step further and try to set all the players at once. This should work (unless I made some stupid mistake again Smile

	String teams = "update TEAMS set N = case ";
	String players = "update PLAYERS set N = case ";
	for(i=0; i<n; i++) {
		teams += Format(" when ID=%i then %i", ids[i], i+1 );
		{TIMING("player-select"); sqlplayer.Execute("SELECT ID FROM PLAYERS WHERE TEAM_ID=? ORDER BY BOARD ASC", arr_N[i]); }
		while (sqlplayer.Fetch()) {
			idp = (int) (sqlplayer[0]);
			players += Format(" when ID=%i then %i", idp, np++);
		}
	}
	players += " else N end ";
	{TIMING("players-update"); sqlp.Execute(players); }
	teams += " else N end ";
	{TIMING("teams");sqlteam.Execute(teams);}

That should bring it to usable speeds, my guess is under half a second Wink

Honza

Report message to a moderator