Hi I have written following stored procedure but it take way too long
to complete. I will be processing thousands of records and it is slow.
I am importing data from a source table and adding logic and then
inserting into another table. Any help with speeding it up will be
highly appreciated.
Thanks
Divyesh
/*************************************************************/
CREATE PROCEDURE dbo.acc_import
as
declare
@sysid int,
@code varchar(10),
@name varchar(80),
@o_name varchar(80),
@errcode int,
@rcount int,
@status_code varchar(10),
@status_desc varchar(50),
@status_sysid int,
@office_code varchar(10),
@office_desc varchar(50),
@office_sysid varchar(10),
@dept_code varchar(10),
@dept_desc varchar(50),
@dept_sysid int,
@cla_opendate int,
@sql_opendate datetime,
@cla_closedate int,
@sql_closedate datetime,
@oldcode varchar(10),
@entity_id int,
@entity_type varchar (50),
@status_ltype_id int,
@status_ltype_desc varchar (10),
@ltype_aka_code varchar (10),
@refentity_id int
/** cursor through Clients table **/
select @entity_id = LEVEL1_ID from LEVELCONFIG
select @status_ltype_id = STATUS_ID from SYSCONFIG
select @ltype_aka_code = ALIAS_CODE FROM ImportSetup
declare client_cursor cursor
for select
Code,Name,OpenDate,CloseDate,StatusCode,OfficeCode,DeptCode,OldCode
from ImportClient for read only
set nocount on
open client_cursor
fetch client_cursor into
@code,
@name,
@sql_opendate,
@sql_closedate,
@status_code,
@office_code,
@dept_code,
@oldcode
while @@fetch_status = 0
begin
IF NOT EXISTS (SELECT 1 FROM REFENTITY WHERE REFENTITY.ENTITY_REF =
@code and REFENTITY.ENTITY_ID = @entity_id)
begin
select @errcode=@@error
if @errcode<>0 return @errcode
/** Get Status **/
select @status_sysid = LTABLE.LTABLE_ID, @status_desc =
LTABLE.LTABLE_DESC
from LTABLE where LTABLE.LTYPE_ID = @status_ltype_id and
LTABLE.LTABLE_CODE = @status_code
/** Entity Type Description **/
select @entity_type = ENTITY_TYPE.ENTITY_TYPE_DESC
from ENTITY_TYPE where ENTITY_TYPE.ENTITY_TYPE_ID = @entity_id
insert into REFENTITY (
ENTITY_ID,
ENTITY_TYPE,
ENTITY_REF,
ENTITY_DESC,
ENTITY_DESC_LONG,
STATUS_ID,
STATUS_DESC
REF_HIERARCHY
)
values
(
@entity_id, --ENTITY_ID,
@entity_type, -- ENTITY_TYPE,
@code, --ENTITY_REF,
@name, -- ENTITY_DESC,
'', --ENTITY_DESC_LONG,
@status_sysid,
@status_desc
@code
)
select @errcode=@@error
if @errcode<>0 return @errcode
SET @refentity_id = @@IDENTITY
UPDATE REFENTITY
SET
HIERARCHY = '.' + CAST(@@IDENTITY AS varchar(10)) + '.'
WHERE REFENTITY_ID = @refentity_id
END/**IF NOT EXISTS EXISTS **/
else
begin
SELECT @sysid = REFENTITY.REFENTITY_ID, @o_name =
REFENTITY.ENTITY_DESC FROM REFENTITY
WHERE REFENTITY.ENTITY_REF = @code and REFENTITY.ENTITY_ID =
@entity_id
/** Get Status **/
select @status_sysid = LTABLE.LTABLE_ID, @status_desc =
LTABLE.LTABLE_DESC
from LTABLE where LTABLE.LTYPE_ID = @status_ltype_id and
LTABLE.LTABLE_CODE = @status_code
UPDATE REFENTITY
SET
--ENTITY_REF = @code, --ENTITY_REF,
ENTITY_DESC = @name, -- ENTITY_DESC,
STATUS_ID = @status_sysid, -- STATUS_ID,
STATUS_DESC = @status_desc, --STATUS_DESC,
OFFICE_ID = @office_sysid, --OFFICE_ID,
OFFICE_DESC = @office_desc, --OFFICE_DESC,
EDITSTAMPFIELD = dbo.fn_GetDate (getdate()),
--EDITSTAMPFIELD,
EDIT_USER_NO = 1, --EDIT_USER_NO,
DATE_CREATION = dbo.fn_GetClarionDate (@sql_opendate),
DATE_INACTIVE = dbo.fn_GetClarionDate (@sql_closedate)
WHERE REFENTITY.ENTITY_REF = @code AND REFENTITY.ENTITY_ID =
@entity_id
select @errcode=@@error
if @errcode<>0 return @errcode
IF LTRIM(RTRIM(@name)) <> LTRIM(RTRIM(@o_name))
begin
EXEC acc_import_aka_xe 10,@sysid,@o_name,@ltype_aka_code,1
end
END/** IF EXISTS **/
fetch client_cursor into
@code,
@name,
@sql_opendate,
@sql_closedate,
@status_code,
@office_code,
@dept_code,
@oldcode
end /** END OF WHILE client_cursor **/
close client_cursor
DeAllocate client_cursor
set nocount off
go
