对大表$ P $统计psented在网络上 [英] Statistics on large table presented on the web

问题描述

我们有大约30 000 0000行的大表数据和不断增长目前每天在每天100 000行，这一数字将随时间增加。

We have a large table of data with about 30 000 0000 rows and growing each day currently at 100 000 rows a day and that number will increase over time.

今天我们产生直接从数据库（MS-SQL 2012）不同的报告，做了很多的计算。

Today we generate different reports directly from the database (MS-SQL 2012) and do a lot of calculations.

的问题是，这需要时间。我们有索引等，但今天的人们要极快的报告。

The problem is that this takes time. We have indexes and so on but people today want blazingly fast reports.

我们也希望能够改变timeperiods，不同的方式来看待数据等。

We also want to be able to change timeperiods, different ways to look at the data and so on.

我们只需要看看这是一天的，所以我们可以从昨天把所有的数据，并用它做什么，以加快查询和报告的数据。

We only need to look at data that is one day old so we can take all the data from yesterday and do something with it to speed up the queries and reports.

所以，做任何你有一个解决方案的任何好的想法，这将是快，还是在网络上没有Excel或BI工具。

So do any of you got any good ideas on a solution that will be fast and still on the web not in excel or a BI tool.

今天，所有的报告都在asp.net C＃与web表单对MS SQL 2012表..

Today all the reports are in asp.net c# webforms with querys against MS SQL 2012 tables..

推荐答案

您有一个OLTP系统。通常你想最大限度这样的系统上的吞吐量。报告将要求门栓和门锁被带到采集数据。这有你的OLTP吞吐量，什么是好报告（附加索引）将是不利于你的OLTP，因为它会产生负面影响性能的拖累。甚至不认为掴 WITH（NOLOCK）是要减轻一些负担的。 ;）

You have an OLTP system. You generally want to maximize your throughput on a system like this. Reporting is going to require latches and locks be taken to acquire data. This has a drag on your OLTP's throughput and what's good for reporting (additional indexes) is going to be detrimental to your OLTP as it will negatively impact performance. And don't even think that slapping WITH(NOLOCK) is going to alleviate some of that burden. ;)

正如其他人所指出的，你可能会想看看从报告数据中分离出有效的数据。

As others have stated, you would probably want to look at separating the active data from the report data.

对表进行分区可以做到这一点，如果的你有企业版的。否则，你需要做一些两轮牛车如 Paritioned景观可能会或可能不会为你根据你的数据访问方式工作。

Partitioning a table could accomplish this if you have Enterprise Edition. Otherwise, you'll need to do some hackery like Paritioned Views which may or may not work for you based on how your data is accessed.

我想看看提取出系统所需要的数据在定期间隔和其他地方推动它。无论是在别处是一组不同的在同一数据库或同一个服务器或一个完全不同的服务器上的不同的目录表，将取决于变量（成本，时间来执行，数据的复杂性，速度要求，存储子系统等宿主）。

I would look at extracted the needed data out of the system at a regular interval and pushing it elsewhere. Whether that elsewhere is a different set of tables in the same database or a different catalog on the same server or an entirely different server would depend a host of variables (cost, time to implement, complexity of data, speed requirements, storage subsystem, etc).

因为它听起来像你没有超具体报告要求（目前你看昨天的数据，但它会很高兴看到更多的，等等），我想看看实施的列存储索引的情况报告表。它提供了查询聚集惊人的表现，甚至在同益聚合表你没有指定具体的粮食（WTD，MTD，YTD，等等）。缺点是，虽然它是一个只读数据结构（和存储器＆放大器;在创建索引的cpu猪）。 SQL服务器2014年将推出更新的列存储索引，这将是giggity但是这一段时间的假。

Since it sounds like you don't have super specific reporting requirements (currently you look at yesterday's data but it'd be nice to see more, etc), I'd look at implementing Columnstore Indexes in the reporting tables. It provides amazing performance for query aggregation, even over aggregate tables with the benefit you don't have to specify a specific grain (WTD, MTD, YTD, etc). The downside though is that it is a read-only data structure (and a memory & cpu hog while creating the index). SQL Server 2014 is going to introduce updatable columnstore indexes which will be giggity but that's some time off.

这篇关于对大表$ P $统计psented在网络上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！