SQL queries in own thread
Author
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! :)
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?
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?
Author
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? :)
if(work to do)
do work
else
sleep(10)
so it never dies? :)
Here's my ThreadProc from my MMORPG server:
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]
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]
Author
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?
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?
Quote:Original post by HazelnussI've never really looked at stored procedures before. This Link seems to suggest that they're A Bad Thing, but I'm not sure.
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 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).
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.
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
