StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy is inserting into and joining #temp tables faster?
text
Body
copied!<p>I have a query that looks like </p> <pre><code>SELECT P.Column1, P.Column2, P.Column3, ... ( SELECT A.ColumnX, A.ColumnY, ... FROM dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A WHERE A.Key = P.Key FOR XML AUTO, TYPE ), ( SELECT B.ColumnX, B.ColumnY, ... FROM dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B WHERE B.Key = P.Key FOR XML AUTO, TYPE ) FROM ( <joined tables here> ) AS P FOR XML AUTO,ROOT('ROOT') </code></pre> <p>P has ~ 5000 rows A and B ~ 4000 rows each</p> <p>This query has a runtime performance of ~10+ minutes.</p> <p>Changing it to this however:</p> <pre><code>SELECT P.Column1, P.Column2, P.Column3, ... INTO #P SELECT A.ColumnX, A.ColumnY, ... INTO #A FROM dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A SELECT B.ColumnX, B.ColumnY, ... INTO #B FROM dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B SELECT P.Column1, P.Column2, P.Column3, ... ( SELECT A.ColumnX, A.ColumnY, ... FROM #A AS A WHERE A.Key = P.Key FOR XML AUTO, TYPE ), ( SELECT B.ColumnX, B.ColumnY, ... FROM #B AS B WHERE B.Key = P.Key FOR XML AUTO, TYPE ) FROM #P AS P FOR XML AUTO,ROOT('ROOT') </code></pre> <p>Has a performance of ~4 seconds.</p> <p>This makes not a lot of sense, as it would seem the cost to insert into a temp table and then do the join should be higher by default. My inclination is that SQL is doing the wrong type of "join" with the subquery, but maybe I've missed it, there's no way to specify the join type to use with correlated subqueries. </p> <p>Is there a way to achieve this without using #temp tables/@table variables via indexes and/or hints?</p> <p>EDIT: Note that dbo.TableReturningFunc1 and dbo.TableReturningFunc2 are inline TVF's, not multi-statement, or they are "parameterized" view statements.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload