在VB.net中观看MySQL表 [英] Watching a MySQL table in VB.net

问题描述

我正在开发一大堆应用程序（在网络上运行）并连接到常见的MySQL数据库。

这些应用程序中，大多数都涉及等待表格

1.换个新行

2.更改字段

3.按字段条件检索行

我的第一直觉是使用System.Threading.Thread（AddressOf X）创建一个线程
然后在里面有一个While True循环继续查询表然后睡了很短的时间（大约250 - 1000ms）

问题是应用程序导致系统CPU使用率上升到100％（请注意，我使用的是四核，4GB内存），内存使用率也达到100％！ br />
通常从系统运行的应用程序数量并不重要。

所以我开始认为线程可能不是答案。是不是因为不断的查询？

任何有DB观看经验的人都可以帮帮我吗？

我已经有了基于线程的应用程序。大多数都是这样的：



1. MainUI.frm将有一个Class

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.

2.

TableWatcherX

将有一个Start（）方法和一个

will have a Start() method and a

private workingThread as Thread

3.在Start（）方法中，我把

3. In the Start() method, I put

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

4.方法KeepWorking（）

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>

显然出现了问题，因为我的内存和CPU完全锁定了！

有什么想法吗？

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

推荐答案

编写一个检查数据库的存储过程。如果检测到所需的更改，存储过程应返回true。



将while循环更改为：

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:

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

此外，在该代码周围放置一个try / catch块并处理 ThreadAbortException 。可以使用异常，因为线程在调用 Thread.Abort 方法时中止。

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.

您的解决方案无法扩展至少。受监控的表越大，问题就越严重。由于您的代码检索ENTIRE表以查看是否有任何更改，每隔几秒钟，因此难怪您的代码会固定处理器。



由于您使用的是MySQL，您没有像在SQL Server上那样访问任何Notification Services。就像John所说，你必须考虑使用存储过程来存储某些信息，每个客户端都可以使用这些信息来检测正在添加或更改的新行。一种方法是为受监控表中的每一行分配带时间戳的序列号。然后，您的存储过程可以提供客户端在orde中查看的最后一个序列号，以仅检索已更改或更新的记录。



任何记录删除都将具有被记录在已删除的表中。您还必须有另一个表来记录对受监控表进行的任何查询。当然，所有这些都必须由存储过程处理。

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.

也许可以向表中添加Insert，Update，Delete触发器来记录具有两列的新表中的行..



<行更改>

<更改类型插入/更新/删除>



然后你访问这个表，它会告诉你具体插入，更新，删除哪些行。

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.

这篇关于在VB.net中观看MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！