I have an winform application that creates 5 threads to connect to and retrieve information from a database on a very slow connection (90 seconds for some queries).Each thread has it's own instance of a class for performing sql queries. When the queried data has been retrieved the main thread is notified by an event fired from the class running the query. After receiving the event various components of the main thread are updated such as display items or just a datatable holding data for later use. The queries are repeated at various intervals depending on the type of information that they are querying.
我有一个winform应用程序,它创建了5个线程来连接并在非常慢的连接上从数据库中检索信息(某些查询为90秒)。每个线程都有自己的类实例,用于执行sql查询。当检索到查询数据时,主线程将由运行查询的类触发的事件通知。在接收到该事件之后,更新主线程的各种组件,例如显示项目或仅保存数据以供以后使用。根据查询的信息类型,以不同的间隔重复查询。
Everything works great...but I am not happy. I feel that it should be done a different way, but I am not sure which way.
一切都很好......但我并不开心。我觉得它应该以不同的方式完成,但我不确定哪种方式。
Below is how I currently set up each thread:
以下是我目前如何设置每个线程:
string ConnectionString = @"Data Source=mySrv;Initial Catalog=myTbl;Connect Timeout=30;UID=sa;pwd=mypwd";
//thread #1
SQL_Requests ReasonRequests;
Thread Reason_Thread;
ReasonRequests = new SQL_Requests();
ReasonRequests.ProcessFinished += new SQL_Requests.ProcessFinished(ReasonRequests_Completed);
Reason_Thread = new Thread(ReasonRequests.ExecuteQuery);
ReasonRequests.DBQueryString = "select * from ReasonTable where staralingment = goodalignment"
ReasonRequests.DBConnection = ConnectionString;
//thread #2
SQL_Requests EventRequests;
Thread Event_Thread;
EventRequests = new SQL_Requests();
EventRequests.ProcessFinished += new SQL_Requests.ProcessFinished(EventRequests_Completed);
Event_Thread= new Thread(EventRequests.ExecuteQuery);
EventRequests.DBQueryString = "select * from EventTable where somefield = somevalue"
EventRequests.DBConnection = ConnectionString;
each Thread.start are at different intervals.
每个Thread.start都有不同的间隔。
any recommendations?
5 个解决方案
#1
Instead of spinning up your own threads you should take a look as the asynchronous methods for executing queries i.e. http://msdn.microsoft.com/en-ca/library/system.data.sqlclient.sqlcommand.beginexecutereader.aspx
你应该把它看作是执行查询的异步方法,而不是自己的线程,例如http://msdn.microsoft.com/en-ca/library/system.data.sqlclient.sqlcommand.beginexecutereader.aspx
You mentioned that your connection is slow, is it a low bandwidth connection or a high latency connection? If the data is being returned slowly because of insufficient bandwidth firing off multiple queries will just make things slower. If it is just a latency issue doing multiple queries at once may improve responsiveness.
您提到您的连接速度很慢,是低带宽连接还是高延迟连接?如果由于带宽不足而导致数据缓慢返回多个查询将导致速度变慢。如果只是一个延迟问题,一次执行多个查询可能会提高响应能力。
If you are performing a group of related queries you may also want to consider grouping them into a single command, or grouping them on the server by using a stored procedure. You can get additional result sets by using the NextResult method on the SqlDataReader.
如果您正在执行一组相关查询,您可能还需要考虑将它们分组为单个命令,或者使用存储过程将它们分组到服务器上。您可以使用SqlDataReader上的NextResult方法获取其他结果集。
#2
If your threads fetch data from the same server over a very slow connection (meaning that limited bandwidth is the main factor) you will not gain anything by using multiple threads.
如果您的线程通过非常慢的连接从同一服务器获取数据(意味着有限的带宽是主要因素),您将无法通过使用多个线程获得任何收益。
OTOH it could actually be better to use a single thread for all data fetch operations:
OTOH实际上可以更好地使用单个线程进行所有数据获取操作:
-
You will get a portion of the data after some time, so you can update the UI with that. Fetching in parallel would probably split the bandwidth, and you would get a long time without any data, and in the end the results would arrive shortly after another. Your UI would look less responsive that way.
一段时间后,您将获得一部分数据,因此您可以使用它更新UI。并行获取可能会分割带宽,并且您将获得很长时间没有任何数据,并且最终结果将很快到达。您的UI看起来反应不那么敏感。
-
If the selects cause a lot of I/O on the server, not having them execute in parallel could actually result in better throughput. Consider that other operations will be executed on the server too.
如果选择导致服务器上的大量I / O,那么不让它们并行执行实际上可以产生更好的吞吐量。考虑其他操作也将在服务器上执行。
IMHO you should keep the fetches in a thread for best responsiveness of the UI, but use only one.
恕我直言,你应该在线程中保留提取以获得最佳的UI响应,但只使用一个。
Edit: You state in the comment that fetches may take different amounts of time. If you can estimate which queries will be the fastest to complete the transfer, execute them first. Still assuming of course that the data transfer takes most of the time, not the query execution on the server.
编辑:您在评论中说明提取可能需要不同的时间。如果您可以估计哪些查询最快完成传输,请先执行它们。当然还假设数据传输占用大部分时间,而不是服务器上的查询执行。
If you can't estimate how long queries will take, or if bandwidth isn't the only limitation, using multiple threads may of course work best for you.
如果您无法估计查询将花费多长时间,或者带宽不是唯一的限制,那么使用多个线程当然可能最适合您。
#3
The typical reason you'd want to do this is if each of the 5 database queries was lengthy and there was some performance gain to be had by running them in parallel. If you're not going to get a performance gain from doing this, I would simply use one thread to do the database work and report its progress to the UI.
您想要执行此操作的典型原因是,如果5个数据库查询中的每个查询都很长,并且通过并行运行它们可以获得一些性能提升。如果您不希望通过这样做获得性能提升,我只需使用一个线程来完成数据库工作并将其进度报告给UI。
If there is a performance benefit to be had from parallel processing, I would use the built in thread pool (System.Threading.ThreadPool.QueueUserWorkItem). Unless you need some more control over the threads, the thread pool is perfect for "fire and forget" type operations. You queue up an operation, and it invokes a delegate when it's done.
如果从并行处理中获得性能优势,我将使用内置线程池(System.Threading.ThreadPool.QueueUserWorkItem)。除非您需要对线程进行更多控制,否则线程池非常适合“即发即弃”类型的操作。排队操作,并在完成后调用委托。
#4
Without knowing specifics, this leaves a bad taste in my mouth, too. I'm sure that it works. Whether it's dangerous depends on whether your main class is thread-safe or not. It sounds like you need to do some serious testing re: what happens when two requests are completed at the same time that both just did something with the same data.
在不知道具体细节的情况下,这也会在我的嘴里留下不好的味道。我确信它有效。是否危险取决于您的主类是否是线程安全的。听起来你需要做一些严肃的测试:当两个请求同时完成时会发生什么,这两个请求都是用相同的数据完成的。
Honestly, if I were to look for a "different way" to do this, I'd probably skip the multiple-thread access to the database, have a single thread doing all the database work, with worker threads doing any extra work that needs to happen (if any), and then reporting back to the main thread for DB access.
老实说,如果我要寻找一种“不同的方式”来做这件事,我可能会跳过对数据库的多线程访问,让一个线程完成所有数据库工作,工作线程做任何需要的额外工作发生(如果有),然后报告回主线程进行数据库访问。
#5
Instead of using plain threading to develop concurrent software, please also consider task parallelism.
请考虑任务并行性,而不是使用纯线程来开发并发软件。
Please read the following articles: http://blog.rednael.com/2009/02/05/ParallelProgrammingUsingTheParallelFramework.aspx http://blog.rednael.com/2009/03/16/ParallelFrameworkDownloadAndSupport.aspx
请阅读以下文章:http://blog.rednael.com/2009/02/05/ParallelProgrammingUsingTheParallelFramework.aspx http://blog.rednael.com/2009/03/16/ParallelFrameworkDownloadAndSupport.aspx
These are articles about basic parallel programming and also contain references to other articles about the background of parallelism. Included are examples in C# .Net. Also, it describes a lightweight parallel framework to work with tasks. Opposed to some other frameworks, this one is very light and very easy to use. After reading these articles, you should be able to write code using parallelism.
这些是关于基本并行编程的文章,还包含有关并行性背景的其他文章的参考。包括C#.Net中的示例。此外,它描述了一个轻量级并行框架来处理任务。与其他一些框架相反,这个框架非常轻巧且易于使用。阅读这些文章后,您应该能够使用并行性编写代码。
Regards, Martijn
#1
Instead of spinning up your own threads you should take a look as the asynchronous methods for executing queries i.e. http://msdn.microsoft.com/en-ca/library/system.data.sqlclient.sqlcommand.beginexecutereader.aspx
你应该把它看作是执行查询的异步方法,而不是自己的线程,例如http://msdn.microsoft.com/en-ca/library/system.data.sqlclient.sqlcommand.beginexecutereader.aspx
You mentioned that your connection is slow, is it a low bandwidth connection or a high latency connection? If the data is being returned slowly because of insufficient bandwidth firing off multiple queries will just make things slower. If it is just a latency issue doing multiple queries at once may improve responsiveness.
您提到您的连接速度很慢,是低带宽连接还是高延迟连接?如果由于带宽不足而导致数据缓慢返回多个查询将导致速度变慢。如果只是一个延迟问题,一次执行多个查询可能会提高响应能力。
If you are performing a group of related queries you may also want to consider grouping them into a single command, or grouping them on the server by using a stored procedure. You can get additional result sets by using the NextResult method on the SqlDataReader.
如果您正在执行一组相关查询,您可能还需要考虑将它们分组为单个命令,或者使用存储过程将它们分组到服务器上。您可以使用SqlDataReader上的NextResult方法获取其他结果集。
#2
If your threads fetch data from the same server over a very slow connection (meaning that limited bandwidth is the main factor) you will not gain anything by using multiple threads.
如果您的线程通过非常慢的连接从同一服务器获取数据(意味着有限的带宽是主要因素),您将无法通过使用多个线程获得任何收益。
OTOH it could actually be better to use a single thread for all data fetch operations:
OTOH实际上可以更好地使用单个线程进行所有数据获取操作:
-
You will get a portion of the data after some time, so you can update the UI with that. Fetching in parallel would probably split the bandwidth, and you would get a long time without any data, and in the end the results would arrive shortly after another. Your UI would look less responsive that way.
一段时间后,您将获得一部分数据,因此您可以使用它更新UI。并行获取可能会分割带宽,并且您将获得很长时间没有任何数据,并且最终结果将很快到达。您的UI看起来反应不那么敏感。
-
If the selects cause a lot of I/O on the server, not having them execute in parallel could actually result in better throughput. Consider that other operations will be executed on the server too.
如果选择导致服务器上的大量I / O,那么不让它们并行执行实际上可以产生更好的吞吐量。考虑其他操作也将在服务器上执行。
IMHO you should keep the fetches in a thread for best responsiveness of the UI, but use only one.
恕我直言,你应该在线程中保留提取以获得最佳的UI响应,但只使用一个。
Edit: You state in the comment that fetches may take different amounts of time. If you can estimate which queries will be the fastest to complete the transfer, execute them first. Still assuming of course that the data transfer takes most of the time, not the query execution on the server.
编辑:您在评论中说明提取可能需要不同的时间。如果您可以估计哪些查询最快完成传输,请先执行它们。当然还假设数据传输占用大部分时间,而不是服务器上的查询执行。
If you can't estimate how long queries will take, or if bandwidth isn't the only limitation, using multiple threads may of course work best for you.
如果您无法估计查询将花费多长时间,或者带宽不是唯一的限制,那么使用多个线程当然可能最适合您。
#3
The typical reason you'd want to do this is if each of the 5 database queries was lengthy and there was some performance gain to be had by running them in parallel. If you're not going to get a performance gain from doing this, I would simply use one thread to do the database work and report its progress to the UI.
您想要执行此操作的典型原因是,如果5个数据库查询中的每个查询都很长,并且通过并行运行它们可以获得一些性能提升。如果您不希望通过这样做获得性能提升,我只需使用一个线程来完成数据库工作并将其进度报告给UI。
If there is a performance benefit to be had from parallel processing, I would use the built in thread pool (System.Threading.ThreadPool.QueueUserWorkItem). Unless you need some more control over the threads, the thread pool is perfect for "fire and forget" type operations. You queue up an operation, and it invokes a delegate when it's done.
如果从并行处理中获得性能优势,我将使用内置线程池(System.Threading.ThreadPool.QueueUserWorkItem)。除非您需要对线程进行更多控制,否则线程池非常适合“即发即弃”类型的操作。排队操作,并在完成后调用委托。
#4
Without knowing specifics, this leaves a bad taste in my mouth, too. I'm sure that it works. Whether it's dangerous depends on whether your main class is thread-safe or not. It sounds like you need to do some serious testing re: what happens when two requests are completed at the same time that both just did something with the same data.
在不知道具体细节的情况下,这也会在我的嘴里留下不好的味道。我确信它有效。是否危险取决于您的主类是否是线程安全的。听起来你需要做一些严肃的测试:当两个请求同时完成时会发生什么,这两个请求都是用相同的数据完成的。
Honestly, if I were to look for a "different way" to do this, I'd probably skip the multiple-thread access to the database, have a single thread doing all the database work, with worker threads doing any extra work that needs to happen (if any), and then reporting back to the main thread for DB access.
老实说,如果我要寻找一种“不同的方式”来做这件事,我可能会跳过对数据库的多线程访问,让一个线程完成所有数据库工作,工作线程做任何需要的额外工作发生(如果有),然后报告回主线程进行数据库访问。
#5
Instead of using plain threading to develop concurrent software, please also consider task parallelism.
请考虑任务并行性,而不是使用纯线程来开发并发软件。
Please read the following articles: http://blog.rednael.com/2009/02/05/ParallelProgrammingUsingTheParallelFramework.aspx http://blog.rednael.com/2009/03/16/ParallelFrameworkDownloadAndSupport.aspx
请阅读以下文章:http://blog.rednael.com/2009/02/05/ParallelProgrammingUsingTheParallelFramework.aspx http://blog.rednael.com/2009/03/16/ParallelFrameworkDownloadAndSupport.aspx
These are articles about basic parallel programming and also contain references to other articles about the background of parallelism. Included are examples in C# .Net. Also, it describes a lightweight parallel framework to work with tasks. Opposed to some other frameworks, this one is very light and very easy to use. After reading these articles, you should be able to write code using parallelism.
这些是关于基本并行编程的文章,还包含有关并行性背景的其他文章的参考。包括C#.Net中的示例。此外,它描述了一个轻量级并行框架来处理任务。与其他一些框架相反,这个框架非常轻巧且易于使用。阅读这些文章后,您应该能够使用并行性编写代码。
Regards, Martijn