INSERT operation too slow.

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 .. !

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)

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 ..!

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.

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 .. !

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.

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 />

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.

Okay, Thanks everyone .. !