SQL Server Performance

Update table without using Cursor & Temp Table

Discussion in 'T-SQL Performance Tuning for Developers' started by sonnysingh, Feb 6, 2007.

  1. sonnysingh Member

    Hi All

    I want to write following update query without using
    quote:CURSOR and TEMP Table
    .

    DECLARE @col1 as bigint
    DECLARE @col2 as bigint
    DECLARE @col3 as varchar(10)
    DECLARE @col4 as varchar(150)



    DECLARE updateCur CURSOR
    FOR SELECT DISTINCT col1,col2,col3,col4 FROM table1
    OPEN updateCur
    FETCH NEXT FROM updateCur INTO @col1,@col2,@col3,@col4
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    UPDATE table2
    SET col2=@col2,col3=@col3,col4=@col4 WHERE Colid = @col1
    END
    FETCH NEXT FROM updateCur INTO @col1,@col2,@col3,@col4
    END
    CLOSE updateCur
    DEALLOCATE updateCur


    Thanks in Advance.....
  2. Roji. P. Thomas New Member

    UPDATE T2
    SET T2.Col2 = T1.Col2,
    T2.Col3= T1.Col3.....
    FROM Table2 T2
    INNER JOIN Table1 T1
    ON T1.Col1 = T2.col1


    Roji. P. Thomas
    SQL Server MVP
    http://toponewithties.blogspot.com
  3. sonnysingh Member

    Thanks Roji...

    I have "SET STATISTICS IO ON" RESULTS and want to make sure that it best performance possibly achievable...

    result:
    Table 'T2_________________000100000025'. Scan count 1, logical reads 66, physical reads 0, read-ahead reads 0.
    Table 'T1_________________000100000025'. Scan count 84, logical reads 168, physical reads 0, read-ahead reads 0.

    I am confuse about logical scan figures..Table have 84 records as it is not much data and there clustered index on T1.Col1 and T2.col1 (in JOIN clause)..

    Advice and Thanks in Advance.

Share This Page