Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
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 Go to previous messageGo to previous message
dolik.rce is currently offline  dolik.rce
Messages: 1789
Registered: August 2008
Location: Czech Republic
Ultimate Contributor

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 Smile 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 Smile

Honza
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Use ctrls in another tab as detail problem
Next Topic: Can U++ communicate with a MSACCESS database?
Goto Forum:
  


Current Time: Tue May 07 04:56:51 CEST 2024

Total time taken to generate the page: 0.03091 seconds