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?
2014-03-12 13:52:58
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.