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 #38614 is a reply to message #38613] Sat, 29 December 2012 13:21 Go to previous messageGo to previous message
forlano is currently offline  forlano
Messages: 1207
Registered: March 2006
Location: Italy
Senior Contributor
Hi Honza,

thank you very much for the anwer. These were the guilty tables

TABLE_(TEAMS)
	INT     (ID) PRIMARY_KEY AUTO_INCREMENT 
	INT_    (N) 
	STRING_ (NAMET, 30)
	STRING_ (ORIG, 30)
	STRING_ (REGION, 30)
	STRING_ (FED, 3) 
	INT_    (RTG)
	INT_    (STATUS) 
END_TABLE
 
TABLE_(PLAYERS)
	INT     (ID) PRIMARY_KEY AUTO_INCREMENT
	INT_    (TEAM_ID) INDEX
	INT_    (BOARD) 
	INT     (N) 
	STRING  (NAME, 30)
	STRING  (FED, 30)
	STRING_ (BDAY, 10)
	STRING_ (SEX, 1) 
	STRING_ (CAT, 5) 
	INT_    (IDFIDE) 
	INT_    (RTGFIDE) 
	STRING_ (IDNAT,10) 
	INT_    (RTGNAT) 
	INT_    (K)
END_TABLE


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

void vegadb::SetDatabase(String dbname)
{
    if (sqliteVT.IsOpen()) sqliteVT.Close();
    if (!FileExists(dbname)) {
		if(!sqliteVT.Open( dbname )) {
			Exclamation("Can't create or open database file");
			return;
		}
		SQL = sqliteVT;
//		sqliteVT.SetTrace();
		SqlSchema sch(SQLITE3);
		All_Tables(sch);
		Sqlite3PerformScript(sch.Upgrade());
		Sqlite3PerformScript(sch.Attributes());  
	}
	else {
		if(!sqliteVT.Open( dbname )) {
			Exclamation("Can't create or open database file");
			return;
		}
		SQL = sqliteVT;
//		sqliteVT.SetTrace();
		SqlSchema sch(SQLITE3);
	}
	SQL.ClearError();
}


Anyway I have not seen any improvement.
I used TIMING

TIMING players-update : 44.66 s - 57.33 ms (44.66 s / 779 ), min: 32.00 ms, max: 698.00 ms, nesting: 1 - 779
TIMING player-select : 44.78 s - 285.22 ms (44.78 s / 157 ), min: 134.00 ms, max: 844.00 ms, nesting: 1 - 157
TIMING team-update : 53.32 s - 339.64 ms (53.32 s / 157 ), min: 167.00 ms, max: 910.00 ms, nesting: 1 - 157

to discover I have understimated the delay Smile . The teams are 157 and players 779 but it looks wrong too. The players-update query seems to have problems. I'll try to understand what can be although at moment I do not see error.

Regards,
Luigi


[Updated on: Sat, 29 December 2012 13:25]

Report message to a moderator

 
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: Sun Jul 20 14:10:16 CEST 2025

Total time taken to generate the page: 0.03651 seconds