SQL Server Performance Forum – Threads Archive
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 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 .. !
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)
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
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.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
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
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 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 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 .. !
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.
Can you post the exact sql code you used? Madhivanan Failing to plan is Planning to fail
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 .. !
Why didnt you use the method I suggested? Madhivanan Failing to plan is Planning to fail
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.
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
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 .. !
]]>