SQL Server Performance

Updating Table

Discussion in 'General Developer Questions' started by mikel4538, Dec 8, 2008.

  1. mikel4538 New Member

    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)
  2. madhuottapalam New Member

    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

Share This Page