Click here to Skip to main content
15,868,027 members
Please Sign up or sign in to vote.
2.33/5 (2 votes)
See more:
I'm writing an application in C# that needs to access a MySQL db in real-time on a continuous basis. I'm currently polling the database every millisecond using a Timer.

I'm assuming (probably correctly) that this isn't the most efficient way to go about this...can someone please tell me the "correct" way to go about this or least a more efficient way?

Thanks in advance,
-Dee
Posted
Updated 15-Oct-22 1:11am
v3
Comments
Addy Tas 26-Nov-11 18:39pm    
Do you have a way of knowing if someone may have changed the contense of the db? If there is a service managing it, it may give you an event when touched. That would prevent even more nop's
Member 15627495 18-Dec-22 5:37am    
the good use of a db needing 'reaction' , is to use 'trigger' embeded.

a trigger based on the event 'insert into' will fit.
querying your db every Ms is too loud.
the trigger will be on every new input.
// by your case, if you have just one new entry during an hour , your solution had request the db 1,000,000,000 times .. It's not relevant at all. //

read about 'triggers' for mysql, and code few for your db.

If you're using the timer, there is one problem: a polling time might sometimes be longer then the timer period; so what you are going to do if the polling is incomplete when another timer tick calls your callback or event handler?

Create a thread polling the database in cycle all the time. Now, you can do two things: 1) add some delay in cycle using System.Threading.Thread.Sleep; 2) Throttle thread cycle execution with EventWaitHandle and call EventWaitHandle.Set from the timer event handle. Both ways do not waste CPU time when a thread is in a wait or sleep state: OS switched the thread off and never schedule it back to execution until it is waken up by the expiration time (or Thread.Abort).

I prefer the second, a little more sophisticated way. It provides better timing. When a polling time is less then the timer period, the polling happens with the period of the timer, and when this period of time is not enough for polling, the process becomes just slower as in continues polling (it cannot be faster anyway, and the polling time depends on a query, current data and other factors, including random factors); in this case all redundant timer events are just ignored.

For this purpose, you can use the class System.Threading.AutoResetEvent. Your polling thread calls the method WaitOne on the instance of this class, and the timer calls Set of the same instance.

See:
http://msdn.microsoft.com/en-us/library/system.threading.eventwaithandle.aspx[^],
http://msdn.microsoft.com/en-us/library/system.threading.autoresetevent.aspx[^],
http://msdn.microsoft.com/en-us/library/system.threading.thread.aspx[^].

[EDIT]

After looking at the follow-up question, another suggestion: the thread, the timer and the event wait handle should be encapsulated in the thread wrapper, by several reasons I describe in my past solutions:

How to pass ref parameter to the thread[^],
change paramters of thread (producer) after it started[^].

—SA
 
Share this answer
 
v5
Comments
d.allen101 25-Nov-11 1:23am    
SA, I like your solution! Wanted to know if I would get the same results you're talking about if I used Thread.Timer a WaitHandle instead creating a managed thread and using Form.Timer?
Sergey Alexandrovich Kryukov 26-Nov-11 20:07pm    
Thank you, Donald.

No, you won't get the same results! First, be aware of Form.Timer: it is amazingly inaccurate. The only benefit (only good for beginners or simple tasks): the timer event happens in the UI thread. Use System.Timers.Timer (which I usually use) or System.Threading.Timer, but remember to use Control.Invoke or Control.BeginInvoke instead of direct call to any UI methods/properties. In the solution I offer, invocation is never needed, as you merely call the Set method of event wait handle, which can be called from any thread, as well as any other thread sync. API. This is another benefit of the solution I advise.

Second problem resolved by my solution: see the first paragraph of the above solution.

I think the benefits of this solution are apparent. If you agree, please accept the solution formally (green button) -- thanks.
--SA
BillWoodruff 26-Nov-11 21:58pm    
+5 A comprehensive, excellent answer. One minor comment on the first paragraph: "a polling time" : while I know what you mean is: that the time involved for a complete updating of the database may be longer than the timer interval, thus creating the potential for an update not being complete when the timer triggers another update. But, I wonder if that meaning will be clear to newcomers.
Sergey Alexandrovich Kryukov 26-Nov-11 22:10pm    
Thank you, Bill.
Well, I did not think "a polling time" was not obvious, so thank you for clarification.

Actually, even though I used my approach exactly for polling a database, my statement is more general. This is a problem of having a timer callback or event handle method incomplete at the moment of next tick.
--SA
d.allen101 27-Nov-11 8:37am    
SA, I need to make sure I'm clear on something, take a look at this and let me know if it's correct:

Threading.Timer t = new Threading.Timer(OnTimer_dbStuff, dataObj, 0, 1);
EventWaitHandle ewh = new AutoResetEvent(false);

void OnTimer_dbStuff(object dataObj)
{
ewh.Set();
ewh.WaitOne();

// database connection, queries, etc...
// update any win form controls via Invoke, etc...
}
If you know what you are looking for you can use: http://www.devart.com/dotconnect/mysql/docs/Devart.Data.MySql~Devart.Data.MySql.MySqlDependency.html

This can do the polling for you and may ( or may not ;) )do it more efficient.

Cheers Addy.
 
Share this answer
 
Comments
Sergey Alexandrovich Kryukov 26-Nov-11 20:11pm    
Sounds very interesting. Does it mean something like inversion control and server push? Is the polling done on server side or client side?
--SA
d.allen101 27-Nov-11 2:13am    
WOW! Devart seems really slick but the free edition doesn't offer the MySqlDependency
Addy Tas 27-Nov-11 4:49am    
Damn, i thought that that part was also included in the free version. Sorry for wasting your time on that one.
d.allen101 27-Nov-11 8:28am    
No it wasn't a waste of time. Now I know that MySql notifications can be sent I just have to figure out how! That's actually what I want do vs. polling! That would be 10x's more efficient!
Addy Tas 27-Nov-11 11:33am    
I've not taken a look at it in detail but i think that what you are looking for can be found under the term "MySQL Triggers"

from http://dev.mysql.com/doc/refman/5.1/en/triggers.html
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.
A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement executes for the associated table. A trigger can be set to activate either before or after the triggering statement. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated

And yes this beats polling any time, depending on the system your efficiency may be a lot more than 10x.



Cheers,



Addy

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900