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