首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >在sql中连接行的最佳方法

在sql中连接行的最佳方法
EN

Stack Overflow用户
提问于 2018-08-14 08:23:02
回答 2查看 900关注 0票数 1

目前,我正在使用下面的代码,以获得总结报告的固定资产审计。起初,这是好的,三年后,我注意到系统现在是非常缓慢的。我重新查看了代码,发现了为什么系统运行缓慢的代码。当我删除子字符串部分时,系统运行得更快。这附近有工作吗?

代码语言:javascript
运行
复制
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]
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-08-30 07:31:25

一种方法是添加一个用户定义聚合函数,作为missing版本中缺少的阿格函数的替代方法。然后用函数替换corrolated子查询。

这类函数的示例可以找到这里这里

另一种不涉及DB管理的方法是在用于与FOR XML连接子查询的字段上使用带有复合索引的临时表。

db<>fiddle https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=7d715c0c4cd4f0c018e722f7cf23d477的测试

代码语言:javascript
运行
复制
-- 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];

此外,可能值得检查视图中的查询是否可以优化。或者,在表上添加某些索引(在视图中使用)可以提高视图本身的性能。

票数 1
EN

Stack Overflow用户

发布于 2018-08-30 09:10:20

连接的最佳方法是:

代码语言:javascript
运行
复制
declare @str nvarchar(max) = space(0);
select @str += [column] from [yourTable];
select @str;

但是,你的情况有点不同。在您的情况下,连接做得相当好,性能下降可能是由于视图结构造成的。如果无法看到视图结构,可以通过使用主视图记录集作为CTE来最小化性能问题,然后在CTE上执行resuorce-代价高昂的contatenate而不是view:

代码语言:javascript
运行
复制
;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];
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/51836901

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档