U++ forum: Welcome to the forum

Re: Doubts on a sqlite query [message #38622 is a reply to message #38621]

Sun, 30 December 2012 09:08

forlano
Messages: 1185
Registered: March 2006
Location: Italy

Senior Contributor

Here is another achievement. The Honza's tecnique applied to insert. It is not trivial as the sqlite syntax is different by that of mysql (this costed me a hour of time to figure out the problem). See here for details :

Original query (26 seconds)

  for (i=0; i<pairs.GetCount(); i++) {
      for (int j=0; j<TD.MaxBoards+2; j++) {t1[j]=0; t2[j]=0;}
      idw = pairs[i].idw;
      idb = pairs[i].idb;
      played =1; //regular game (default)
      if (idb==0 || idw==0) played = 0; //byed game       
	  {RTIMING("save-team"); SQL.Execute("INSERT INTO TEAMROUND (ROUND,BOARD,NW,NB, PLAYED) VALUES(?,?,?,?, ?)", round, i+1, idw, idb, played); }
      Team& team1 = VTeam.Get( idw );
      Team& team2 = VTeam.Get( idb );   
      if (idw) team1.GetPlayerTeam(t1); //get the white players
      if (idb) team2.GetPlayerTeam(t2); //get the white players
      int idTEAMROUND = SQL.GetInsertedId();   
      //save the player pairing
      for (b=1; b<=TD.NBoards; b++) {                
          gameplayed =1; //regular game (default)
          if (t2[b]==0 || t1[b]==0) gameplayed = 0; //byed game       
	      {RTIMING("save-player"); SQL.Execute("INSERT INTO PLAYERROUND (TEAMROUND_ID, ROUND,BOARD,NW,NB,PLAYED) VALUES(?,?,?,?,?, ?)", 
	      				idTEAMROUND, round, b, t1[b], t2[b], gameplayed); }
      }     
  }

New version (5 seconds)

  String teamround = "INSERT INTO PLAYERROUND (TEAMROUND_ID, ROUND,BOARD,NW,NB,PLAYED) select 0 as TEAMROUND_ID, 0 as ROUND, 0 as BOARD, 0 as NW, 0 as NB,0 as PLAYED ";
  int npair = pairs.GetCount();  
  for (i=0; i<pairs.GetCount(); i++) {
      for (int j=0; j<TD.MaxBoards+2; j++) {t1[j]=0; t2[j]=0;}
      idw = pairs[i].idw;
      idb = pairs[i].idb;
      played =1; //regular game (default)
      if (idb==0 || idw==0) played = 0; //byed game       
	  SQL.Execute("INSERT INTO TEAMROUND (ROUND,BOARD,NW,NB, PLAYED) VALUES(?,?,?,?, ?)", round, i+1, idw, idb, played);
      Team& team1 = VTeam.Get( idw );
      Team& team2 = VTeam.Get( idb );   
      if (idw) team1.GetPlayerTeam(t1); //get the white players
      if (idb) team2.GetPlayerTeam(t2); //get the white players
      int idTEAMROUND = SQL.GetInsertedId();   
      //save the player pairing
      for (b=1; b<=TD.NBoards; b++) {                
          gameplayed =1; //regular game (default)
          if (t2[b]==0 || t1[b]==0) gameplayed = 0; //byed game       	      				
	  teamround += Format("UNION SELECT %d,%d,%d,%d,%d,%d ", idTEAMROUND, round, b, t1[b], t2[b], gameplayed); 
      }     
  }
  SQL.Execute(teamround);

Perhaps the previous ugly query stored in teamround string can be simplified with the U++ syntax... if one knows how to do.

Regards,
Luigi

Report message to a moderator