SQL Server大型表查询优化

Question

我目前正在尝试执行一些有关许可的SAP分析。我想通过他们所拥有的角色将用户链接到事务，并根据他们的事务获得所需的许可证。此分析在多个SAP服务器上执行，每个服务器包含多个SAP实例。在这一分析中，我遇到了大表和查询优化，因为查询永远不会结束(运行超过10个小时(尽管我有128 GB的RAM和较大的CPU性能)。

下面是有关我基于以下查询的表的一些详细信息：

1. SAP_LICENSING.DBO.calc_2_USER_ROLES包含100万行，由以下列定义: DTT_ID ( Server名称)、MANDT (SAP )、UNAME (用户名)、ASSIGNED_ROLE (指定给他的角色，例如: accoutant)。此表存储分配给用户的角色。
2. SAP_LICENSING.DBO.raw_AGR_1251包含200万行，由以下列定义: DTT_ID ( Server名称)、MANDT ( start)、AGR_NAME (角色)、低(事务范围开始)、高(事务范围结束)
3. SAP_LICENSING.DBO.param_LICENSING_RULES包含140 K行，并由以下列定义: TRANSAC (transaction)、LICENSE (相关事务所需的许可证)

我已经在每个表上设置了聚集索引：

1. SAP_LICENSING.DBO.calc_2_USER_ROLES: DTT_ID、MANDT、UNAME、ASSIGNED_ROLE索引
2. SAP_LICENSING.DBO.raw_AGR_1251: DTT_ID，MANDT，AGR_NAME，低，高索引
3. SAP_LICENSING.DBO.param_LICENSING_RULES: TRANSAC索引

以下是每个子查询的select响应时间：

1. 有关UR别名的子查询:30秒
2. 关于T别名的子查询:1分30秒
3. 有关LR别名的子查询:5秒

请注意以下几点：

• 当运行此查询时，我看到CPU的最大使用量为10%，RAM的使用率为30%。
• 该查询在SAP服务器的子集上快速工作(对1个SAP服务器名称进行筛选)
• 第二个子查询用于将SAP事务范围转换为可搜索的事务，并与中间关键字的联接兼容。
• 使用左联接是因为我希望保留不匹配的事务以进行进一步分析。

下面是我正在苦苦挣扎的相关查询：

INSERT INTO SAP_LICENSING.DBO.calc_3_USER_ROLES_TRANSACTIONS
SELECT UR.DTT_ID,UR.MANDT,UR.UNAME,UR.ASSIGNED_ROLE,
TRANSAC_FROM,TRANSAC_TO,SAP_TRANSAC_RANGE,LR.TRANSAC,LR.LICENSE
--Get User Role Assignments
FROM 
    (
    SELECT DISTINCT UR.DTT_ID,UR.MANDT,UR.UNAME,UR.ASSIGNED_ROLE
    FROM SAP_LICENSING.DBO.calc_2_USER_ROLES UR
    )UR
--Join on transactions and Convert SAP Transaction ranges into SQL searchable ranges
JOIN 
    (
    SELECT T.DTT_ID,T.MANDT,T.AGR_NAME,T.AUTH,
    replace(replace(T.LOW,'*',' '),'$',' ') AS TRANSAC_FROM,
    replace(replace(coalesce(T.HIGH,T.LOW),'*',left('ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ',41-len(coalesce(T.HIGH,T.LOW)))),'$','Z') AS TRANSAC_TO,
    CONCAT(T.LOW,' TO ', isnull(T.HIGH,'')) as SAP_TRANSAC_RANGE
    FROM SAP_LICENSING.DBO.raw_AGR_1251 T
    )T
ON UR.ASSIGNED_ROLE=T.AGR_NAME and UR.MANDT=T.MANDT and UR.DTT_ID=T.DTT_ID
--Join on transactions requiring a license
LEFT JOIN SAP_LICENSING.DBO.param_LICENSING_RULES LR
ON LR.TRANSAC between T.TRANSAC_FROM and T.TRANSAC_TO;

非常感谢你的帮助。最亲切的尊重

Answer 1

• (一)是否可以删除不同的？
• (二)批量插入
• (三)使用覆盖指数/过滤指数
• (四)注释、插入和使用select操作
• 如果没有使用索引，那么请使用提示
• 你忘了用(nolock)

Answer 2

在最大的表上执行一些代价高昂的字符串转换，并在JOIN条件下使用结果。这肯定是没有效率的。默认情况下，Server会将最大的表放在最后，因此T将通过这些转换的字符串将JOIN编辑为UR和LR。

一种方法是首先创建一个临时表T，另一种方法是在SAP_LICENSING.DBO.raw_AGR_1251上创建一个索引视图，其中包含TRANSAC_FROM和TRANSAC_TO计算字段，作为聚集索引的一部分。

如果将T移到FROM子句的前面，并将表的其余部分移至JOIN，那么您也可能会对优化器感到幸运。也值得一试OPTION (FORCE ORDER)。

张贴当前的执行计划也将有助于提供适当的建议。