| View previous topic :: View next topic |
| Author |
Message |
Primogenitor
Joined: 08 Jan 2005
Posts: 88
|
 Posted: Tue Mar 29, 2005 14:46 Post subject: Multithreading database access? |
 |
|
Is it possible to set up SQLite to run as a multithreaded operation? I have a very large and complex SELECT query on a huge dataset and because its blocking, the server timeouts any clients connected.
Heres the query:
| Code: |
SQL = "SELECT cached2da_feat.rowid, LABEL, PREREQFEAT1, PREREQFEAT2, OrReqFeat0,"
+" OrReqFeat1, OrReqFeat2, OrReqFeat3, OrReqFeat4, REQSKILL, REQSKILL2,"
+" ReqSkillMinRanks, ReqSkillMinRanks2, cached2da_cls_feat.FeatIndex"
+" FROM cached2da_feat, cached2da_cls_feat "
+" WHERE (LABEL != '****')"
+" AND (cached2da_cls_feat.file = '"+sFeatList+"')"
+" AND ((cached2da_cls_feat.List = '****') OR (cached2da_cls_feat.List = 0) OR (cached2da_cls_feat.List = 1))"
+" AND ((PreReqEpic = '****') OR (PreReqEpic = 0))"
+" AND (ALLCLASSESCANUSE = 0)"
+" AND ((MINATTACKBONUS = '****') OR (MINATTACKBONUS <= "+IntToString(nBAB)+"))"
+" AND ((MINSPELLLVL = '****') OR (MINSPELLLVL <= "+IntToString(nCasterLevel)+"))"
+" AND ((MINSTR = '****') OR (MINSTR <= "+IntToString(nStr)+"))"
+" AND ((MINDEX = '****') OR (MINDEX <= "+IntToString(nDex)+"))"
+" AND ((MINCON = '****') OR (MINCON <= "+IntToString(nCon)+"))"
+" AND ((MININT = '****') OR (MININT <= "+IntToString(nInt)+"))"
+" AND ((MINWIS = '****') OR (MINWIS <= "+IntToString(nWis)+"))"
+" AND ((MINCHA = '****') OR (MINCHA <= "+IntToString(nCha)+"))"
+" AND ((MaxLevel = '****') OR (MaxLevel > "+IntToString(nLevel)+"))"
+" AND ((MinFortSave = '****') OR (MinFortSave <= "+IntToString(nFortSave)+"))"
+" AND (cached2da_feat.rowid = cached2da_cls_feat.FeatIndex)"
+" LIMIT 25, "+IntToString(i);
|
and the dataset its being performed on is huge. Its a table thats a copy of a 21989 line feat.2da files. Also in the database is a lot of other data, its 13,375kb in size alltogether.
And running the database on another server is not a practical option, this is supposed to be aimed at small PW/LANs and single players. |
|
| Back to top |
|
 |
NoMercy
Joined: 03 Jan 2005
Posts: 123
Location: UK
|
| Posted: Tue Mar 29, 2005 17:25 Post subject: |
|
|
It's an idea, but I think for the majority of people, using a multi-threaded interface would just be a pain. And likely that if you use it once, you'd have to use it for all queries, as one long query would then delay all the others.
Any hope in optimising that join, or pre-computing most of it somehow? |
|
| Back to top |
|
|
Primogenitor
Joined: 08 Jan 2005
Posts: 88
|
| Posted: Wed Mar 30, 2005 15:08 Post subject: |
|
|
Another benefit of multithreading might be that you could use stacked queries. e.g.
| Code: |
SQLExecDirect(SELECT Something);
while(PRC_SQLFetch() == PRC_SQL_SUCCESS)
{
SQLExecDirect(SELECT something else);
while(PRC_SQLFetch() == PRC_SQL_SUCCESS)
{
do something based on both results
}
}
|
As for this particular one, I have had great sucess using INNER JOIN to link the tables rather than , so it looks like
FROM cached2da_cls_feat INNER JOIN cached2da_feat |
|
| Back to top |
|
|
Dal
Joined: 27 Mar 2005
Posts: 6
|
| Posted: Wed Mar 30, 2005 15:19 Post subject: |
|
|
| That query string is a monster, maybe turning it into a stored procedure might speed things up slightly. |
|
| Back to top |
|
|
NoMercy
Joined: 03 Jan 2005
Posts: 123
Location: UK
|
| Posted: Wed Mar 30, 2005 15:45 Post subject: |
|
|
What you seem to be suggesting is concurrent queries, where the only problem is locking between tables, I think MySQL can do this but I don't think sqlite can.
When I think of multi-threaded database access, something like this comes to mind:
| Code: | void spin( int id )
{
if ( SQL_Ready( id ) == FALSE )
{
DelayCommand( 0.6f, spin( id ) );
return;
}
while( SQLFetch() == SQL_SUCCESS )
{
// do something based on both results
}
}
void main()
{
int id = SQLExecDirect("SELECT something FROM something");
DelayCommand( 0.2f, spin( id ) );
} |
Last edited by NoMercy on Wed Mar 30, 2005 19:45; edited 1 time in total |
|
| Back to top |
|
|
Dal
Joined: 27 Mar 2005
Posts: 6
|
| Posted: Wed Mar 30, 2005 16:21 Post subject: |
|
|
| That's a nice, clean solution, NoMercy.. assuming the first While is really supposed to be an If and it's called as a feature of a plugin and not a standard requirement to treat all queries that way. The hard part is finding a sucker to write the dataset caching classes. |
|
| Back to top |
|
|
NoMercy
Joined: 03 Jan 2005
Posts: 123
Location: UK
|
| Posted: Wed Mar 30, 2005 19:44 Post subject: |
|
|
| *nods* true, didn't spot that one, so many damn while loops in nwn, starting to write it without thinking, modified it now. I could probably write one, but I'm bussy writing pointless C++ classes for other things but how hard can a cache for SQL results be :) |
|
| Back to top |
|
|
mfx
Joined: 13 Feb 2005
Posts: 19
Location: Stockholm, Sweden
|
| Posted: Thu Mar 31, 2005 8:31 Post subject: |
|
|
All those OR need to be looked at.
OR is your number one enemy in that query. See if you can redesign the database and get rid of a few. That will speed up things for you.
_________________
MNXJ - Java API for NWN : https://www.sourceforge.net/projects/mnxj/ |
|
| Back to top |
|
|
Athanor Salamander
Joined: 15 May 2005
Posts: 6
|
| Posted: Thu Jul 14, 2005 20:00 Post subject: |
|
|
What happen if two objects in game fire a SQL query in the same time ?? Does one could have any data from another 's query object ??
_________________
"The Wolf, the Ram, the Hart"
Humanity is a Weakness Science.
Atha, Script Artist |
|
| Back to top |
|
|
Acrodania
Joined: 02 Jan 2005
Posts: 208
|
| Posted: Thu Jul 14, 2005 20:35 Post subject: |
|
|
| Athanor Salamander wrote: | | What happen if two objects in game fire a SQL query in the same time ?? Does one could have any data from another 's query object ?? |
In NWN the script engine is single-threaded. Two scripts cannot fire at the same time, they go into a que and are executed in the order they were received.... |
|
| Back to top |
|
|
NoMercy
Joined: 03 Jan 2005
Posts: 123
Location: UK
|
| Posted: Fri Jul 15, 2005 20:24 Post subject: |
|
|
| NWN is single threaded, as is the current database model, using my example of a possible script above, I guess you'd have to pass the quiery ID into the fetchData() function as well to allow mutiple concurrent queries. |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Mon Jul 18, 2005 5:58 Post subject: Re: Multithreading database access? |
|
|
| Primogenitor wrote: | Is it possible to set up SQLite to run as a multithreaded operation? I have a very large and complex SELECT query on a huge dataset and because its blocking, the server timeouts any clients connected.
Heres the query:
| Code: |
SQL = "SELECT cached2da_feat.rowid, LABEL, PREREQFEAT1, PREREQFEAT2, OrReqFeat0,"
+" OrReqFeat1, OrReqFeat2, OrReqFeat3, OrReqFeat4, REQSKILL, REQSKILL2,"
+" ReqSkillMinRanks, ReqSkillMinRanks2, cached2da_cls_feat.FeatIndex"
+" FROM cached2da_feat, cached2da_cls_feat "
+" WHERE (LABEL != '****')"
+" AND (cached2da_cls_feat.file = '"+sFeatList+"')"
+" AND ((cached2da_cls_feat.List = '****') OR (cached2da_cls_feat.List = 0) OR (cached2da_cls_feat.List = 1))"
+" AND ((PreReqEpic = '****') OR (PreReqEpic = 0))"
+" AND (ALLCLASSESCANUSE = 0)"
+" AND ((MINATTACKBONUS = '****') OR (MINATTACKBONUS <= "+IntToString(nBAB)+"))"
+" AND ((MINSPELLLVL = '****') OR (MINSPELLLVL <= "+IntToString(nCasterLevel)+"))"
+" AND ((MINSTR = '****') OR (MINSTR <= "+IntToString(nStr)+"))"
+" AND ((MINDEX = '****') OR (MINDEX <= "+IntToString(nDex)+"))"
+" AND ((MINCON = '****') OR (MINCON <= "+IntToString(nCon)+"))"
+" AND ((MININT = '****') OR (MININT <= "+IntToString(nInt)+"))"
+" AND ((MINWIS = '****') OR (MINWIS <= "+IntToString(nWis)+"))"
+" AND ((MINCHA = '****') OR (MINCHA <= "+IntToString(nCha)+"))"
+" AND ((MaxLevel = '****') OR (MaxLevel > "+IntToString(nLevel)+"))"
+" AND ((MinFortSave = '****') OR (MinFortSave <= "+IntToString(nFortSave)+"))"
+" AND (cached2da_feat.rowid = cached2da_cls_feat.FeatIndex)"
+" LIMIT 25, "+IntToString(i);
|
and the dataset its being performed on is huge. Its a table thats a copy of a 21989 line feat.2da files. Also in the database is a lot of other data, its 13,375kb in size alltogether.
And running the database on another server is not a practical option, this is supposed to be aimed at small PW/LANs and single players. |
Still a problem?
What indexing are you using?
You should have index on cached2da_feat.rowid
and another on cached2da_cls_feat.FeatIndex
This will stop tablescan's, which I assume is your problem, as you only want 25 rows.
Also consider replacing the '****' (INVALID) with nulls during your import of the 2DA, you can then data-type your columns so that they are not all strings.
Cheers
Gryphyn |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
