We are building an application which requires a daily insertion of approximately 1.5 million rows of data per table. We have 16 tables. We keep track of 3-day historical data including the current day's data.
我们正在构建一个应用程序,每个表每天需要插入大约150万行数据。我们有16张桌子。我们会跟踪3天的历史数据,包括当天的数据。
The application is done using C#; on the server side, we run an exe that fills the data tables during market hours (4.5 hours), and we update the 16 tables every 5 seconds.
应用程序使用C#完成;在服务器端,我们运行一个在市场营业时间(4.5小时)填充数据表的exe,并且我们每5秒更新16个表。
On the client side, the application gets user queries which require the most recently inserted data ( in the last 5 seconds) and a historical point which could be today or before, and plots them somehow.
在客户端,应用程序获取用户查询,这些查询需要最近插入的数据(在最后5秒内)和可能在今天或之前的历史点,并以某种方式绘制它们。
We are having some serious performance issue, as one query might take 1 second or more which is too much. The question is, for today's data that is being inserted at runtime, can we make use of caching instead of going to the database each time we want something from today's data? Will that be more efficient? And if so, how can we do that? P.S one day data is approximately 300 MB, and we have enough RAM
我们遇到了一些严重的性能问题,因为一个查询可能需要1秒或更长时间。问题是,对于今天在运行时插入的数据,我们是否可以使用缓存而不是每当我们想要今天的数据时进入数据库?这会更有效吗?如果是这样,我们怎么做呢? P.S一天的数据大约是300 MB,我们有足够的RAM
1 个解决方案
#1
Keep a copy of the data along with the datetime you used to retrieve the data. The next time, retrieve only the new data, which minimizes the amount of data you send over the wire.
保留数据的副本以及用于检索数据的日期时间。下一次,只检索新数据,这会最大限度地减少您通过网络发送的数据量。
If it is that all the queries run in the operation amount to 1 sec, maybe the issue you are seeing is that the UI is freezing. If that is the case, don't do it on the UI thread.
如果所有查询在操作中运行的时间为1秒,那么您可能遇到的问题是UI处于冻结状态。如果是这种情况,请不要在UI线程上执行此操作。
Upate (based on comments): the code you run in the event handlers of the controls, runs in the UI thread, which is what causes the UI to freeze. There isn't a single way to run it in a separate thread, I suggest BackGroundWorker for this scenario. Look the community provided example at the end.
Upate(基于注释):您在控件的事件处理程序中运行的代码在UI线程中运行,这是导致UI冻结的原因。没有一种方法可以在单独的线程中运行它,我建议BackGroundWorker用于此场景。最后查看社区提供的示例。
#1
Keep a copy of the data along with the datetime you used to retrieve the data. The next time, retrieve only the new data, which minimizes the amount of data you send over the wire.
保留数据的副本以及用于检索数据的日期时间。下一次,只检索新数据,这会最大限度地减少您通过网络发送的数据量。
If it is that all the queries run in the operation amount to 1 sec, maybe the issue you are seeing is that the UI is freezing. If that is the case, don't do it on the UI thread.
如果所有查询在操作中运行的时间为1秒,那么您可能遇到的问题是UI处于冻结状态。如果是这种情况,请不要在UI线程上执行此操作。
Upate (based on comments): the code you run in the event handlers of the controls, runs in the UI thread, which is what causes the UI to freeze. There isn't a single way to run it in a separate thread, I suggest BackGroundWorker for this scenario. Look the community provided example at the end.
Upate(基于注释):您在控件的事件处理程序中运行的代码在UI线程中运行,这是导致UI冻结的原因。没有一种方法可以在单独的线程中运行它,我建议BackGroundWorker用于此场景。最后查看社区提供的示例。