How do I optimize a query that has high cost from Clustered Index Insert [CWT_PrimaryKey] and stop it from doing a full Table Scan?

Iris Pleasent 2014-03-12 13:52:58

Aaron Bertrand 2014-03-12 14:14:59
Here's how I would write this query without the cursor, using a single, set-based operation, though note that the TOP 1 you use to set @TMP_ID above is non-deterministic, and so is this. If you expect a specific customer to be selected, in the event that more than one match is found between customers and the view (e.g. the one most recently updated), you'll need to specify those requirements.

ALTER PROCEDURE dbo.RISP_UPDATE_COMPANY_FROM_PSOFT
AS
BEGIN
  SET NOCOUNT ON;
 
  UPDATE comp
  SET
    Phone             = CAST(v.PHONE AS NVARCHAR(14)),
    Fax               = CAST(v.Fax AS NVARCHAR(14)),
    BillingAddress1   = CAST(v.ADDRESS1 AS NVARCHAR(50)),
    BillingAddress2   = CAST(v.ADDRESS2 AS NVARCHAR(50)),
    BillingAddress3   = CAST(v.ADDRESS3 AS NVARCHAR(50)),
    BillingCity       = CAST(v.CITY AS NVARCHAR(50)),
    BillingStateID    = s.StateID,
    BillingPostalCode = CAST(v.POSTAL AS NVARCHAR(10)),
    BillingCountryID  = c.CountryID
  FROM dbo.Company AS comp
  INNER JOIN dbo.Customers AS cust 
    ON comp.CompanyID = cust.CompanyID
  INNER JOIN dbo.PS_RI_TRX_CUST_VW AS v 
    ON v.CUST_ID = cust.psHomeOfficeID
    AND v.ADDRESS_SEQ_NUM = cust.psHomeLocationID
  INNER JOIN dbo.States AS s 
    ON RTRIM(v.[STATE]) = RTRIM(s.Abbreviation)
  INNER JOIN dbo.Countries AS c 
    ON RTRIM(v.COUNTRY) = RTRIM(c.Abbreviation)
  WHERE cust.CompanyID IS NOT NULL; 
    -- WHERE clause is redundant, but maybe matches a filtered index?
END
GO

I don't believe this will prevent a full table scan, unless only a small number of rows match. But you shouldn't always consider a table scan to be a problem, or the problem. In many cases, this is the most efficient way for SQL Server to operate on a table. Rob Farley just blogged about this yesterday.