StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
4763152
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
6
CommunityOwnedDate
CreationDate
2011-01-21T19:52:29.800
FavoriteCount
0
LastActivityDate
2011-01-21T19:52:29.800
LastEditDate
LastEditorUserId
0
OwnerUserId
105929
ParentId
4760841
PostTypeId
2
Score
2
ViewCount
0
LastEditorDisplayName
text
Body
As with almost all SQL performance questions, the answer is not the query, the answer is the data schema. What indexes you have, that is what drives the performance of your queries. Usually many-to-many relations require two complementary indexes, one as <code>(ID1, ID2)</code> and the other as <code>(ID2, ID1)</code>. One of them is clustered, it doesn't really matter which one. So lets create a test DB (100k articles, 1K tags, 1-10 tags per article): <pre><code>:setvar dbname testdb :setvar articles 1000000 :setvar tags 1000 :setvar articletags 10 :on error exit set xact_abort on; go use master; go if db_id('$(dbname)') is not null begin alter database [$(dbname)] set single_user with rollback immediate; drop database [$(dbname)]; end go create database [$(dbname)]; go use [$(dbname)]; go create TABLE Articles ( ArtID INT not null identity(1,1), name varchar(100) not null, filler char(500) not null default replicate('X', 500), constraint pk_Articles primary key clustered (ArtID)); go create table Tags ( TagID INT not null identity(1,1), TagText VARCHAR(10) not null, constraint pk_Tags primary key clustered (TagID), constraint unq_Tags_Text unique (TagText)); go create TABLE ArticleTags ( ArtID INT not null, TagID INT not null, constraint fk_Articles foreign key (ArtID) references Articles (ArtID), constraint fk_Tags foreign key (TagID) references Tags (TagID), constraint pk_ArticleTags primary key clustered (ArtID, TagID)); go create nonclustered index ndxArticleTags_TagID on ArticleTags (TagID, ArtID); go -- populate articles set nocount on; declare @i int =0, @name varchar(100); begin transaction while @i < $(articles) begin set @name = 'Name ' + cast(@i as varchar(10)); insert into Articles (name) values (@name); set @i += 1; if @i %1000 = 0 begin commit; raiserror (N'Inserted %d articles', 0, 1, @i); begin transaction; end end commit go -- populate tags set nocount on; declare @i int =0, @text varchar(100); begin transaction while @i < $(tags) begin set @text = 'Tag ' + cast(@i as varchar(10)); insert into Tags (TagText) values (@text); set @i += 1; if @i %1000 = 0 begin commit; raiserror (N'Inserted %d tags', 0, 1, @i); begin transaction; end end commit go -- populate article-tags set nocount on; declare @i int =0, @a int = 1, @cnt int, @tag int; set @cnt = rand() * $(articletags) + 1; set @tag = rand() * $(tags) + 1; begin transaction while @a < $(articles) begin insert into ArticleTags (ArtID, TagID) values (@a, @tag); set @cnt -= 1; set @tag += rand()*10+1; if $(tags)<=@tag begin set @tag = 1; end if @cnt = 0 begin set @cnt = rand() * $(articletags) + 1; set @tag = rand() * $(tags) + 1; set @a += 1; end set @i += 1; if @i %1000 = 0 begin commit; raiserror (N'Inserted %d article-tags', 0, 1, @i); begin transaction; end end commit raiserror (N'Final: %d article-tags', 0, 1, @i); go </code></pre> Now lets compare the two queries: <pre><code>set statistics io on; set statistics time on; select a.ArtID from Articles a where exists ( select * from ArticleTags at join Tags t on at.TagID = t.TagID where at.ArtID = a.ArtID and t.TagText in ('Tag 10', 'Tag 12')); SELECT a.ArtID FROM Articles a INNER JOIN ArticleTags at ON a.ArtID = at.ArtID INNER JOIN Tags t ON at.TagID = t.TagID WHERE t.TagText IN ('Tag 10', 'Tag 12') GROUP BY a.ArtID </code></pre> Result: <pre><code>Table 'Articles'. Scan count 0, logical reads 3561, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'ArticleTags'. Scan count 2, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Tags'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Articles'. Scan count 0, logical reads 3561, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'ArticleTags'. Scan count 2, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Tags'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. </code></pre> Surprise! (well, not really). They're IDENTICAL. In fact, they have exactly the same execution plan.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POMost Efficient Many-t-Many Query
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USRemus Rusanu
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POMost Efficient Many-t-Many Query
 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
 VTAcceptedByOriginator
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

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.