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   |
 |
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 . 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
|
|
|
Goto Forum:
Current Time: Sun Jul 20 14:10:16 CEST 2025
Total time taken to generate the page: 0.03651 seconds
|