This question is to expand my knowledge on how the query optimizer of SQL Server works. I like to know how stuff works behind the scenes so I can better optimist it/debug it.
I have the following query to perform a search on my database:
declare @tags table (tag_id int)
insert into @tags (tag_id) values ('5'),('19')--,('25')
declare @tagslength int
select @tagslength = sum(tbl.cnt) from (select count(ptg_tp.exitcontrol_exitmeta_page_tag_type_id) as cnt from exitcontrol_exitmeta_page_tags as ptg
left join exitcontrol_exitmeta_page_tag_types as ptg_tp on ptg.tag_type_id = ptg_tp.exitcontrol_exitmeta_page_tag_type_id
where ptg.exitcontrol_exitmeta_page_tag_id in (select tag_id from @tags)
GROUP BY ptg_tp.exitcontrol_exitmeta_page_tag_type_id) as tbl ;
declare @list table (content nvarchar(50) collate SQL_Latin1_General_CP1253_CI_AI)
insert into @list (content) values ('las'),('vegas')
declare @listlength int
select @listlength = count(content) from @list
SELECT
content.[richpage_id] as richpage_id
,content.[hotel_id] as hotel_id
,sum(article.[word_count]) as word_count
,sum(article.[word_weight_in_page]) as word_weight_in_page
,[ExitProducts].[dbo].GROUP_CONCAT(distinct word.word) as wordsmatched
,[ExitProducts].[dbo].GROUP_CONCAT(distinct language.iso3) as iso
,count(distinct ptg.exitcontrol_exitmeta_page_tag_id)
,'['+[ExitProducts].[dbo].GROUP_CONCAT(distinct
'{ "name":"'+ptg.name+'",
"id":"'+cast(ptg.exitcontrol_exitmeta_page_tag_id as nvarchar(50)) +'",
"tag_type":"'+(case when ptg_tp.name is null then '' else ptg_tp.name end)+'",
"tag_sortorder":"'+cast((case when ptg.sort_order is null then '0' else ptg.sort_order end) as nvarchar(9))+'",
"tag_type_sortorder":"'+cast((case when ptg_tp.sort_order is null then '0' else ptg_tp.sort_order end) as nvarchar(9))+'",
"tag_language":"'+ptg_cntry.iso2+'",
"tag_type_language":"'+ptg_tp_cntry.iso2+'"}')+']' as tags
FROM [ExitProducts].[dbo].[exitcontrol_exitmeta_indexed_article_words] as article
left join [ExitProducts].dbo.exitcontrol_exitmeta_indexed_words as word on article.indexed_word_id = word.indexed_word_id
right join @list as lst on word.word = lst.content
left join [ExitProducts].[dbo].[exitcontrol_exitmeta_indexed_articles] as content on article.indexed_article_id = content.indexed_article_id
right join [ExitProducts].[dbo].[exitcontrol_countrymanager_countries] as language on word.language_id = language.id
-- get page tags from hotels. Incidentally be able to get all info from hotel relations \o/
left join hotels as ht on content.hotel_id = ht.id
left JOIN hotels_translations as tr on ht.id = tr.hotel_id
left JOIN exitcontrol_exitmeta_internal_search_result_containers src on tr.search_result_display_id = src.internal_search_result_container_id
left join block_page as richpage on content.richpage_id = richpage.block_page_id
left join exitcontrol_exitmeta_internal_search_result_containers rp_src on richpage.search_result_display_id = rp_src.internal_search_result_container_id
left JOIN exitcontrol_exitmeta_internal_search_result_container_to_page_tag as src_ptg_lnk on (src.internal_search_result_container_id = src_ptg_lnk.internal_search_result_container_id OR rp_src.internal_search_result_container_id = src_ptg_lnk.internal_search_result_container_id )
left join exitcontrol_exitmeta_page_tags as ptg on src_ptg_lnk.page_tag_id = ptg.exitcontrol_exitmeta_page_tag_id
left join exitcontrol_countrymanager_countries as ptg_cntry on ptg.language_id = ptg_cntry.id
left join exitcontrol_exitmeta_page_tag_types as ptg_tp on ptg.tag_type_id = ptg_tp.exitcontrol_exitmeta_page_tag_type_id
left join exitcontrol_countrymanager_countries as ptg_tp_cntry on ptg_tp.language_id = ptg_tp_cntry.id
right join @tags as tag on ptg.exitcontrol_exitmeta_page_tag_id = tag.tag_id
where language.iso2 = 'NL' and
article.[indexed_article_id] is not null
and ptg.exitcontrol_exitmeta_page_tag_id in (select tag_id from @tags)
group by content.indexed_article_id,content.richpage_id,content.hotel_id
having count(distinct word.word) = @listlength
and count(distinct ptg.exitcontrol_exitmeta_page_tag_id) = @tagslength
order by word_weight_in_page desc, word_count desc;
And if I look at the Execution plan it's all thin lines and it executes very well. My question is more about the query optimizer.
In this I have the first tag_type occurrence query. This is to determine how often a unique tag type occurs
select @tagslength = sum(tbl.cnt) from (select count(ptg_tp.exitcontrol_exitmeta_page_tag_type_id) as cnt from exitcontrol_exitmeta_page_tags as ptg
left join exitcontrol_exitmeta_page_tag_types as ptg_tp on ptg.tag_type_id = ptg_tp.exitcontrol_exitmeta_page_tag_type_id
where ptg.exitcontrol_exitmeta_page_tag_id in (select tag_id from @tags)
GROUP BY ptg_tp.exitcontrol_exitmeta_page_tag_type_id) as tbl ;
Now in this query all the fields are being joined that will get joined later on in the query into the main result set.
My Question is
Does the query optimizer recognize that this is the same data-set and reuse it? Or does it re-query the tables and re-compiles it before joining?
For those interested the sqlplan can be downloaded at https://gist.github.com/tschallacka/3c851c3490a414c5f7f1
