2023年5月11日 15:57:57go评论125阅读模式

英文:

SQL takes too long. How can I optimize this query?

问题

这个查询的问题是速度太慢。我不确定如何优化它。

英文:

The issue with this query is that is too slow:

SELECT
   *
FROM
(
      SELECT
         *
      FROM
(
            SELECT
               *
            FROM
               (
                  SELECT
                     *
                  FROM
                     (
                        SELECT
                           *
                        FROM
                           (
                              SELECT
                                 MIN(UPPER(MSG_USERS.MSU_LOGIN_NAME)) as MSU_LOGIN_NAME_SORT,
                                 MSG_USERS.MSU_USER_ID ID,
                                 MSG_USERS.MSU_LAST_LOGIN_TIME,
                                 MSG_USERS.MSU_CREATED_DATE,
                                 MSG_USERS.MSU_LOGIN_NAME,
                                 MSG_USERS.MSU_UUID,
                                 MSG_USERS.MSU_FIRST_NAME,
                                 MSG_USERS.MSU_LAST_NAME,
                                 MSG_USERS.MSU_LAST_UPDATED,
                                 MSG_USERS.MSU_CUSTOMER_UID
                              FROM
                                 (
                                    MSG_USERS
                                    left join MSG_ACCOUNT_USERS on MAU_USER_ID = MSU_USER_ID
                                    left join MSG_ACCOUNTS on MSA_ACCOUNT_ID = MAU_ACCOUNT_ID
                                    left join MSG_TENANTS on MTE_ACCOUNT_ID = MAU_ACCOUNT_ID
                                 )
                              WHERE
                                 (
                                    MSG_USERS.MSU_ROLE_NAME &lt;&gt; &#39;CUSTOMER_INTEGRATION&#39;
                                    AND MSG_USERS.MSU_ROLE_NAME &lt;&gt; &#39;CUSTOMER_EXTERNAL_INTEGRATION&#39;
                                    AND MSG_USERS.MSU_ROLE_NAME &lt;&gt; &#39;INTEGRATION_SUPERUSER&#39;
                                    AND MSG_USERS.MSU_ROLE_NAME &lt;&gt; &#39;OAUTH2_CLIENT&#39;
                                 )
                              GROUP BY
                                 MSG_USERS.MSU_USER_ID,
                                 MSG_USERS.MSU_LAST_LOGIN_TIME,
                                 MSG_USERS.MSU_CREATED_DATE,
                                 MSG_USERS.MSU_LOGIN_NAME,
                                 MSG_USERS.MSU_UUID,
                                 MSG_USERS.MSU_FIRST_NAME,
                                 MSG_USERS.MSU_LAST_NAME,
                                 MSG_USERS.MSU_LAST_UPDATED,
                                 MSG_USERS.MSU_CUSTOMER_UID
                              ORDER BY
                                 MIN(UPPER(MSG_USERS.MSU_LOGIN_NAME)) ASC
                           )
                        ORDER BY
                           UPPER(MSU_LOGIN_NAME_SORT) ASC,
                           ID ASC
                     )
                  WHERE
                     ROWNUM &lt; 7
               )
            ORDER BY
               UPPER(MSU_LOGIN_NAME_SORT) DESC,
               ID DESC
         )
      WHERE
         ROWNUM &lt; 7
   )
ORDER BY
   UPPER(MSU_LOGIN_NAME_SORT) ASC,
   ID ASC;

I am not sure how to optimize it.

答案1

得分: 3

以下是翻译好的代码部分：

原则上，你执行：
    选择 ...
    从 (((( ...
                                  按顺序
                                     最小(大写(MSG_USERS.MSU_LOGIN_NAME)) 升序
                               )
                            按顺序
                               大写(MSU_LOGIN_NAME_SORT) 升序，
                               ID 升序
                         )
                      在
                         ROWNUM &lt; 7
                   )
                按顺序
                   大写(MSU_LOGIN_NAME_SORT) 降序，
                   ID 降序
             )
          在
             ROWNUM &lt; 7
       )
    按顺序
       大写(MSU_LOGIN_NAME_SORT) 升序，
       ID 升序;

这个疯狂的链条的目的是什么？

你在表 MSG_ACCOUNT_USERS, MSG_ACCOUNTS, MSG_TENANTS 上进行了 LEFT JOIN，但它们在你的查询中并未被使用。那么，为什么你要（左）连接它们？它们没有任何影响，我猜你可以从查询中简单地移除它们，然后你甚至不需要 GROUP BY 子句。

我假设你的查询可以简化成像这样的：

选择
   MSG_USERS.MSU_USER_ID ID,
   MSG_USERS.MSU_LAST_LOGIN_TIME,
   MSG_USERS.MSU_CREATED_DATE,
   MSG_USERS.MSU_LOGIN_NAME,
   MSG_USERS.MSU_UUID,
   MSG_USERS.MSU_FIRST_NAME,
   MSG_USERS.MSU_LAST_NAME,
   MSG_USERS.MSU_LAST_UPDATED,
   MSG_USERS.MSU_CUSTOMER_UID
从 MSG_USERS
在 MSG_USERS.MSU_ROLE_NAME 不在 ('CUSTOMER_INTEGRATION' 'CUSTOMER_EXTERNAL_INTEGRATION' 'INTEGRATION_SUPERUSER' 'OAUTH2_CLIENT') 的情况下
按顺序 ...
获取前 7 行;

英文:

In principle you do

select ...
from (((( ...
                              ORDER BY
                                 MIN(UPPER(MSG_USERS.MSU_LOGIN_NAME)) ASC
                           )
                        ORDER BY
                           UPPER(MSU_LOGIN_NAME_SORT) ASC,
                           ID ASC
                     )
                  WHERE
                     ROWNUM &lt; 7
               )
            ORDER BY
               UPPER(MSU_LOGIN_NAME_SORT) DESC,
               ID DESC
         )
      WHERE
         ROWNUM &lt; 7
   )
ORDER BY
   UPPER(MSU_LOGIN_NAME_SORT) ASC,
   ID ASC;

What is the purpose of this crazy chain?

You make LEFT JOIN with tables MSG_ACCOUNT_USERS, MSG_ACCOUNTS, MSG_TENANTS but they are not used in your query. So, why do you (left) join them? They have no effect, I would guess you can simply remove them from your query and then you even don't need the GROUP BY clause.

I assume your query can be reduced to something like this:

SELECT
   MSG_USERS.MSU_USER_ID ID,
   MSG_USERS.MSU_LAST_LOGIN_TIME,
   MSG_USERS.MSU_CREATED_DATE,
   MSG_USERS.MSU_LOGIN_NAME,
   MSG_USERS.MSU_UUID,
   MSG_USERS.MSU_FIRST_NAME,
   MSG_USERS.MSU_LAST_NAME,
   MSG_USERS.MSU_LAST_UPDATED,
   MSG_USERS.MSU_CUSTOMER_UID
FROM MSG_USERS
WHERE MSG_USERS.MSU_ROLE_NAME NOT IN (&#39;CUSTOMER_INTEGRATION&#39; &#39;CUSTOMER_EXTERNAL_INTEGRATION&#39; &#39;INTEGRATION_SUPERUSER&#39; &#39;OAUTH2_CLIENT&#39;)
ORDER BY ...
FETCH FIRST 7 ROWS ONLY;

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

SQL执行时间太长。如何优化这个查询？

问题

答案1

How is the Definer Rights program unit obtaining ALTER SESSION Privilege?

SQL在表的最左边显示指定列的查询结果。

获取薪水高于平均薪水50%的员工。

在PostgreSQL中，是否可以省略WHERE子句中的字段？

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。