問題描述
我對 SQL Server 相當(dāng)陌生，我遇到了這個(gè)以相當(dāng)笨拙的方式實(shí)現(xiàn)的非常大的表的問題.這是設(shè)計(jì)的概要圖片
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，名稱 = 帳號和值 =[實(shí)際帳號]"與所需的 itemID.現(xiàn)在表 B 非常大，以至于使用視圖查詢特定交易的帳號需要永遠(yuǎn).
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 中，但由于不斷變化的業(yè)務(wù)需求，添加更多列導(dǎo)致團(tuán)隊(duì)以這種方式創(chuàng)建結(jié)構(gòu)，以便添加新字段不需要向表 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.
優(yōu)化此表的最佳方法是什么.
What is the best way to optimize this table.
推薦答案
您的同事創(chuàng)建的是實(shí)體屬性值 (EAV) 存儲.是不是壞東西我就不說了.
What your colleagues created are an Entity Attribute Value (EAV) store. I'll leave aside whether those are evil things or not.
簡短的回答是:您無法優(yōu)化查詢.
The short answer is: you cannot optimize your queries.
更長的答案是:您可以在表 B 的 AV 中加入一個(gè)(索引)類型字段，以提高效率.
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.
根據(jù)您在其中存儲的內(nèi)容，一個(gè)稍微相關(guān)的選項(xiàng)可能是使用觸發(fā)器在其中維護(hù)額外的類型字段，并為后者編制索引.例如，觸發(fā)器可以維護(hù)一個(gè) int 字段，該字段保存標(biāo)記為具有 int 類型的字段的整數(shù)值.
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.
也就是說，不要指望這些解決方案會(huì)帶來巨大的性能提升，因?yàn)槟匀粫?huì)進(jìn)行大量不必要的連接.請記住，這兩種技巧都會(huì)增加開銷(額外的磁盤空間 + 與索引維護(hù)相關(guān)的時(shí)間).
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 存儲應(yīng)該只用于存儲元信息.
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.
換句話說，確定經(jīng)常查詢的關(guān)鍵字段，是否為 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.
在此期間，請考慮移動(dòng)表 A 中大多數(shù)/所有行中存在的內(nèi)容:您移動(dòng)的每個(gè)字段都將節(jié)省空間，因?yàn)椴辉傩枰?code>name表 B 中的字段，您將獲得適當(dāng)類型的數(shù)據(jù)作為獎(jiǎng)勵(lì).
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.
這篇關(guān)于優(yōu)化搜索 SQL Server 中列中的值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！