USE CTE slow in view than use temp table in sp

Question

question

SSGC asked Oct 31, '18

USE CTE slow in view than use temp table in sp

I have question for SQL professional for help. I have a piece query run slow in a view(25s), I use temp table can improve the performance to 2s. Since temp table cannot use in view, I try to use CTE. But for some reason it slow again. Here is the query in view:

	SELECT mc.CustID,
		mc.ScustID,
		STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(code)) AS 'data()'
				FROM (SELECT code
					FROM [sqlmc].mc1015.dbo.texture am WITH (NOLOCK)
					INNER JOIN [sqlmc].mc1015.dbo.texture_flags af WITH (NOLOCK)
						ON af.mt_id = am.mt_id AND am.ma_id = 3
					WHERE af.me_id = ep.me_id) temp 
	FOR XML PATH('')),' #!',','), 1, 2, '')	AS SCodes
	FROM c_mid_vw mc WITH (NOLOCK)
	INNER JOIN [sqlmc].mc1015.dbo.user pm WITH (NOLOCK)
		ON pm.Cust_id = mc.mCust_id
	INNER JOIN [sqlmc].mc1015.dbo.event_users ep WITH (NOLOCK)
		ON pm.pt_id = ep.pt_id
		AND ep.ptt_id IN (1, 8, 9)

sqlmc is a link server; c_mid_vw is on local server. I modified it to use temp table but not in view:

SELECT me_id,Cust_id
	INTO #mc1015  
	FROM [sqlmc].mc1015.dbo.user pm WITH (NOLOCK)
	INNER JOIN [sqlmc].mc1015.dbo.event_users ep WITH (NOLOCK)
		ON pm.pt_id = ep.pt_id
		AND ep.ptt_id IN (1, 8, 9)


SELECT DISTINCT code,af.me_id
INTO #md1015
					FROM [sqlmc].mc1015.dbo.texture am WITH (NOLOCK)
					INNER JOIN [sqlmc].mc1015.dbo.texture_flags af WITH (NOLOCK)
						ON af.mt_id = am.mt_id AND am.ma_id = 3
					JOIN #mc1015 a ON a.me_id=af.me_id


	SELECT mc.CustID,
		mc.ScustID,
		STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(code)) AS 'data()'
				FROM (SELECT code FROM #md1015 b WHERE b.me_id=a.me_id ) temp 
	FOR XML PATH('')),' #!',','), 1, 2, '')	AS SCodes
	FROM c_mid_vw mc WITH (NOLOCK)
	INNER JOIN #mc1015 a ON a.Cust_id = mc.mCust_id

it give good performance. when I use cte for same concept since we can use CTE in view, it slow again(25s):

;WITH  mc1015(Cust_id,me_id)
AS(
 	SELECT TOP 100 PERCENT Cust_id,me_id
	FROM [sqlmc].mc1015.dbo.user pm WITH (NOLOCK)
	INNER JOIN [sqlmc].mc1015.dbo.event_users ep WITH (NOLOCK)
		ON pm.pt_id = ep.pt_id
		AND ep.ptt_id IN (1, 8, 9)
	ORDER BY me_id
),
md1015(code,me_id)
AS
(
SELECT 
		DISTINCT code,af.me_id
					FROM [sqlmc].mc1015.dbo.texture am WITH (NOLOCK)
					INNER JOIN [sqlmc].mc1015.dbo.texture_flags af WITH (NOLOCK)
						ON af.mt_id = am.mt_id AND am.ma_id = 3
					JOIN mc1015 a ON a.me_id=af.me_id
)
	SELECT mc.CustID,
		mc.ScustID,
		STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(code)) AS 'data()'
				FROM (SELECT code FROM md1015 b WHERE b.me_id=a.me_id ) temp 
	FOR XML PATH('')),' #!',','), 1, 2, '')	AS SCodes
	FROM c_mid_vw mc WITH (NOLOCK)
	INNER JOIN mc1015 a ON a.Cust_id = mc.mCust_id

view cte linked server temp

7

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SSGC commented · Oct 31, 2018 at 07:09 PM

For some reason original view be used in app. I try to do the performance tuning. I am very appriciate if someone can provide help

0 ·

Kev Riley ♦♦ commented · Nov 01, 2018 at 07:08 AM

It might help if you could post the execution plans for each scenario - we could then see where the query is running slow. It could be that you are getting better estimates from the #temp tables as this will have the statement recompiled after being populated.

0 ·

SSGC commented · Nov 01, 2018 at 01:59 PM

Thank you Kev Riley, I attached execution plan png files. For some reason I cannot attached sqlplan files here. it said "This attachment is not permitted because the file type is invalid." I created test code, but i cannot attached here too. It is too long to post code too.

0 ·

cte-plan.png (36.6 KiB)

temptable-plan.png (80.2 KiB)

SSGC commented · Nov 08, 2018 at 07:57 PM

Can anyone can help me for this?

0 ·

Kev Riley ♦♦ commented · Nov 09, 2018 at 10:48 AM

if you rename the sqlplan files as .txt you should be able to upload, otherwise maybe you could use https://www.brentozar.com/pastetheplan/

0 ·

Show more comments

Answer 1 · 2018-11-12T14:33:38Z

Kev Riley answered Nov 12, '18

As I suspected, the performance gain comes from the fact that the materialized temp tables have statisitics, and so a different execution plan is chosen, rather than the inline approach of the view.

One way around this is to create a multi-statement table valued function, which has the same effect:

create function YourFunction_MSTVF()
returns @results table (me_id int ,Scodes varchar(max))
as begin

declare @tabvar1 table (me_id int, code varchar(10))
insert into @tabvar1
select distinct me_id, am.code
FROM [sqlmc].mc1015.dbo.texture am WITH (NOLOCK)
INNER JOIN [sqlmc].mc1015.dbo.texture_flag af WITH (NOLOCK)
	ON af.mt_id = am.mt_id 

insert into @results 
select
	me_id,
	STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(temp1.code)) AS 'data()'
				FROM @tabvar1 temp1 
				where temp1.me_id = temp2.me_id
	FOR XML PATH('')),' #!',','), 1, 2, '')	AS SCodes
from @tabvar1 temp2;
return
end
go

which you can then reference in your view

SELECT mc.CustID,
	mc.ScustID,
	YourFunction_MSTVF.SCodes
FROM c_mid_vw mc WITH (NOLOCK)
INNER JOIN [sqlmc].mc1015.dbo.users pm WITH (NOLOCK)
	ON pm.Cust_id = mc.mCust_id
INNER JOIN [sqlmc].mc1015.dbo.event_users ep WITH (NOLOCK)
	ON pm.pt_id = ep.pt_id AND ep.ptt_id IN (1, 8, 9)
left join YourFunction_MSTVF() on ep.me_id = YourFunction_MSTVF.me_id

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SSGC commented · Nov 14, 2018 at 01:26 PM

Thank you Kev. It is a good solution. In this case can we let CTE choose the good execution plan?

0 ·

question