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?
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.