问题描述

我对 SQL Server 相当陌生，我遇到了这个以相当笨拙的方式实现的非常大的表的问题.这是设计的概要图片

I am fairly new to SQL Server and i am having this issue with this very big table that was implemented in a rather awkward way. Here is summary picture of what the design looks like

表 A

itemID uniqueidentifier PK
name   vchar(32)
datecreated datetime
dateprocessed datetime
datereviewed datetime
approvalstatus int
workflowstatus int

表 B

fieldID uniqueidentifier PK
itemID  uniqueidentifier FK(referencing table A's itemID)
name    vchar(32)
value   vchar(32)

在表 A 中，我们有交易记录，其中一些字段被填充到表 B.就像说 AccountNumber 表 A 中的交易被填充到表 B，名称 = 帐号和值 =[实际帐号]"与所需的 itemID.现在表 B 非常大，以至于使用视图查询特定交易的帐号需要永远.

In table A we have record of transactions with some of the fields being populated to Table B. Like say AccountNumber for a transactions in table A is populated to table B with name = accountnumber and value = '[actual account number]' with the required itemID. Now table B is extremely large so that querying for account number for a specific transaction using a view is taking forever.

请注意，所有这些名称列以前都在表 A 中，但由于不断变化的业务需求，添加更多列导致团队以这种方式创建结构，以便添加新字段不需要向表 A 添加新列.

Mind you all this name columns were previously in table A but due to always changing business needs to add more columns led the team to create the structure this way so that adding new fields would not require adding new columns to table A.

优化此表的最佳方法是什么.

What is the best way to optimize this table.

推荐答案

您的同事创建的是实体属性值 (EAV) 存储.是不是坏东西我就不说了.

What your colleagues created are an Entity Attribute Value (EAV) store. I'll leave aside whether those are evil things or not.

简短的回答是:您无法优化查询.

The short answer is: you cannot optimize your queries.

更长的答案是:您可以在表 B 的 AV 中加入一个(索引)类型字段，以提高效率.

The longer answer is: you might be able to toss in an (indexed) type field in the AV your table B, to make things slightly more efficient.

根据您在其中存储的内容，一个稍微相关的选项可能是使用触发器在其中维护额外的类型字段，并为后者编制索引.例如，触发器可以维护一个 int 字段，该字段保存标记为具有 int 类型的字段的整数值.

Depending on what you store in it, a slightly related option might be to maintain extra typed fields therein using triggers, and indexing the latter as well. For instance, a trigger could maintain an int field that holds the integer value of fields marked as having a type of int.

也就是说，不要指望这些解决方案会带来巨大的性能提升，因为您仍然会进行大量不必要的连接.请记住，这两种技巧都会增加开销(额外的磁盘空间 + 与索引维护相关的时间).

That said, don't count on a great performance boost with those solutions, since you'll still be doing lots of needless joins. And keep in mind that both of these tricks add overhead (extra disk space + time related to index maintenance).

正确答案是:EAV 存储应该只用于存储元信息.

The correct answer is: an EAV store should only ever get used to store meta information.

元，我的意思是你很少使用 where 子句的东西，更不用说非常有选择性的了.

By meta, I mean stuff that you'll seldom use a where clause on, let alone a very selective one.

换句话说，确定经常查询的关键字段，是否为 where 子句、join 子句、order by 子句等.将它们移回表 A，并正确索引它们.

In other words, identify the key fields that are frequently queried against, whether for where clauses, join clauses, order by clauses, etc. Shift them back into table A, and index them properly.

在此期间，请考虑移动表 A 中大多数/所有行中存在的内容:您移动的每个字段都将节省空间，因为不再需要该name表 B 中的字段，您将获得适当类型的数据作为奖励.

While you're at it, consider moving stuff that is present in most/all rows from table A as well: each field that you move will save you space due to no longer needing that name field in table B, and you'll get appropriately typed data as a bonus.

这篇关于优化搜索 SQL Server 中列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！