I am trying to write an SQL statement to update a large table (6 million rows) from a smaller one (3000 rows) TABLE A booking int nonclustered index supplier char(6) nonclustered index amt money native_amt money cur char(3) br char(2) TABLE B supplier char(6) nonclustered index cur char(3) br char(2) I need to update table A with cur from table B and an exchange rate update a set a.cur = b.cur, a.rate = CASE WHEN b.cur = 'GBP' THEN (2.2) WHEN b.cur = 'USD' THEN (1.25) ELSE (1) END, a.amt = CASE WHEN b.cur = 'GBP' THEN (CAST(a.native_amt * 2.2 as decimal(18,2))) WHEN b.cur = 'USD' THEN (CAST(a.native_amt * 1.25 as decimal(18,2))) ELSE (a.native_amt) END from costs a INNER JOIN(select supplier,branch,cur from sufinance where cur <> 'CAD') b ON b.supplier = a.supplier and b.branch = a.branch The SQL execution plans sows a table scan of table A and the statement takes over 30 minutes to complete. Is there something I am doing wrong? Most of the entries in table a use a supplier that does not have CAD as currency (most are USD and very few are GBP)
Why these tables are Heap? Why there is no Clustered INdex? Generally, in SQL Server each table should have Clusterd index. Coming to the query, The indexes available on the table is not useful. It may not be used because , optimizer may not find it useful. Covering index may help to improve performance. Covering index is the index which covers all the columns in the query. Also You are using Functions (CAST) against a.Native_amt which may cause Table scan. Madhu
