U++ forum: Welcome to the forum

Re: Doubts on a sqlite query [message #38613 is a reply to message #38612]

Sat, 29 December 2012 10:35

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

Ultimate Contributor

Hi Luigi,

The 25 seconds for few simple queries sounds awfully wrong. Have you tried to figure out what exactly takes so long there? Putting TIMING macros to each of the Execute() calls could tell you something:

 	for(i=0; i<n; i++) {
 	    id = arr_N[i];
	    { TIMING("team-update"); sqlteam.Execute("update TEAMS set N=? where ID=?", i+1, ids[i]); }
            { TIMING("player-select"); sqlplayer.Execute("SELECT ID FROM PLAYERS WHERE TEAM_ID=? ORDER BY BOARD ASC", id); }//retrieve players of team id by boards
	    while (sqlplayer.Fetch()) {
			idp = (int) (sqlplayer[0]);
		        TIMING("player-update"); sqlp.Execute("update PLAYERS set N=? where ID=?", np++, idp); //set pairing number to the player idp
	    }      
	}

One trick that might make it faster is to execute it all in single query, using a long case construct. The query would look something like this:

update PLAYERS 
set N=case ID
  when 1 then 23
  when 2 then 42
  ...
  when 600 then 123

It'll be a long (and ugly Smile

) query, but it could be faster then 600 small ones. Similar thing can be done for teams too.

Also, are you using indexes on your tables? They can make a lot of difference.

Best regards,
Honza

Report message to a moderator