Sign in to follow this  
Hazelnuss

SQL queries in own thread

Recommended Posts

Hazelnuss    122
Hello folks! At the moment I am writing a small client/server online game. Since SQL queries are blocking I decided to write a small query manager which does the job in an own thread. At the moment I have it set up so the thread dies when it has no more jobs and when there are new ones to work on the thread gets created again. My question is, since it's the first time I ever touched multithreading, if that's how it should work or if the thread should run in a while(true)-kind of loop with some kind of sleep delay inside? Thanks for any help, tips and information! :)

Share this post


Link to post
Share on other sites
swiftcoder    18437
I would go with the while (true) sleep approach, because otherwise thread synchronisation may cause it to diebefore you hand it its last task (because it finishes the previous task before it recieves a new one).
Of course, you could hand it all of its tasks before starting the thread, but what if you later need more queries after the initial set?

Share this post


Link to post
Share on other sites
Hazelnuss    122
If the thread died I would of course respawn it. My question is if that is okay or if I should let it idle like

if(work to do)
do work
else
sleep(10)

so it never dies? :)

Share this post


Link to post
Share on other sites
Evil Steve    2017
Here's my ThreadProc from my MMORPG server:

DWORD CDatabase::ThreadProc()
{
Event theEvent;
bool bQuit = false;

while(!bQuit)
{
// Grab the first event
EnterCriticalSection(&m_cs);
if(m_vEvents.empty())
{
LeaveCriticalSection(&m_cs);
Sleep(50);
continue;
}
theEvent = m_vEvents[0];
m_vEvents.erase(m_vEvents.begin());
LeaveCriticalSection(&m_cs);

// Process it
ResultEx theResult;
switch(theEvent.eEvent)
{
case Event::Quit: return 0;
case Event::GetUserByID: DoGetUserByID(theEvent,theResult); break;
case Event::GetUserByName: DoGetUserByName(theEvent,theResult); break;
case Event::GetCharacterByID: DoGetCharacterByID(theEvent,theResult); break;
case Event::GetCharacters: DoGetUsersCharacters(theEvent,theResult); break;
case Event::AddCharacter: DoAddCharacter(theEvent,theResult); break;
case Event::DeleteCharacter: DoDeleteCharacter(theEvent,theResult); break;
default: Assert(false); break;
}

// Add the result to the list
EnterCriticalSection(&m_csEventsOut);
m_vResults.push_back(theResult);
LeaveCriticalSection(&m_csEventsOut);
}

return 0;
}





Basically, a while loop. If your database isn't as busy as that, I'd use WaitForSingleObject() (Assuming C++ and Win32) to wait on a handle which you'd signal whenever you post an event. That way you don't waste CPU time (The OS can mark your thread as waiting and spend less CPU time on it).

To actually answer your question: You should use a while loop. [Re]spawning a thread is pretty expensive, and should be kept to a minimum (Hence why you may have heard of Thread Pools). If you're only doing a DB query every few seconds, I'd still use a while() loop, but use my event method.

I'm happy to post my entier DB class if you want. It's currently only for MySQL 4.0 though.

EDIT: Hmm, that while(bQuit) should be for(;;). It's a relic from some old code.

[Edited by - Evil Steve on March 15, 2006 1:10:10 PM]

Share this post


Link to post
Share on other sites
Hazelnuss    122
Thanks man! It now works perfectly and I added some priority stuff. :)

Another question: Right now I am sending strings containing the query to my database. Would it be wiser/better to execute stored procedures and is the efford worth it?

Share this post


Link to post
Share on other sites
Evil Steve    2017
Quote:
Original post by Hazelnuss
Thanks man! It now works perfectly and I added some priority stuff. :)

Another question: Right now I am sending strings containing the query to my database. Would it be wiser/better to execute stored procedures and is the efford worth it?
I've never really looked at stored procedures before. This Link seems to suggest that they're A Bad Thing, but I'm not sure.
I get pretty reasonable performance from just running text queries through MySQL (It took about 3 seconds to execute 3000 INSERT statements, although it takes a bit longer for SELECT).

Share this post


Link to post
Share on other sites
markr    1692
There are a lot of opinions on stored procedures. Some of them are based on false premises, others are not.

However, I have to share the following, which will hopefully be non-controvertial:

- If you use stored procedures, you'll need to learn the SP language of your database and have all the other developers understand it too
- If you use stored procedures, you have to have a way of versioning them, as they're part of your application. However, as they're stored in the database, they will tend not to update when you update the rest of your application. It's up to you to ensure the the SPs and the rest of the application stay "in sync". This applies to each developer's server as well as staging, production etc.
- Some of the cited performance benefits of SPs are a myth
- In many databases, you can do parameterised queries without a stored procedure*
- In many databases, you can do query batching without a stored procedure

Now you go and draw your own conclusions.

Mark

* I am aware that in MSSQL, some data access layers use their own stored procedure to implement prepared statements. However, this doesn't count as it's not visible to the developer, nor does it need to be maintained by him.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this