sqlsql-serverquery-optimization

How to update by the other GROUPed table values effectively?


I have the tables Invoices (with No_ key) and the related InvoiceLines (with [Document No_] key that bounds to their invoices). The subset of invoice numbers is inserted into the table variable (no problem here).

The next step is to update the table variable with the count of lines for the invoice, and with the maximum timestamp of the lines of the invoice. So far, I am updating the values as shown below:

    DECLARE @inv TABLE (
        No_ nvarchar(20) PRIMARY KEY NOT NULL,
        timestamp_line binary(8),
        line_count int
    )

    -- @inv is filled by subset of invoice numbers (No_) here.

    UPDATE @inv 
       SET timestamp_line = (SELECT MAX(lin.timestamp) 
                               FROM InvoiceLines AS lin WITH (NOLOCK)
                              WHERE lin.[Document No_] = No_),
           line_count =     (SELECT COUNT(*) 
                               FROM InvoiceLines AS lin WITH (NOLOCK)
                              WHERE lin.[Document No_] = No_) 

It works. Anyway, is there a better way to get the aggregate values from the InvoiceLines? Is the aggregation of InvoiceLines here done twice, or is it optimized by the SQL engine to be done only once? Is there a way to get the COUNT(*) and the MAX(timestamp) in one command, and to use the values for the UPDATE?


Solution

  • You can do this with a single scan instead of two (or many, depending on how those subqueries optimize). You just need to move the aggregation to its own thing:

    ;WITH agg AS
    (
      SELECT [Document No_],
             ts = MAX(timestamp),
             c  = COUNT(*)
        FROM dbo.InvoiceLines AS lin
       WHERE [Document No_] IN (SELECT [No_] FROM @inv)
       GROUP BY [Document No_]
    )
    UPDATE i
       SET i.timestamp_line = agg.ts,
           i.line_count = agg.c
      FROM @inv AS i
      JOIN agg ON i.[No_] = agg.[Document No_];
    

    Leaving out your NOLOCKs was intentional but I especially don't know what you want it to achieve when you are performing an update.