SQL Server语言 - WITH或Temp表- TempDB问题

Question

我遇到临时数据库问题，正在优化我的查询，希望在TempDB上释放空间。

所以我有以下查询(伪代码)，我想知道示例a是否占用更多/更少/或与示例b相同的空间，或者是否有更好的解决方案。

编辑实际的查询提取了8个以上的字段，但是它们都由表4的Identity ID列映射。

--(@PageSize/@PageIndex come in as parameters from the SP)
WITH data1 AS (
SELECT
t4.[id] AS [id],
t1.name1 AS [name]
FROM TABLE1 t1
INNER JOIN TABLE4 t4 ON t4.[systemId] = t1.[id] and t4.[systemType] = 'Table1'
UNION
SELECT
t4.[id] AS [id],
t2.name1 AS [name]
FROM TABLE2 t2
INNER JOIN TABLE4 t4 ON t4.[systemId] = t2.[id] and t4.[systemType] = 'Table2'
UNION
SELECT
t4.[id] AS [id],
t3.name1 AS [name]
FROM TABLE3 t3
INNER JOIN TABLE4 t4 ON t4.[systemId] = t3.[id] and t4.[systemType] = 'Table3'
)
SELECT id, name
FROM data1
ORDER BY [id]
OFFSET (@PageSize * @PageIndex) ROWS FETCH NEXT @PageSize ROWS ONLY;

例B

--(@PageSize/@PageIndex come in as parameters from the SP
CREATE TABLE #data
(
[id]                  int,
[name]                NVARCHAR(64),
);
INSERT INTO #data ([id], [name])
SELECT
t4.[id],
t1.name1
FROM TABLE1 t1
INNER JOIN TABLE4 t4 ON t4.[systemId] = t1.[id] and t4.[systemType] = 'Table1';
INSERT INTO #data ([id], [name])
SELECT
t4.[id],
t2.name1
FROM TABLE2 t2
INNER JOIN TABLE4 t4 ON t4.[systemId] = t2.[id] and t4.[systemType] = 'Table2';
INSERT INTO #data ([id], [name])
SELECT
t4.[id],
t3.name1
FROM TABLE3 t3
INNER JOIN TABLE4 t4 ON t4.[systemId] = t3.[id] and t4.[systemType] = 'Table3'
SELECT id, name
FROM #data
ORDER BY id
OFFSET (@PageSize * @PageIndex) ROWS FETCH NEXT @PageSize ROWS ONLY;

已经在优化器中查看了查询，但不幸的是，在我们的测试环境中没有监视TempDB的性能。

Answer 1

解决这个问题的方法如下:

create table #t_ids (id int, systemType varchar(30))
create clustered index IX_t_ids on #t_ids (systemType, id)
insert into #t_ids (id, systemType )
select t.id, t.systemtype
from table4 t
where t.systemtype IN ('Table1', 'Table2','Table3')
order by id
OFFSET (@PageSize * @PageIndex) ROWS FETCH NEXT @PageSize ROWS ONLY
select *
from #tid ids
inner join table1 t
ON t.id = ids.id
and systemtype = 'table1'
union
...
order by id

通过这种方式，您可以跳过连接的艰苦工作，并且不需要过早地提取太多数据。我不确定它是否适用于你的查询，因为你没有提供细节，但它可以。