StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
16209784
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-04-25T08:22:35.090
FavoriteCount
0
LastActivityDate
2013-04-25T08:22:35.090
LastEditDate
LastEditorUserId
0
OwnerUserId
417194
ParentId
16208900
PostTypeId
2
Score
1
ViewCount
0
LastEditorDisplayName
text
Body
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. 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. 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 <code>int</code> field that holds the integer value of fields marked as having a type of <code>int</code>. 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). The correct answer is: an EAV store should only ever get used to store meta information. By meta, I mean stuff that you'll seldom use a where clause on, let alone a very selective one. 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. 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 <code>name</code> field in table B, and you'll get appropriately typed data as a bonus.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POOptimizing search for values in columns in SQL Server
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USDenis de Bernardy
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POOptimizing search for values in columns in SQL Server
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.