目前,我正在使用下面的代码,以获得总结报告的固定资产审计。起初,这是好的,三年后,我注意到系统现在是非常缓慢的。我重新查看了代码,发现了为什么系统运行缓慢的代码。当我删除子字符串部分时,系统运行得更快。这附近有工作吗?
SELECT
ENTITY
, [ASSET NUMBER]
, [YEAR AUDITED]
, SUM(COUNT) AS AUDITED
, SUBSTRING
(
(
SELECT
', ' + [SCANNED BY].USERNAME
FROM dbo.vwAUDITED as [SCANNED BY]
WHERE vwAUDITED.[ASSET NUMBER] = [SCANNED BY].[ASSET NUMBER]
AND vwAUDITED.ENTITY = [SCANNED BY].ENTITY
AND vwAUDITED.[YEAR AUDITED] = [SCANNED BY].[YEAR AUDITED]
ORDER BY [SCANNED BY].[ASSET NUMBER] FOR XML PATH('')
), 2, 1000
) AS [SCANNED BY]
, MAX(DATE) AS [COMPLETION DATE]
FROM dbo.vwAUDITED
GROUP BY ENTITY
, [ASSET NUMBER]
, [YEAR AUDITED]
发布于 2018-08-30 07:31:25
一种方法是添加一个用户定义聚合函数,作为missing版本中缺少的阿格函数的替代方法。然后用函数替换corrolated子查询。
另一种不涉及DB管理的方法是在用于与FOR XML
连接子查询的字段上使用带有复合索引的临时表。
db<>fiddle https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=7d715c0c4cd4f0c018e722f7cf23d477的测试
-- Just assuming the datatypes here, so change them to the correct types.
IF OBJECT_ID('tempdb..#tmpAUDITED') IS NOT NULL DROP TABLE #tmpAUDITED;
CREATE TABLE #tmpAUDITED
(
[ENTITY] INT NOT NULL,
[ASSET NUMBER] INT NOT NULL,
[YEAR AUDITED] INT NOT NULL,
[COUNT] INT,
[DATE] DATE,
[USERNAME] VARCHAR(100),
INDEX idx_1 NONCLUSTERED ([ENTITY], [ASSET NUMBER], [YEAR AUDITED])
);
INSERT INTO #tmpAUDITED (
[ENTITY], [ASSET NUMBER],[YEAR AUDITED], [COUNT], [DATE], [USERNAME]
)
SELECT
[ENTITY], [ASSET NUMBER],[YEAR AUDITED], [COUNT], [DATE], [USERNAME]
FROM dbo.vwAUDITED;
-- Now using the temp table instead of the view.
SELECT
[ENTITY],
[ASSET NUMBER],
[YEAR AUDITED],
SUM([COUNT]) AS AUDITED,
SUBSTRING
((
SELECT ', ' + s.[USERNAME]
FROM #tmpAUDITED AS s
WHERE s.[ENTITY] = [AUDITED].[ENTITY]
AND s.[ASSET NUMBER] = [AUDITED].[ASSET NUMBER]
AND s.[YEAR AUDITED] = [AUDITED].[YEAR AUDITED]
-- ORDER BY s.[ENTITY], s.[ASSET NUMBER], s.[YEAR AUDITED], s.[USERNAME]
FOR XML PATH('')
), 2, 1000) AS [SCANNED BY],
MAX([DATE]) AS [COMPLETION DATE]
FROM #tmpAUDITED AS [AUDITED]
GROUP BY [ENTITY], [ASSET NUMBER], [YEAR AUDITED];
此外,可能值得检查视图中的查询是否可以优化。或者,在表上添加某些索引(在视图中使用)可以提高视图本身的性能。
发布于 2018-08-30 09:10:20
连接的最佳方法是:
declare @str nvarchar(max) = space(0);
select @str += [column] from [yourTable];
select @str;
但是,你的情况有点不同。在您的情况下,连接做得相当好,性能下降可能是由于视图结构造成的。如果无法看到视图结构,可以通过使用主视图记录集作为CTE来最小化性能问题,然后在CTE上执行resuorce-代价高昂的contatenate而不是view:
;with [data] as (
select [entity], [asset number], [year audited], [username], [date], [count] from [dbo].[vwAUDITED]
)
select
[entity] = [d].[entity]
,[asset number] = [d].[asset number]
,[year audited] = [d].[year audited]
,[audited] = sum([d].[count])
,[scanned by] = substring((
select
', ' + [username]
from
[dbo].[vwAUDITED]
where
[asset number] = [d].[asset number]
and [entity] = [d].[entity]
and [year audited] = [d].[year audited]
order by
[asset number]
for xml path('')
), 2, 1000)
,[completion date] = max([d].[date])
from
[data] as [d]
group by
[d].[entity]
,[d].[asset number]
,[d].[year audited];
https://stackoverflow.com/questions/51836901
复制相似问题