为读写优化的数据库表 [英] Database tables optimized for both read and write
问题描述
我们有一个 Web 服务，可将数据泵入 3 个数据库表，还有一个 Web 应用程序，可在 SQL Server + ASP.Net 环境中以聚合格式读取该数据.
We have a web service that pumps data into 3 database tables and a web application that reads that data in aggregated format in a SQL Server + ASP.Net environment.
有太多数据到达数据库表，从它们读取的数据如此之多，而且速度如此之快，以至于系统开始出现故障.
There is so much data arriving to the database tables and so much data read from them and at such high velocity, that the system started to fail.
表上有索引，其中之一是唯一的.其中一个表有数十亿条记录，占用了几百 GB 的磁盘空间；另一张表较小，只有几百万条记录.每天清空一次.
The tables have indexes on them, one of them is unique. One of the tables has billions of records and occupies a few hundred gigabytes of disk space; the other table is a smaller one, with only a few million records. It is emptied daily.
我有什么选择可以消除同时读取和写入多个数据库表的明显问题?
What options do I have to eliminate the obvious problem of simultaneously reading and writing from- and to multiple database tables?
我对每一个优化技巧都感兴趣，尽管我们已经尝试了遇到的每一个技巧.
I am interested in every optimization trick, although we have tried every trick we came across.
我们无法选择安装 SQL Server 企业版以使用分区和 内存优化表.
We don't have the option to install SQL Server Enterprise edition to be able to use partitions and in-memory-optimized tables.
该系统用于从数万台设备收集健身追踪器数据，并在仪表板上实时向数千台设备显示数据.
The system is used to collect fitness tracker data from tens of thousands of devices and to display data to thousands of them on their dashboard in real-time.
推荐答案
要求和细节过于宽泛，无法给出具体答案.但是一个建议是设置第二个数据库并将日志传送到它.因此，原始数据库将是写入"数据库，而新数据库将成为读取"数据库.
Way too broad of requirements and specifics to give a concrete answer. But a suggestion would be to setup a second database and do log shipping over to it. So the original db would be the "write" and the new db would be the "read" database.
缺点
- 磁盘空间
- 读取数据库会因日志传输器的时间长度而过时
专业版- 可能会删除写入"数据库上的一些索引，这会/可能会提高性能- 然后您可以汇总读取"数据库中的表以提高查询性能
Pro - Could possible drop some of the indexes on "write" db, this would/could increase performance - You could then summarize the table in the "read" database in order to increase query performance
https://msdn.microsoft.com/en-us/library/ms187103.aspx
这篇关于为读写优化的数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
