处理大型数据集的图形和显示的最佳方法 [英] Best way to handle graphing and display of large data sets

问题描述

我们的网站为我们的客户提供各种数据服务；其中之一是仪表数据.有些仪表每 15 分钟记录一次信息，有些则每分钟记录一次.这些数据被发送到我们的 SQL 数据库.

Our website provides various data services to our clients; one of which is gauge data. Some gauges log information every 15 minutes, some every minute. This data is sent to our SQL database.

所有这些数据都通过图表(通过 PHP 和 JPGraphs 生成的服务器端)显示，每个单独的日志条目都显示为可折叠表 (jquery 1.10.2) 中的一行.

All of this data is displayed via a graph (generated server side via PHP and JPGraphs) with each individual log entry being displayed as a row in a collapsible table (jquery 1.10.2).

当客户想要查看数据时，他们会选择一个日期范围以及他们想要查看的量表.如果他们想查看每分钟记录一次的仪表的最后 3 天，那么它的加载速度非常快.如果他们想查看其中的 2 个，则加载大约需要 15-30 秒.当他们想要查看一个月的数据时，真正的问题就出现了.尤其是超过1个规格.这可能需要 15-20 分钟以上的时间来加载，并且浏览器会反复询问我们是否要阻止脚本填充可折叠表行 (jquery).

When a client wants to view the data, they select a date range and which gauges they would like to view. If they want to view the last 3 days of a gauge that logs every minute then it loads pretty quickly. If they want to view 2 of those then it takes around 15-30 seconds to load. The real problem comes when they want to view a months worth of data; especially more than 1 gauge. This can take upwards of 15-20 minutes to load and the browser repeatedly asks if we want to stop the script from populating the collapsible table rows(jquery).

显然这是一个问题，因为客户需要相对快速的响应(最多 1-5 分钟).理想情况下，我们还希望能够一次从几个月中提取仪表数据.我们现在能做到的唯一方法是一次提取 2 周的数据并手动编译总数.

Obviously this is a problem since clients want a relatively fast response (1-5 min max). Ideally, we would also like to be able to pull gauge data from several months at a time. The only way we can do that now is to pull data 2 weeks at a time and compile the total manually.

供参考:如果我想为我们的 2 个每分钟记录一次的仪表提取一个月的数据，那么将有 86,400 行通过 jQuery 添加到可折叠表中.该页面大约需要.加载时间为 5 分钟，在此期间浏览器非常慢.

For reference: If I wanted to pull a months data for 2 of our once-a-minute-logging gauges, then there would be 86,400 rows added via jQuery to a collapsible table. The page takes approx. 5 minutes to load and the browser is terribly slow during this time period.

我的问题是:使用基于 PHP 的服务器(Symfony 1.4 框架)和 javascript 提取/绘制/填充数据的最佳方法是什么?

My question is: What is the best way to pull/graph/populate data using a PHP based server (Symfony 1.4 framework) and javascript?

我们是否应该考虑升级我们分配的处理能力/RAM(我们由 GoDaddy 托管)?有没有比 jquery 更快的方法来填充可折叠对象?我们所有的计算都是在服务器端完成的.我们是否应该只提取原始数据并让客户端进行数据处理?我们应该在客户端和服务器之间拆分数据处理吗?

Should we look into upgrading our allotted processing power/RAM(we are hosted by GoDaddy)? Is there a faster way to populate collapsibles than with jquery? All of our calculatoins are done server side. Should we just pull the raw data and let the client side do the data processing? Should we split the data processing between client and server?

这是网页的屏幕截图.它被裁剪，以便不显示更多客户端敏感信息:

Here's a screen shot of the web page. Its cropped so that more client-sensitive information is not displayed:

推荐答案

回应我的评论.

由于您只在服务器端需要整个数据集(您在服务器上创建图形)，这意味着您实际上不需要将整个数据集发送到客户端.

Since you need the entire data-set only on the server side (you create your graph on the server), this means that you don't actually need to send the entire data-set to the client.

而是将一小部分发送给客户端.假设前 200 个结果.然后，您可以继续将结果集的其余部分缓存到 JSON 文件中(精简数据库，无论您真正想要什么).然后创建一个界面，用户可以在其中请求更多数据.无限滚动很好，但有其自身的问题.也许只是一个显示加载更多数据的按钮.正如人们所说，一次在一张表中超过几百个数据点是很疯狂的，因为人们无论如何都不会看它.然后，当他们点击按钮获取更多数据时，您向服务器发送一个 AJAX 请求，其中包含您想要的数据的正确参数.

Instead send a small portion to the client. Let's say the first 200 results. Then you can go ahead and cache the rest of the result-set into a JSON file (lite database, whatever you want really). Then create an interface where the user can request for more data. Infinity scroll is nice but has its own problems. Maybe just a button that says load more data. As people have said anything more than a few hundred data points in a table at one time is crazy to have because people won't look at it anyways. Then when they hit the button to get more data, you send an AJAX request to the server with the correct parameters for what data you want.

例如，当他们第一次单击 getMoreData() 时，您想要获取接下来的 200 个数据点.所以你发送 getMoreData(start=200, length=200).您的服务器接收 AJAX 请求并在 JSON 文件或 lite 数据库中找到正确的数据，无论您将结果缓存到何处.并且用户可以继续请求更多数据(确保更新开始参数)，并且您只返回一小部分.用户甚至没有意识到他们面前没有整个数据集，因为看起来他们有.

For example the first time they click getMoreData() you want to get the next 200 data points. So you send getMoreData(start=200, length=200). Your server picks up the AJAX request and finds the correct data in the JSON file or the lite database, wherever you have cached the results. And the user can keep requesting more data (making sure you update your start parameter), and you only ever return a small subset. The user doesn't even realize that they don't have the whole data-set there in front of them because it looks like they do.

对此比较复杂的是排序和搜索.如果要实现这些，则需要确保转到服务器端并对缓存的结果进行排序/搜索.

One that is complicated about this is sorting and searching. If you want to implement those then you need to make sure you go to the server side and sort/search through the cached results.

所以基本上你有一个系统，你可以在服务器端创建整个图形，这应该不会花很长时间.需要很长时间的是将整个数据集加载到客户端.所以你把它分成小块.您甚至可以使用此方法轻松创建分页等.

So basically you have a system where you can create the entire graph on the server side which shouldn't take long. What does take long is the loading of the entire data-set to the client side. So you break up that up into small chunks. You can even easily create pagination and the such with this method.

这篇关于处理大型数据集的图形和显示的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！