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

Hi
Try to avoid using a cursor instead one option to create a temporary table
at the begining of the stored procedure.




"Divyesh" <divyeshkhatri (AT) hotmail (DOT) com> wrote

On 21 Dec 2004 16:17:40 -0800, Divyesh wrote:

(snip)

Hi Divyesh,

I just posted a reply in comp.databases.ms-sqlserver. Please don't
multi-post.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Could you also post the DDL for acc_import_aka_xe?

Although T-SQL provides some procedural-like statements, it is not intended
to be used as a procedural language. All to often we programmers cannot
break the habit of "I do it this way in _____ so I should do it that way in
T-SQL too" and we end up with poorly performing procedures that look like
what you've posted.

Cursor-based solutions should only be used as a last resort and even then,
they're "iffy". Set-based solutions (on which relational databases are
based) will always outperform a cursor-based solution. Most likely, this
entire procedure can be replaced with two different DML queries. Your first
query could be INSERT INTO REFENTITY (...) SELECT ... FROM ImportClient
WHERE NOT EXISTS ... while your second query could be UPDATE REFENTITY SET
.... FROM REFENTITY, ImportClients WHERE ... You would obviously need to run
a few joins to get some of the data from other tables (such as the data from
LTABLE).

Switching to this methodology should help things run considerably faster for
you. Without knowing the function/structure of acc_import_aka_xe though,
it's impossible further define how to perform with an entirely set based
operation.

HTH and good luck
--
Dave Fancher
http://davefancher.blogspot.com


"Divyesh" <divyeshkhatri (AT) hotmail (DOT) com> wrote