登录
 
主页技术资源库学习下载支持社区论坛
IT 专业人士的资源 >
> 
> 
Optimize UPDATE query with sub queries

已答复 Optimize UPDATE query with sub queries

  • 2012年7月6日 9:00
     
      包含代码
    登录进行投票
    0

    Hi,
    Other than creating indexes, does anyone has suggestions to optimize the query below ?
    Thank you in advance for your time. 

    UPDATE t_bill_service
SET    insurancevalue = (SELECT Isnull(Sum(unitprice * qty), 0)
                         FROM   t_dist_servicedetail
                         WHERE  serviceid = t_bill_service.serviceid
                                AND sku IN ( 'ADC.47', 'ADC.39' )),
       reimbursementvalue = (SELECT Isnull(Sum(unitprice * qty), 0)
                             FROM   t_dist_servicedetail
                             WHERE  serviceid = t_bill_service.serviceid
                                    AND sku = 'ADC.21'),
       disbursementvalue = (SELECT Isnull(Sum(unitprice * qty), 0)
                            FROM   t_dist_servicedetail
                            WHERE  serviceid = t_bill_service.serviceid
                                   AND sku = 'ADC.22'),
       otherservicesvalue = (SELECT Isnull(Sum(unitprice * qty), 0)
                             FROM   t_dist_servicedetail
                             WHERE  serviceid = t_bill_service.serviceid
                                    AND sku NOT IN ( 'ADC.21', 'ADC.22','ADC.47','ADC.39' ))
WHERE  invoiceid = 404927

     

全部回复

  • 2012年7月6日 9:22
     
     已答复 包含代码
    登录进行投票
    0

    Hi,

    ;with CTE
as
(
 select serviceId
 , sum(case when sku in ('ADC.47', 'ADC.39') then  unitProce * qty else 0 end ) as insurancevalue
 , sum(case when sku in ('ADC.21') then  unitProce * qty else 0 end ) as reimbursementvalue 
 from  t_dist_servicedetail
 group by serviceId
)  
update t
set t.insurancevalue =  c.insurancevalue
, t.reimbursementvalue =  c.reimbursementvalue
from t_bill_service  t
inner join Cte c on t.serviceId = c.serviceId
and t.invoiceid = 404927

    - Chintak (My Blog)

     
  • 2012年7月6日 9:23
     
     已答复
    登录进行投票
    0

    I will probably update the table t_bill_service by joining with t_dist_servicedetail table and use CASE statement in update.

    Murali Krishnan

     
  • 2012年7月6日 9:38
     
     已答复
    登录进行投票
    0

    UPDATE t_bill_service       
    SET    insurancevalue = coalesce(sd.insurancevalue, 0),
           reimbursementvalue = coalesce(sd.reimbursementvalue, 0),
           disbursementvalue = coalesce(sd.disbursementvalue, 0),
           otherservicesvalue = coalesce(sd.otherservicesvalue, 0)
    FROM  t_bill_service bs
    JOIN  (SELECT serviceid,
                  insurancevalue = SUM(CASE WHEN sku IN ('ADC.47', 'ADC.39') THEN unitprice*qty END),
                  reimbursementvalue = SUM(CASE WHEN sku IN  ('ADC.21') THEN unitprice*qty END),
                  disbursementvalue = SUM(CASE WHEN sku IN  ('ADC.22') THEN unitprice*qty END),
                  otherservicesvalue = SUM(CASE WHEN sku  NOT IN  ('ADC.21', 'ADC.22', 'ADC.47','ADC.39') THEN unitprice*qty END)
           FROM    t_dist_servicedetail
           GROUP  BY serviceid) AS sd ON bs.serviceid= ds.serviceid
    WHERE  bs.invoiceid = 404927 

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • 2012年7月6日 9:59
     
     
    登录进行投票
    0

    I will probably update the table t_bill_service by joining with t_dist_servicedetail table and use CASE statement in update.

    Murali Krishnan


    Thank you.
     
  • 2012年7月6日 10:00
     
      包含代码
    登录进行投票
    0

    Hi,

    ;with CTE
as
(
 select serviceId
 , sum(case when sku in ('ADC.47', 'ADC.39') then  unitProce * qty else 0 end ) as insurancevalue
 , sum(case when sku in ('ADC.21') then  unitProce * qty else 0 end ) as reimbursementvalue 
 from  t_dist_servicedetail
 group by serviceId
)  
update t
set t.insurancevalue =  c.insurancevalue
, t.reimbursementvalue =  c.reimbursementvalue
from t_bill_service  t
inner join Cte c on t.serviceId = c.serviceId
and t.invoiceid = 404927

    - Chintak (My Blog)



    Thank you.
     
  • 2012年7月6日 10:06
     
     
    登录进行投票
    0

    UPDATE t_bill_service       
    SET    insurancevalue = coalesce(sd.insurancevalue, 0),
           reimbursementvalue = coalesce(sd.reimbursementvalue, 0),
           disbursementvalue = coalesce(sd.disbursementvalue, 0),
           otherservicesvalue = coalesce(sd.otherservicesvalue, 0)
    FROM  t_bill_service bs
    JOIN  (SELECT serviceid,
                  insurancevalue = SUM(CASE WHEN sku IN ('ADC.47', 'ADC.39') THEN unitprice*qty END),
                  reimbursementvalue = SUM(CASE WHEN sku IN  ('ADC.21') THEN unitprice*qty END),
                  disbursementvalue = SUM(CASE WHEN sku IN  ('ADC.22') THEN unitprice*qty END),
                  otherservicesvalue = SUM(CASE WHEN sku  NOT IN  ('ADC.21', 'ADC.22', 'ADC.47','ADC.39') THEN unitprice*qty END)
           FROM    t_dist_servicedetail
           GROUP  BY serviceid) AS sd ON bs.serviceid= ds.serviceid
    WHERE  bs.invoiceid = 404927 

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thank you.