SQL Server数据表数据量达到多大会影响查询和插入效率

[imlmy]

项目中使用SQL Server存储一些采集数据，每分钟一条数据，每条数据大概有200多个字段。每年大概有50多万条数据。其中包括两个表，表A存储时间，以自增字段为主键。表B存数据，以ID字段作为主键，同时也是外键，指向表A的主键。
原来是考虑使用几年之后会导致数据表过大而影响效率，就像按照年份每年的数据存一个表。用户查询的时候就按照年份来查，然后把查询结果union之后返回给用户。但是现在遇到一个问题就是使用了坑爹的ado来访问数据库，ado的游标不支持union操作，所以想改回将所有数据都存在一张表里面。
请问一张数据表的数据达到多少条之后会影响插入和查询效率，以前听过说达到100万条数据之后查询和插入效率会大大降低，是这样吗？

[最爱午夜]

你现在的数据大小完全不必考虑，除非出现了一行数据超过8060个字节，如果是超过，那么最好分表了，否则不用理会。

[imlmy]

引用 6 楼 ap0405140 的回复:

建议 1.用"union all"替代"union". 2.ado的游标不支持union操作 --> 应有解决方案.是否与CursorLocation属性有关. 或写为存储过程,传入起始日期&结束日期,在存储过程中union all总可以吧. 3.每年大概有50多万条数据 --> 这样的数据量,应可不必按年存一个表.

我是用union all。之前试过使用CursorLocation游标，但是这种情况就会导致所有数据都先缓存到本地，占用本地大量内存。写在存储过程里面返回结果集如果游标不是CursorLocation也会导致无法使用游标的Move功能。 看来还是放到同一张表里算了

[大力水手]

100多万虽不叫大数据，你有200多个列那就不太好说了。弱弱问一句这表是谁设计的。你这个一条数据库是不是就能占用一个数据页。如果从结构上设计的不好。那你的很多优化都是徒劳！

[niss]

引用 楼主 imlmy 的回复:

项目中使用SQL Server存储一些采集数据，每分钟一条数据，每条数据大概有200多个字段。每年大概有50多万条数据。其中包括两个表，表A存储时间，以自增字段为主键。表B存数据，以ID字段作为主键，同时也是外键，指向表A的主键。 原来是考虑使用几年之后会导致数据表过大而影响效率，就像按照年份每年的数据存一个表。用户查询的时候就按照年份来查，然后把查询结果union之后返回给用户。但是现在遇到一个问题就是使用了坑爹的ado来访问数据库，ado的游标不支持union操作，所以想改回将所有数据都存在一张表里面。 请问一张数据表的数据达到多少条之后会影响插入和查询效率，以前听过说达到100万条数据之后查询和插入效率会大大降低，是这样吗？

分区视图.....

[imlmy]

引用 2 楼 zhengnan2012 的回复:

你都按年分了，那么 为什么还会union呢？ 比如用户查今年，就去今年的表里查，查哪一年就去哪一年的表里查啊。

采用union的情况是在用户跨年份查询的时候，例如2012-3-4至2013-5-4

[imlmy]

引用 3 楼 u010998373 的回复:

100w不叫大数据，建议取消外键，数据分区

没有使用外键，只是那个字段实现了外键的功能在关联查询的时候使用。你说的数据分区是指分区表吗

[唐诗三百首]

建议 1.用"union all"替代"union". 2.ado的游标不支持union操作 --> 应有解决方案.是否与CursorLocation属性有关. 或写为存储过程,传入起始日期&结束日期,在存储过程中union all总可以吧. 3.每年大概有50多万条数据 --> 这样的数据量,应可不必按年存一个表.

[imlmy]

引用 10 楼 public0011 的回复:

100多万虽不叫大数据，你有200多个列那就不太好说了。弱弱问一句这表是谁设计的。你这个一条数据库是不是就能占用一个数据页。如果从结构上设计的不好。那你的很多优化都是徒劳！

是我设计的。之前是分成了多个表，后来发现插入的时候效率有点慢，而且查询的时候经常是全部200多个字段一起查询，所以改成了一个表。不过我现在是用另一个表去描述这个数据表，所以想改成多个表只需要修改一下那个描述表的值。分表除了从技术方面考虑，还需要从行业经验来分析，把经常一起查询的字段放在一张表。但是目前没有这方面的反馈。分成一个表主要是因为内存测试经常一次性全部查出来。

[imlmy]

引用 10 楼 public0011 的回复:

100多万虽不叫大数据，你有200多个列那就不太好说了。弱弱问一句这表是谁设计的。你这个一条数据库是不是就能占用一个数据页。如果从结构上设计的不好。那你的很多优化都是徒劳！

前面说错，部署内存测试，是内部测试。测试的时候并没有考虑行业的实际应用，而是主要针对所有数据进行压力测试，所以按照测试需求改了表设计。听起来有点扯

[东那个升]

100w的数据分个毛区啊。。。直接时间来个索引就可以了。

[lythappy]

200个字段，什么东西能有200个属性啊？100多万条记录不多，但存储应该很大了吧，分区表长远考虑应该用。union 是T-Sql和ADO有什么关系？ 一分钟插一条数据，开销不大，如果只查询几条数据，应该没有问题。如果有像你这种跨年的查询肯定会慢，没有好办法只能优化表结构或者优化索引。

[imlmy]

引用 15 楼 lythappy 的回复:

200个字段，什么东西能有200个属性啊？100多万条记录不多，但存储应该很大了吧，分区表长远考虑应该用。union 是T-Sql和ADO有什么关系？ 一分钟插一条数据，开销不大，如果只查询几条数据，应该没有问题。如果有像你这种跨年的查询肯定会慢，没有好办法只能优化表结构或者优化索引。

200多个字段是保存一个设备采集的数据，一共有200多项。采用ado查询数据返回游标的时候，需要通过游标的GetCount方法查询返回的数据量，但是如果查询语句中用到union，就无法通过GetCount获取数据量。 插入开销不大，所以之前设计的时候主要都是考虑查询。之前按照年份分表主要是考虑用户大部分查询应该都不会跨年，在一年内查询的话效率就会提高一些，特别是用多年后单表数据暴涨问题。

[大力水手]

引用 14 楼 ldslove 的回复:

100w的数据分个毛区啊。。。直接时间来个索引就可以了。

东升哥可是很久不来了。你知道我是谁吗？乒乓球台球都打败过你哦。

[东那个升]

引用 17 楼 public0011 的回复:

[quote=引用 14 楼 ldslove 的回复:] 100w的数据分个毛区啊。。。直接时间来个索引就可以了。

东升哥可是很久不来了。你知道我是谁吗？乒乓球台球都打败过你哦。 [/quote]你太賤了

[發糞塗牆]

1、请问一张数据表的数据达到多少条之后会影响插入和查询效率，以前听过说达到100万条数据之后查询和插入效率会大大降低，是这样吗？ 没这个说法，100万查询如果你扫描的话那估计性能够呛的，如果不是扫描的话速度应该不会有什么指数级别的降低。插入如果是插到最后（有聚集索引且按顺序插入），那千万数据也不会慢，反正不影响上面的数据，但是如果你要反转再反转，那........ 2、ado做这种事情一般都不是很擅长，去找找有没有其他方法把，而且你还用游标的话，如果没用好，性能会大打折扣。 3、100万数据的确没必要做分区或者分表，1个亿还差不多。

[發糞塗牆]

另外如果用到临时表，tempdb的配置、目标数据库数据文件的配置、增长比例等等都有可能影响效率。如果你觉得慢，应该全面排查，找出瓶颈

[大力水手]

引用 18 楼 ldslove 的回复:

[quote=引用 17 楼 public0011 的回复:] [quote=引用 14 楼 ldslove 的回复:] 100w的数据分个毛区啊。。。直接时间来个索引就可以了。

东升哥可是很久不来了。你知道我是谁吗？乒乓球台球都打败过你哦。 [/quote]你太賤了[/quote] you too！有空出来打打球，赌赌博。 更重要的是想多跟你学习点数据库方面的东西！

[shoppo0505]

换个SSD，啥问题都解决了。哈哈

[imlmy]

引用 19 楼 DBA_Huangzj 的回复:

1、请问一张数据表的数据达到多少条之后会影响插入和查询效率，以前听过说达到100万条数据之后查询和插入效率会大大降低，是这样吗？ 没这个说法，100万查询如果你扫描的话那估计性能够呛的，如果不是扫描的话速度应该不会有什么指数级别的降低。插入如果是插到最后（有聚集索引且按顺序插入），那千万数据也不会慢，反正不影响上面的数据，但是如果你要反转再反转，那........ 2、ado做这种事情一般都不是很擅长，去找找有没有其他方法把，而且你还用游标的话，如果没用好，性能会大打折扣。 3、100万数据的确没必要做分区或者分表，1个亿还差不多。

目前还没有大规模数据测试。聚集索引之类的都加了，主要是考虑查询的效率。听你这么说那我就放心了，就按照目前的方案来做

[handthing]

优化是具体情况，具体分析，硬件，改进表结构，索引，改进存储过程都有关系，你这样说的太龙统了。