时间戳

首页 > 解决方案 > 更新查询需要更多时间

sql-server - 更新查询需要更多时间

问题描述

更新查询需要更多时间。没有TOP 1，有没有其他方法可以写下面的查询？喜欢使用排名或行号功能？

SELECT proddiv, 
       prodstl, 
       prodcode, 
       lablcode, 
       proddim, 
       proddiv       AS PRD_DIV, 
       prodstl       AS PRD_PRODSTL, 
       prodcode      AS PRDCOLOR, 
       label_code    AS PRDlbl, 
       proddim       AS par_dim, 
       0             AS low_level, 
       0             AS smuh_pkey, 
       IDENTITY(int) AS prdkey, 
       list_price 
INTO   #brandsdata 
FROM   products WITH (nolock) 

DECLARE @Max INT = 30, 
        @x   INT = 0 

WHILE ( @x < 30 ) 
  BEGIN 
      UPDATE t 
      SET    smuh_pkey = (SELECT TOP 1 prdkey 
                          FROM   customers WITH (nolock) 
                          WHERE  ( proddiv = '' 
                                    OR proddiv = t.proddiv ) 
                                 AND ( prodstl = '' 
                                        OR prodstl = t.prodstl ) 
                                 AND ( prodcode = '' 
                                        OR prodcode = t.prodcode ) 
                                 AND ( lablcode = '' 
                                        OR lablcode = t.lablcode ) 
                                 AND ( proddim = '' 
                                        OR proddim = t.proddim ) 
                                 AND prod_type = '' 
                                 AND active_ok = 'Y' 
                          ORDER  BY brand_name, 
                                    brand_type DESC) 
      FROM   #brandsdata t 
      WHERE  smuh_pkey = 0 
             AND low_level = @x 

      SET @x = @x + 1 
  END

提前致谢。

标签： sql-serverperformancesql-server-2012sql-updatessms

解决方案

尝试这样的事情。

;with c as (
    select c.prdKey, c.proddiv, c.prodstl, c.prodcode, c.lablcode, c.proddim, 
        ROW_NUMBER() over (
            partition by c.proddiv, c.prodstl, c.prodcode, c.lablcode, c.proddim 
        order by c.brand_name, c.brand_type DESC) rn
     from customers c
     where prod_type = '' and  
     active_ok = 'Y' 
)
update t
set smuh_pkey = c.prdKey
from #brandsdata t 
inner join c 
    on      ( c.proddiv  = '' OR c.proddiv  = t.proddiv ) 
        AND ( c.prodstl  = '' OR c.prodstl  = t.prodstl ) 
        AND ( c.prodcode = '' OR c.prodcode = t.prodcode ) 
        AND ( c.lablcode = '' OR c.lablcode = t.lablcode ) 
        AND ( c.proddim  = '' OR c.proddim  = t.proddim ) 
where c.rn = 1

我认为这对于 100% 的情况来说是不够的，因为如果你可以有多个空字符串和非空字符串的组合，你可能会得到不同的结果。然后你可以微调

推荐阅读