none
Optimize UPDATE query with sub queries

    问题

  • 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:00

答案

  • 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:22
  • 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:23
  • 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:38

全部回复

  • 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:22
  • 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:23
  • 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:38
  • 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日 9:59
  • 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:00
  • 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.
    2012年7月6日 10:06