sql - 使用嵌套查询与 CTE 以及性能影响

Question

我使用 SQL 一段时间了，想知道将包含大量 CTE 的脚本转换为常规嵌套脚本是否有意义以及如何有意义。我正在使用:

WITH cte_account_pricelevelid 
     AS (SELECT a.accountid, 
                a.pricelevelid 
         FROM   companypricelist a 
                JOIN(SELECT accountid 
                     FROM   crm_accountbase 
                     WHERE  defaultpricelevelid IS NULL) b 
                  ON a.accountid = b.accountid), 
     totals 
     AS (SELECT a.accountid, 
                a.pricelevelid 
         FROM   companypricelist a 
                JOIN(SELECT accountid 
                     FROM   crm_accountbase 
                     WHERE  defaultpricelevelid IS NULL) b 
                  ON a.accountid = b.accountid), 
     totalsgrouped 
     AS (SELECT pricelevelid, 
                Count(*) counts 
         FROM   totals 
         GROUP  BY pricelevelid), 
     final 
     AS (SELECT cte.accountid, 
                cte.pricelevelid, 
                frequency.counts 
         FROM   cte_account_pricelevelid cte 
                CROSS JOIN totalsgrouped frequency 
         WHERE  cte.pricelevelid = frequency.pricelevelid), 
     mycolumns 
     AS (SELECT b.accountid, 
                b.pricelevelid, 
                b.counts 
         FROM   (SELECT accountid 
                 FROM   crm_accountbase 
                 WHERE  defaultpricelevelid IS NULL) a 
                JOIN final b 
                  ON a.accountid = b.accountid), 
     e 
     AS (SELECT *, 
                Row_number() 
                  OVER ( 
                    partition BY accountid 
                    ORDER BY counts, pricelevelid ) AS Recency 
         FROM   mycolumns), 
     cte_result 
     AS (SELECT accountid, 
                pricelevelid 
         FROM   e 
         WHERE  recency = 1) 

SELECT a.accountid, 
       a.defaultpricelevelid, 
       b.pricelevelid 
FROM   crm_accountbase a 
       JOIN cte_result b 
         ON a.accountid = b.accountid 

我觉得在我的 CTE 中继续运行相同的查询是愚蠢的:

SELECT accountid 
                     FROM   crm_accountbase 
                     WHERE  defaultpricelevelid IS NULL

但我不知道如何解决这个问题。我想我可以将其转换为，但不知道是否会有任何性能提升。

select * from (select * from(select * from(...

是否有机会通过将其转换为嵌套 SQL 或只是简化 CTE 来极大地提高性能？如果是这样，您能让我开始吗？

Answer 1

如果 accountid 已编入索引，则此连接将不会使用该索引
派生表没有索引

SELECT a.accountid, a.pricelevelid 
  FROM companypricelist a 
  JOIN (SELECT accountid 
          FROM crm_accountbase 
                     WHERE  defaultpricelevelid IS NULL) b 
    ON a.accountid = b.accountid

这将使用 accountid 上的索引
即使accountid上没有索引也会更快

SELECT a.accountid, a.pricelevelid 
  FROM companypricelist a 
  JOIN crm_accountbase  b 
    ON a.accountid = b.accountid 
   AND b.defaultpricelevelid IS NULL