SQL Server Performance

INSERT operation too slow.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by GRKool, Jun 15, 2007.

  1. GRKool New Member

    My code reads records from table A in a cursor one by one.

    Read record - @emp_id
    {
    If the record (@emp_id) exists in table B,
    Update the record with values from table A.
    Else,
    Insert new record in table B with values from table A.

    Read next record - @emp_id
    }

    Here, table A is too large and INSERT is performed record by record;
    which takes too long to execute.

    To, improve the performance, I tried to convert it simply into -
    Insert empid, .. , .. into table_B
    select emp_id, ... , ... from table_A where (emp_id NOT found IN table_B).

    This statement threw an error as -
    Column "emp_id" not allowed in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

    My aim is to improve the performance of INSERT statement here.
    Any inputs ?


    Thanks .. !
  2. DilliGrg Member

    quote:Originally posted by GRKool
    Here, table A is too large and INSERT is performed record by record;
    which takes too long to execute.

    To, improve the performance, I tried to convert it simply into -
    Insert empid, .. , .. into table_B
    select emp_id, ... , ... from table_A where (emp_id NOT found IN table_B).

    This statement threw an error as -
    Column "emp_id" not allowed in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

    My aim is to improve the performance of INSERT statement here.
    Any inputs ?


    Thanks .. !



    Are those the exact syntax you used for INSERT? Then, there are many syntax errors. Either you can do this for simple insert part:

    SELECT * INTO Table_B FROM Table_A
    OR
    INSERT INTO Table_B
    SELECT * FROM Table_A

    Also, you are right for removing cursor to do simple insert. There is no need of cursor for simple insert like this.




    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

  3. Madhivanan Moderator

    Dont use Cursor

    General Approach

    Update T
    set col=S.col
    from TargetTable T inner join SourceTable S
    on T.keycol=S.keycol

    Insert into TargetTable (columns)
    Select columns from SourceTable S
    where not exists
    (Select * from TargetTable where keycol=S.keycol)

    Madhivanan

    Failing to plan is Planning to fail
  4. GRKool New Member

    quote:Originally posted by Madhivanan

    Dont use Cursor

    General Approach

    Update T
    set col=S.col
    from TargetTable T inner join SourceTable S
    on T.keycol=S.keycol

    Insert into TargetTable (columns)
    Select columns from SourceTable S
    where not exists
    (Select * from TargetTable where keycol=S.keycol)

    Madhivanan

    Failing to plan is Planning to fail


    Hi,

    I have tried the same INSERT operation suggested by you.
    But in this case, columns in TargetTable are getting populated from SourceTable.
    In my TargetTable, one of the columns (empid) has constraints that it can not be populated through another column value. (When we use cursor, we fetch the empid in a variable and then populate; so this particular pbm has no chance to occur there).
    As I have mentioned in my pbm itself; the above INSERT stmt gives an error as -
    "Column "emp_id" not allowed in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

    Now, I want to get rid of this error and want to improve the performance as well.
    I hope this clears the problem.

    Thanks ..!
  5. GRKool New Member

    quote:Originally posted by DilliGrg


    quote:Originally posted by GRKool
    Here, table A is too large and INSERT is performed record by record;
    which takes too long to execute.

    To, improve the performance, I tried to convert it simply into -
    Insert empid, .. , .. into table_B
    select emp_id, ... , ... from table_A where (emp_id NOT found IN table_B).

    This statement threw an error as -
    Column "emp_id" not allowed in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

    My aim is to improve the performance of INSERT statement here.
    Any inputs ?


    Thanks .. !



    Are those the exact syntax you used for INSERT? Then, there are many syntax errors. Either you can do this for simple insert part:

    SELECT * INTO Table_B FROM Table_A
    OR
    INSERT INTO Table_B
    SELECT * FROM Table_A

    Also, you are right for removing cursor to do simple insert. There is no need of cursor for simple insert like this.




    Name
    ---------
    Dilli Grg

    (1 row(s) affected)


    This is not the exact syntax; just kind of a psuedo code.
  6. Madhivanan Moderator

    Can you post the exact sql code you used?

    Madhivanan

    Failing to plan is Planning to fail
  7. GRKool New Member

    This is original code which is pretty slow - (specifically the INSERT statement).

    *****************************************************************************************
    Declare CursorA CURSOR For
    SELECT emp_id
    FROM tableA

    OPEN CursorA
    FETCH NEXT FROM CursorA
    INTO @empId

    WHILE @@FETCH_STATUS = 0
    BEGIN

    If Exists (Select 1 from tableB Where iEmpId = @empId)
    Begin
    Update tableB
    Set UpdateBy = suser_sname(),
    UpdateDate = GetDate()
    Where iEmpId = @empId
    End
    Else
    Begin
    Insert Into tableB (
    iEmpId, iSiteId, User11, User12,
    User13, User14, User15, User16,
    User17, User18, User19, User20,
    User21, User22, User23, User24,
    User25, User26, User27, User28,
    User29, User30, InsertBy, InsertDate,
    UpdateBy, UpdateDate, RecordStatus)
    Values (
    @empId, 1, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, suser_sname(), GetDate(),
    suser_sname(), GetDate(), 1)
    End

    FETCH NEXT FROM CursorA
    INTO @empId

    END
    *****************************************************************************************

    I tried to convert this like this - (to check whether performance improves it not)

    /* Do the UPDATE first */

    Update tableB
    Set UpdateBy = suser_sname(),
    UpdateDate = GetDate()
    Where iEmpId IN(select emp_id
    from tableA)

    /* Do the INSERT now */

    Insert Into tableB (
    iEmpId, iSiteId, User11, User12,
    User13, User14, User15, User16,
    User17, User18, User19, User20,
    User21, User22, User23, User24,
    User25, User26, User27, User28,
    User29, User30, InsertBy, InsertDate,
    UpdateBy, UpdateDate, RecordStatus)
    Values (
    emp_Id, 1, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, suser_sname(), GetDate(),
    suser_sname(), GetDate(), 1)
    from tableA
    Where emp_id NOT IN(select iEmpid fom tableB)

    *****************************************************************************************

    For new code (INSERT statement) - this is the error :

    The name 'emp_Id' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

    *****************************************************************************************


    Thanks .. !




  8. Madhivanan Moderator

    Why didnt you use the method I suggested?

    Madhivanan

    Failing to plan is Planning to fail
  9. GRKool New Member

    quote:Originally posted by Madhivanan

    Why didnt you use the method I suggested?

    Madhivanan

    Failing to plan is Planning to fail

    Can you please take a look at the error I have mentioned while execution of INSERT ?
    Because in any of the cases, IN/EXISTS - iEmpID from tableB will be populated from emp_id of tableA.

    Will try EXISTS as well; but the error also needs to be taken care of !


    Thanks.
  10. Madhivanan Moderator

    Try this

    Insert Into tableB (
    iEmpId, iSiteId, User11, User12,
    User13, User14, User15, User16,
    User17, User18, User19, User20,
    User21, User22, User23, User24,
    User25, User26, User27, User28,
    User29, User30, InsertBy, InsertDate,
    UpdateBy, UpdateDate, RecordStatus)
    select
    emp_Id, 1, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL,
    NULL, NULL, suser_sname(), GetDate(),
    suser_sname(), GetDate(), 1
    from tableA
    Where emp_id NOT IN(select iEmpid fom tableB)


    Madhivanan

    Failing to plan is Planning to fail
  11. GRKool New Member

    Ohh .. I mis-interpreted the error it seems :-(<br />You are right; this worked perfectly fine and I can see the performance improvement as well. The execution time reduced to 48 secs from around 6 minutes.<br /><br />Thanks a lot <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Btw, Can you throw some light on using EXISTS instead of IN here ?<br /><br />
  12. Adriaan New Member

    Where emp_id NOT IN(select iEmpid fom tableB)

    is equivalent to

    Where NOT EXISTS (select iEmpid fom tableB where tableB.iEmpId = tableA.emp_id )

    ***

    Execution plans are usually pessimistic about the weight of an EXISTS clause: EXISTS can perform better than IN, but it depends on how many matches you're expecting to find.

    In any case, it never hurts to rewrite a (NOT) IN subquery as (NOT) EXISTS.
  13. GRKool New Member

    Okay, Thanks everyone .. !

Share This Page