By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Help with Stored Procedure

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 21 Dec 2004 16:12:01 -0800, di***********@hotmail.com wrote:
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.


Hi Divyesh,

Using cursors to process many rows is notoriously slow. In well over 99%
of all cases, a set-based solution will be much, much quicker. However,
you didn't provide enough information to assist you in rewriting this
procedure to use set-based logic. We need:

* The structure of all tables used in the query (posted as CREATE TABLE
statements, omitting all irrelevant columns but including all constraints,
properties and indexes on the other columns),
* Some illustrative sample data (posted as INSERT statement to facilitate
testing),
* Expected output (based on the sample data you posted, of course),
* The code for the user-defined functions (dbo.fn_GetClarionDate,
dbo.fn_GetDate) and stored procedure (acc_import_aka_xe) you call in your
current procedure,
* A description of the business problem you're trying to solve.
Based on the code you did post, I can't give you much advise. You might
try if using a FAST_FORWARD cursor helps, but I don't think it will be
enough. Another thing is to use profiler to see which of the queries take
most time and to check if indexes can be added or changed to speed up
those indexes.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.