sql - 如何关闭按功能分组排序，因为它占用大量资源并且在我的情况下不需要

Question

我有一个 SQL 查询，我正在对其进行“分组”，但没有必要进行排序。

我要加入 2 张桌子，

1. 角色管理器
2. aclMstr
3. 角色AclMap

'roleMstr', 'aclMstr' 是多对多 & 存储在 'roleAclMap'

我正在尝试获取分配给该角色的 acl 并且处于活动状态（由于软删除）并将它们分组以查找分配的和总计

当我签入 SQL Server Profiler 时，排序占用 68%，索引扫描占用 32%

BEGIN
DECLARE @RoleCode VARCHAR(20);
SET @RoleCode = 'CLN';
    SELECT am.aclGroup, am.subAclGroup, SUM(CASE ram.roleCode WHEN @RoleCode THEN 1 ELSE 0 END) AS assignedChild, COUNT(*) AS totalChild
    FROM aclMstr am 
    LEFT JOIN roleAclMap ram
        ON am.acl_code = ram.acl_code AND ram.roleCode = @RoleCode
    WHERE am.isActive = 1
    group by am.aclGroup, am.subAclGroup;
END

查询计划执行器文本link1 , link2

我正在计算根据“角色代码”将多少 acl 分配给“acl 组”

我也得到了排序的列。

roleMstr contains roleCode 
 CONSTRAINT [pk_rolemstr_rolecode] PRIMARY KEY CLUSTERED 
(
    [roleCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

aclMstr contains aclCode, isActive, aclGroup, subAclGroup 
 CONSTRAINT [pk_aclmstr_aclcode] PRIMARY KEY CLUSTERED 
(
    [aclCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[roleAclMap](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [roleCode] [varchar](20) NOT NULL,
    [aclCode] [varchar](50) NOT NULL,
 CONSTRAINT [pk_roleaclmap_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [uk_roleaclmap_rolecode_aclcode] UNIQUE NONCLUSTERED 
(
    [roleCode] ASC,
    [aclCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[roleAclMap]  WITH CHECK ADD  CONSTRAINT [fk_roleaclmap_aclmstr_aclcode] FOREIGN KEY([aclCode])
REFERENCES [dbo].[aclMstr] ([aclCode])
GO

ALTER TABLE [dbo].[roleAclMap] CHECK CONSTRAINT [fk_roleaclmap_aclmstr_aclcode]
GO

ALTER TABLE [dbo].[roleAclMap]  WITH CHECK ADD  CONSTRAINT [fk_roleaclmap_rolemstr_rolecode] FOREIGN KEY([roleCode])
REFERENCES [dbo].[roleMstr] ([roleCode])
GO

ALTER TABLE [dbo].[roleAclMap] CHECK CONSTRAINT [fk_roleaclmap_rolemstr_rolecode]
GO

如果可以通过查询以某种方式关闭排序。查询将在一半时间内执行。

标签： sqlsql-server