Watching a MySQL table in VB.net

Question

4.00/5 (1 vote)

See more:

I am developing a whole bunch of applications (that run on a network) and connect to common MySQL DB.
Of these applications, most of them involve waiting for a table to
1. Have a new row
2. Change a field
3. Retrieve rows by a field condition
My first instinct was to create a thread using System.Threading.Thread(AddressOf X)
and then have a While True loop inside that keeps on querying the table and then sleeps for a small amount of time (around 250 - 1000ms)
The problem is that the applications cause my System CPU usage to jump to 100% (mind you, I am using a Quad Core, 4GB RAM) and memory usage to hit 100% too!
It doesn't usually matter how many of the applications are running from a system.
So I got to thinking that maybe threading is not the answer. Is it because of the constant Querying?
Could anyone experienced in DB watching please help me out?
I already have the threading based applications. Most of them are like this:

1. MainUI.frm will have a Class

TableWatcherX

2.

TableWatcherX

will have a Start() method and a

private workingThread as Thread

3. In the Start() method, I put

workingThread = new Thread(AddressOf KeepWorking)
                   workingThread.Start()

4. In the method KeepWorking()

While True
    Using ATable as <datasettable> = <datasetadapter>.getData
          For each of the rows, process and do stuff...
    End Using
End While</datasetadapter></datasettable>

Apparently something is wrong, as I've got my memory and CPU completely locked!
Any thoughts?

Posted 21-Mar-11 1:27am

Hariharan Arunachalam

Updated 3-Nov-17 0:51am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

#realJSOP · Answer 1 · 2011-03-21T01:34:00

Write a stored procedure that checks the database. The stored procedure should return "true" if the desired changes where detected.

Change your while loop to this:

VB

Dim hasData as Boolean
Dim cmd as SqlCommand = new SqlCommand()
' setup your SqlCommand object to connect to your db and call the stored proc

while True
    hasData = CTyle(cmd.ExecutreScalar(), Boolean)
    if (hasData) Then
        'query the database (using a stored procedure again)
        'process ythe returned data
    End If
    Thread.Sleep(5000) 'sleep for 5 seconds
End While

Also, put a try/catch block around that code and handle the ThreadAbortException. It'ds okay to eat the exception because the thread aborts when you call the Thread.Abort method.

Dave Kreskowiak · Answer 2 · 2011-03-21T08:12:00

Solution 2

Your solution is not scalable in the least. The larger the monitored table grows, the worse your problem becomes. Since your code retrieves the ENTIRE table to see if anything changed, every couple of seconds, it's no wonder your code pegs the processor.

Since you're using MySQL, you don't have access to any Notification Services like you would on SQL Server. Like John said, you'd have to look into using stored procedures to store some kind of information that each client can use to detect new rows being added or changed. One method would be to assign time-stamped serial numbers to each row in the monitored table. Your stored procedure could then be supplied the last serial number that a client saw in orde to retrieve only the records that have been changed or updated.

Any record deletes would have to be recorded in a deleted table. You would also have to have another table to record any queries made to your monitored tables. Of course, ALL of this would have to be handled by stored procedures.

Posted 21-Mar-11 8:12am

Dave Kreskowiak

Comments

Hariharan Arunachalam 22-Mar-11 3:21am

Thank you Dave for explaining one of the things I was looking for.
On a side note, I am not retrieving the entire table. There is a status field that is set on a field when the first client fetches it. (As many clients do different levels of processing on the row). The next client has to wait until the previous client bumps the status flag to its level (XSD Query-> SELECT * FROM Table WHERE Status = 2).
I now have concerns if this could be because most of the queries use SELECT *. Would it make a difference if I used each field name? I need all the columns in most cases. Is there a performance issue between the both?

Dave Kreskowiak 22-Mar-11 7:26am

SELECT * returns the entire row, no matter how many columns are in it. Specifying all column names in the SELECT query doesn't make any difference in terms of performance.

Where it can screw you is if you change the database schema and your code uses numeric indexes to get at data in certain columns instead of strings. If the column order changes your index numbers in your code will no longer point to the same columns they did before the change.

Hariharan Arunachalam 23-Mar-11 1:19am

Thanks again Dave.
Been sleeplessly trying to find out an alternative database to use. Thinking of using an IMDB. But can't find any that can work and integrate with .Net.
Any suggestions?

Dave Kreskowiak 23-Mar-11 8:06am

The only one that I know of that does notifications is SQL Server, and not the Express Edition.

Andrew Kelly · Answer 3 · 2017-11-03T00:51:00

Solution 3

Perhaps add Insert, Update, Delete triggers to the table to log row in a new table with two columns..

<uid of row changed>
<type of change insert/update/delete>

Then you access this table and it tells you the specifically which rows were inserted, updated, deleted.

Posted 3-Nov-17 0:51am

Andrew Kelly

Updated 3-Nov-17 0:52am

v2

Comments

CHill60 3-Nov-17 14:58pm

The question is nearly 7 years old! Stick to answering new questions where the OP still needs help