Home » U++ Library support » U++ SQL » Doubts on a sqlite query
Re: Doubts on a sqlite query [message #38615 is a reply to message #38614] |
Sat, 29 December 2012 15:03 |
|
forlano wrote on Sat, 29 December 2012 13:21 | I have now added an index to TEAM_ID. I do not know if the index is created while the database is populated or even later. I hope it has been created with this code when I opened the existing DB
| I believe the sch.Upgrade() should create the index just fine. The index is normally created when it is added to the table and it is updated with each change to the contents of the table.
forlano wrote on Sat, 29 December 2012 13:21 | The players-update query seems to have problems. I'll try to understand what can be although at moment I do not see error.
| It actually seems to me that players-update is the best performing one out of those three It takes only 57 ms per call. Could you try to do it in fewer queries using CASE as I described above? The code should look something like this (not tested): String teams = "update TEAMS set N = case ID";
for(i=0; i<n; i++) {
teams += Format(" when %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]);}
String players = "update PLAYERS set N = case ID";
while (sqlplayer.Fetch()) {
idp = (int) (sqlplayer[0]);
players += Format(" when %i then %i", idp, np++);
}
{TIMING("players-update"); sqlp.Execute(players); }
}
{TIMING("teams"); sqlteam.Execute(teams);}
It should lower the query count to 157+157+1. I'm really curious if it cuts down the overall time significantly or not
Honza
|
|
|
Goto Forum:
Current Time: Tue May 07 04:56:51 CEST 2024
Total time taken to generate the page: 0.03091 seconds
|