SQL性能调优 - 更新查询

Question

在我的下面的更新查询中，我有两个子查询。它们都是相同的，除了它选择的列（ColumnA、ColumnB）。单个子查询会返回超过 100,000 条记录，其中将选取第一行记录。

但是由于性能问题，将两个子查询合并为单个查询是可能的。

如何合并？

UPDATE TABLE1 SET 
LOWEST_RATE = (SELECT TOP 1 ColumnA from Table2 WHERE Table2.Currency = Table1.Currency),
DIFF_RATE = (SELECT TOP 1 ColumnB from Table2 WHERE Table2.Currency = Table1.Currency)

- Sarath

---

为什么你在没有使用order by的情况下进行top 1操作？这样做不能保证你得到想要的值。 - Bobby

请详细解释一下数据量。table1总共有多少行？table2总共有多少行？您是说对于table1中的每一行，子查询将扫描table2中的100,000行。因为“lakh”这个术语并不被普遍理解，所以我使用了100,000。 - Paul Maxwell

这些表上存在哪些索引？我们能否获取包括索引和每个表的一些示例数据在内的DDL？（小样本即可） - Paul Maxwell

Answer 1

使用可更新的CTE和row_number()函数，获取每种货币的单个行：

;with x as (
select Currency, ColumnA, ColumnB,
row_number() over(partition by Currency order by ...) as rn
from Table1
),
y as (
select t1.*, t2.*
from Table1 t1
join x t2 on t1.currency = t2.currency and t2.rn = 1
)
update y
set 
lowest_rate = ColumnA,
diff_rate = ColumnB

如果您真的不介意从Table2中选择哪一行，请使用任何列代替...

- dean

Answer 2

也许你可以像这样做。

UPDATE TABLE1 SET 
              LOWEST_RATE = (SELECT TOP 1 ColumnA),
              DIFF_RATE = (SELECT TOP 1 ColumnB)
from Table2 INNER JOIN  Table1 
on Table2.Currency = Table1.Currency

- Dudi Konfino

Answer 3

如果我没错的话，你的相关子查询可以转换成INNER JOIN。试试这个。

UPDATE A 
SET    LOWEST_RATE = B.columna, 
       DIFF_RATE = C.columnb 
FROM   table1 A 
       INNER JOIN (SELECT Max(columna) ColumnA, 
                          currency 
                   FROM   table2 
                   GROUP  BY currency) B 
               ON A.currency = B.currency 
       INNER JOIN (SELECT Max(columnb) ColumnB, 
                          currency 
                   FROM   table2 
                   GROUP  BY currency) C 
               ON A.currency = C.currency 

- Pரதீப்

Answer 4

使用CROSS APPLY是处理通过CTE传递到更新中的相关子查询的有效方法。

;WITH cte
    AS (
        SELECT
            table1.lowest_rate 
          , table1.diff_rate 
          , ca1.lowest_one
          , ca2.highest_one
        FROM table1
        CROSS APPLY (
                SELECT TOP (1)
                    ColumnA
                FROM table2
                WHERE table1.Currency  = table2.Currency 
                ORDER BY
                    ColumnA  ASC
            ) ca1 (lowest_one)
        CROSS APPLY (
                SELECT TOP (1)
                    ColumnB
                FROM table2
                WHERE table1.Currency  = table2.Currency 
                ORDER BY
                    ColumnB  DESC
            ) ca2 (highest_one)
        )
UPDATE cte
SET
    lowest_rate = lowest_one
  , diff_rate = highest_one
;

- Paul Maxwell