MS SQL大表PK查找性能提升问题 [英] MS SQL large table PK lookup performance boost question

问题描述

嗨.


我们的一个大客户进行了数据库设计，他们在松散连接的表之间具有经典的一对多关系.它是一个数据表(PK增量bigint和一个VARBINARY数据列)和一个查找表= PK增量int，8列bigint引用数据表中的PK.


关系由业务层处理.这可以完美地工作.
在极少数情况下，它们需要主要按照数据表的顺序遍历这些表中的所有数据.
该设计导致查找表中的引用列将越来越远地引用数据表中的PK.


它们一次从数据表中选择8行，例如:

Hi.


One of our large customers have a database design where they have a classical one to many relation between to table loosely connected. It is a data table (PK incremental bigint and one VARBINARY Data columns) and a lookup table = PK incremental int, 8 columns bigint referring to the PK in the data table.


The relations is handled by the business layer. This works flawlessly.
In some rare situations they need to run through all the data in these tables mainly in order of the data table.
The design causes the reference columns in the lookup table will reference the PK in the data table with further and further distance.


They select 8 rows at a time from the data table like:

SELECT Data FROM DataTable WHERE Identifier IN (1,2,3,4,5,6,7,8)  
SELECT Data FROM DataTable WHERE Identifier IN (1,2,3,4,1000,10001,2000,2001)

问题在于，数据表中的PK距离越远，在处理过程中花费的时间就越长.


任何设计/技巧都可以解决此问题并提高性能吗?


关于Thomas
的问题

The issue is that the further apart the PK in the data table is, the longer it takes so during the process things slow down.


Can any design/tricks solve this and make a boost in performance?


Regards Thomas

推荐答案

如果您使用显示的原始查询，则应确保PK字段已正确索引.

If you are using raw queries as you show, then you should ensure that the PK fields are properly indexed.

如果PK是使用主键约束创建的，则索引应该可以，但是统计信息又如何呢?最近更新了吗?

这也可能是一个优化缺陷，因此强制进行索引扫描可以纠正这种情况，特别是如果将实际值作为参数(而不是文字)传递给dbms的话.

If the PK is created using primary key constraint, the indexing should be fine but what about the statistics? Have they been updated lately?

This could also be an optimization flaw so forcing an index scan could correct the situation especially if the actual values are passed to the dbms as paramters, not as literals.

Mika. >
谢谢你的时间.我浪费了一些，抱歉.
我们发现了问题.事实证明，在处理查找表时，我们以100.000的块进行处理以节省内存.这些块未按预期顺序排序，但分散了.我们处理的块越多，这种碎片就越严重.因此，这就是为什么增加数据获取时间的原因.
但是我学会了使用参数代替文字. :)
前面提到，我尝试使用存储过程进行调用，直接在SqlDBCommand.CommandText中设置查询更为有效.有什么原因吗?

问候托马斯

Hi Mika.

Thank''s for your time. I have wasted some of it, sorry.
We have found the problem. It turns out that while processing the lookup table, we process it in chunks of 100.000 to conserve memory. These chunks were not sorted sequential as expected but were fragmented. This fragmentation was getting worse the more chunks we processed. So this is why the increase of time in data fetching.

But I learned to use parameters instead of literals. :)
I mentioned earlier that I tried to use a stored procedure for the calls, setting the query directly in SqlDBCommand.CommandText is more efficient. Any reason for that?

Regards Thomas

这篇关于MS SQL大表PK查找性能提升问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！