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

How to speed Stored Procedure?

P: n/a
Hello,

I have wrote a stored procedure but its real slow. Its activated by a
button on web page but its takes to long to process and the web server
gives a timeout message after 5 minutes.

Is there anyway to speed up this stored procedure? What am I doing
wrong here?

------------------------------------------------------------------------------

if exists(select 1 from sysobjects where type='P' and
name='sp_scan_import')
begin
drop proc sp_scan_import
end
go
create procedure sp_scan_import
as
/* SCAN_IMPORT */
declare @waarde1 varchar(20) /* barcode */
declare @waarde2 varchar(6) /* location_id */
declare @waarde3 varchar(10) /* scan_time */
declare @waarde4 datetime /* scan_date */
/* SCAN_MAIN */
declare @waarde11 varchar(20) /* barcode */
declare @waarde12 varchar(6) /* location_id */
declare @waarde13 datetime /* scan_date */
declare @waarde14 int /* record_id */
/* SCAN_LOCATIONS */
declare @waarde21 varchar(6) /* location_id */
/* COUNTERS */
declare @countMain int /* counter records to scan_main */
declare @countSub int /* counter records to scan_sub */
declare @countError int /* counter records to scan_error */
declare @countTotal int /* Total processed records */

select @countMain=0
select @countSub=0
select @countError=0
select @countTotal=0

delete from scan_error

declare c_scan_import cursor for select barcode, location_id,
scan_time, scan_date
from scan_import for read only

open c_scan_import
/* get first record from scan_import */
fetch c_scan_import into @waarde1, @waarde2, @waarde3, @waarde4
while (@@fetch_status=0)
begin
/* check data for error's and process data */
select @waarde11=barcode,
@waarde12=location_id,
@waarde13=scan_date,
@waarde14=record_id
from scan_main where barcode=@waarde1

select @waarde21=location_id
from scan_locations where location_id=@waarde2

if (@waarde1=@waarde11 and @waarde2=@waarde21 and
(convert(varchar,@waarde4,120)>=convert(varchar,@w aarde13,120))
and
(convert(varchar,@waarde4,120)<convert(varchar,get date(),120)))
begin
/* copy old record to scan_sub (history) */
insert into scan_sub(main_record_id, location_id, scan_date,
scan_time)
select record_id,
location_id,
scan_date,
scan_time
from scan_main
where barcode=@waarde1
/* update old record in scan_main with new data from scan_import
*/
update scan_main
set location_id=@waarde2,
scan_date= convert(datetime,
/* date part */
substring(convert(varchar,@waarde4,105),7,4)+'-'+
substring(convert(varchar,@waarde4,105),4,2)+'-'+
substring(convert(varchar,@waarde4,105),1,2)+' '+
/* time part */
substring(convert(varchar,@waarde4,108),1,2)+':'+
substring(convert(varchar,@waarde4,108),4,2)+':'+
substring(convert(varchar,@waarde4,108),7,2)
,121),
scan_time= substring(convert(varchar,@waarde3,108),1,2)+':'+
substring(convert(varchar,@waarde3,108),4,2)+':'+
substring(convert(varchar,@waarde3,108),7,2)
where barcode=@waarde1
select @countMain=@countMain+1
end
else
if (@waarde1=@waarde11 and @waarde2=@waarde21 and
(convert(varchar,@waarde4,120)<convert(varchar,@wa arde13,120)))
begin
insert into scan_sub
(
main_record_id,
location_id,
scan_time,
scan_date
)
values
(
@waarde14,
@waarde2,
@waarde3,
@waarde4
)
select @countSub=@countSub+1
end
else
begin
insert into scan_error
(
barcode,
location_id,
scan_time,
scan_date
)
values
(
@waarde1,
@waarde2,
@waarde3,
@waarde4
)
select @countError=@countError+1
end

/* get next record in scan_import for processing */
fetch c_scan_import into @waarde1, @waarde2, @waarde3, @waarde4
end
close c_scan_import
deallocate c_scan_import

select @countTotal=@countMain+@countSub+@countError

delete from scan_result
insert into scan_result(description,result) (select 'in
SCAN_IMPORT',count(*) from scan_import)
insert into scan_result values('to SCAN_MAIN',@countMain)
insert into scan_result values('to SCAN_SUB',@countSub)
insert into scan_result values('to SCAN_ERROR',@countError)
insert into scan_result values('Total processed',@countTotal)
go

------------------------------------------------------------------------------

Please help...

Marc,
Database administrator,
:-)
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Change it to work with sets instead of looping through that cursor. How many
records do you have in scan_import?
Jul 20 '05 #2

P: n/a
Marc,

As Kristofer mentioned in his post, you need to convert your stored
procedure to use a set-based solution. I just want to add a tiny little
thing to it. I don't know how big your tables are. If scan_error and
scan_result tables are big, and if you don't have a transaction to rollback
after deleting records from these two tables, then you would better to use
TRUNCATE TABLE <TableName> instead of DELETE FROM <TableName> to delete all
the records. TRUNCATE is much faster than DELETE because it doesn't log
records, so be carfeul when using it.
But again, your main problem is that cursor.

Shervin

"deprins" <ma***@netlane.com> wrote in message
news:59*************************@posting.google.co m...
Hello,

I have wrote a stored procedure but its real slow. Its activated by a
button on web page but its takes to long to process and the web server
gives a timeout message after 5 minutes.

Is there anyway to speed up this stored procedure? What am I doing
wrong here?

-------------------------------------------------------------------------- ----
if exists(select 1 from sysobjects where type='P' and
name='sp_scan_import')
begin
drop proc sp_scan_import
end
go
create procedure sp_scan_import
as
/* SCAN_IMPORT */
declare @waarde1 varchar(20) /* barcode */
declare @waarde2 varchar(6) /* location_id */
declare @waarde3 varchar(10) /* scan_time */
declare @waarde4 datetime /* scan_date */
/* SCAN_MAIN */
declare @waarde11 varchar(20) /* barcode */
declare @waarde12 varchar(6) /* location_id */
declare @waarde13 datetime /* scan_date */
declare @waarde14 int /* record_id */
/* SCAN_LOCATIONS */
declare @waarde21 varchar(6) /* location_id */
/* COUNTERS */
declare @countMain int /* counter records to scan_main */
declare @countSub int /* counter records to scan_sub */
declare @countError int /* counter records to scan_error */
declare @countTotal int /* Total processed records */

select @countMain=0
select @countSub=0
select @countError=0
select @countTotal=0

delete from scan_error

declare c_scan_import cursor for select barcode, location_id,
scan_time, scan_date
from scan_import for read only

open c_scan_import
/* get first record from scan_import */
fetch c_scan_import into @waarde1, @waarde2, @waarde3, @waarde4
while (@@fetch_status=0)
begin
/* check data for error's and process data */
select @waarde11=barcode,
@waarde12=location_id,
@waarde13=scan_date,
@waarde14=record_id
from scan_main where barcode=@waarde1

select @waarde21=location_id
from scan_locations where location_id=@waarde2

if (@waarde1=@waarde11 and @waarde2=@waarde21 and
(convert(varchar,@waarde4,120)>=convert(varchar,@w aarde13,120))
and
(convert(varchar,@waarde4,120)<convert(varchar,get date(),120)))
begin
/* copy old record to scan_sub (history) */
insert into scan_sub(main_record_id, location_id, scan_date,
scan_time)
select record_id,
location_id,
scan_date,
scan_time
from scan_main
where barcode=@waarde1
/* update old record in scan_main with new data from scan_import
*/
update scan_main
set location_id=@waarde2,
scan_date= convert(datetime,
/* date part */
substring(convert(varchar,@waarde4,105),7,4)+'-'+
substring(convert(varchar,@waarde4,105),4,2)+'-'+
substring(convert(varchar,@waarde4,105),1,2)+' '+
/* time part */
substring(convert(varchar,@waarde4,108),1,2)+':'+
substring(convert(varchar,@waarde4,108),4,2)+':'+
substring(convert(varchar,@waarde4,108),7,2)
,121),
scan_time= substring(convert(varchar,@waarde3,108),1,2)+':'+
substring(convert(varchar,@waarde3,108),4,2)+':'+
substring(convert(varchar,@waarde3,108),7,2)
where barcode=@waarde1
select @countMain=@countMain+1
end
else
if (@waarde1=@waarde11 and @waarde2=@waarde21 and
(convert(varchar,@waarde4,120)<convert(varchar,@wa arde13,120)))
begin
insert into scan_sub
(
main_record_id,
location_id,
scan_time,
scan_date
)
values
(
@waarde14,
@waarde2,
@waarde3,
@waarde4
)
select @countSub=@countSub+1
end
else
begin
insert into scan_error
(
barcode,
location_id,
scan_time,
scan_date
)
values
(
@waarde1,
@waarde2,
@waarde3,
@waarde4
)
select @countError=@countError+1
end

/* get next record in scan_import for processing */
fetch c_scan_import into @waarde1, @waarde2, @waarde3, @waarde4
end
close c_scan_import
deallocate c_scan_import

select @countTotal=@countMain+@countSub+@countError

delete from scan_result
insert into scan_result(description,result) (select 'in
SCAN_IMPORT',count(*) from scan_import)
insert into scan_result values('to SCAN_MAIN',@countMain)
insert into scan_result values('to SCAN_SUB',@countSub)
insert into scan_result values('to SCAN_ERROR',@countError)
insert into scan_result values('Total processed',@countTotal)
go

-------------------------------------------------------------------------- ----
Please help...

Marc,
Database administrator,
:-)

Jul 20 '05 #3

P: n/a
First of all thanks for your reply's. :-)

The table scan_main has 64.000 records, the table scan_import varies
in number of records. The table scan_result doesnt have more then 5
records. And the table scan_error doesnt have more records then max.
50.

But what do you mean by a set-based solution??? I dont understand what
you mean by that? Could you please explain that to me?

Many thanks in advance.

Marc.
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message news:<vp************@corp.supernews.com>...
Marc,

As Kristofer mentioned in his post, you need to convert your stored
procedure to use a set-based solution. I just want to add a tiny little
thing to it. I don't know how big your tables are. If scan_error and
scan_result tables are big, and if you don't have a transaction to rollback
after deleting records from these two tables, then you would better to use
TRUNCATE TABLE <TableName> instead of DELETE FROM <TableName> to delete all
the records. TRUNCATE is much faster than DELETE because it doesn't log
records, so be carfeul when using it.
But again, your main problem is that cursor.

Shervin

"deprins" <ma***@netlane.com> wrote in message
news:59*************************@posting.google.co m...
Hello,

I have wrote a stored procedure but its real slow. Its activated by a
button on web page but its takes to long to process and the web server
gives a timeout message after 5 minutes.

Is there anyway to speed up this stored procedure? What am I doing
wrong here?

--------------------------------------------------------------------------

----

if exists(select 1 from sysobjects where type='P' and
name='sp_scan_import')
begin
drop proc sp_scan_import
end
go
create procedure sp_scan_import
as
/* SCAN_IMPORT */
declare @waarde1 varchar(20) /* barcode */
declare @waarde2 varchar(6) /* location_id */
declare @waarde3 varchar(10) /* scan_time */
declare @waarde4 datetime /* scan_date */
/* SCAN_MAIN */
declare @waarde11 varchar(20) /* barcode */
declare @waarde12 varchar(6) /* location_id */
declare @waarde13 datetime /* scan_date */
declare @waarde14 int /* record_id */
/* SCAN_LOCATIONS */
declare @waarde21 varchar(6) /* location_id */
/* COUNTERS */
declare @countMain int /* counter records to scan_main */
declare @countSub int /* counter records to scan_sub */
declare @countError int /* counter records to scan_error */
declare @countTotal int /* Total processed records */

select @countMain=0
select @countSub=0
select @countError=0
select @countTotal=0

delete from scan_error

declare c_scan_import cursor for select barcode, location_id,
scan_time, scan_date
from scan_import for read only

open c_scan_import
/* get first record from scan_import */
fetch c_scan_import into @waarde1, @waarde2, @waarde3, @waarde4
while (@@fetch_status=0)
begin
/* check data for error's and process data */
select @waarde11=barcode,
@waarde12=location_id,
@waarde13=scan_date,
@waarde14=record_id
from scan_main where barcode=@waarde1

select @waarde21=location_id
from scan_locations where location_id=@waarde2

if (@waarde1=@waarde11 and @waarde2=@waarde21 and
(convert(varchar,@waarde4,120)>=convert(varchar,@w aarde13,120))
and
(convert(varchar,@waarde4,120)<convert(varchar,get date(),120)))
begin
/* copy old record to scan_sub (history) */
insert into scan_sub(main_record_id, location_id, scan_date,
scan_time)
select record_id,
location_id,
scan_date,
scan_time
from scan_main
where barcode=@waarde1
/* update old record in scan_main with new data from scan_import
*/
update scan_main
set location_id=@waarde2,
scan_date= convert(datetime,
/* date part */
substring(convert(varchar,@waarde4,105),7,4)+'-'+
substring(convert(varchar,@waarde4,105),4,2)+'-'+
substring(convert(varchar,@waarde4,105),1,2)+' '+
/* time part */
substring(convert(varchar,@waarde4,108),1,2)+':'+
substring(convert(varchar,@waarde4,108),4,2)+':'+
substring(convert(varchar,@waarde4,108),7,2)
,121),
scan_time= substring(convert(varchar,@waarde3,108),1,2)+':'+
substring(convert(varchar,@waarde3,108),4,2)+':'+
substring(convert(varchar,@waarde3,108),7,2)
where barcode=@waarde1
select @countMain=@countMain+1
end
else
if (@waarde1=@waarde11 and @waarde2=@waarde21 and
(convert(varchar,@waarde4,120)<convert(varchar,@wa arde13,120)))
begin
insert into scan_sub
(
main_record_id,
location_id,
scan_time,
scan_date
)
values
(
@waarde14,
@waarde2,
@waarde3,
@waarde4
)
select @countSub=@countSub+1
end
else
begin
insert into scan_error
(
barcode,
location_id,
scan_time,
scan_date
)
values
(
@waarde1,
@waarde2,
@waarde3,
@waarde4
)
select @countError=@countError+1
end

/* get next record in scan_import for processing */
fetch c_scan_import into @waarde1, @waarde2, @waarde3, @waarde4
end
close c_scan_import
deallocate c_scan_import

select @countTotal=@countMain+@countSub+@countError

delete from scan_result
insert into scan_result(description,result) (select 'in
SCAN_IMPORT',count(*) from scan_import)
insert into scan_result values('to SCAN_MAIN',@countMain)
insert into scan_result values('to SCAN_SUB',@countSub)
insert into scan_result values('to SCAN_ERROR',@countError)
insert into scan_result values('Total processed',@countTotal)
go

--------------------------------------------------------------------------

----

Please help...

Marc,
Database administrator,
:-)

Jul 20 '05 #4

P: n/a
Marc,

First of all, if your scan_result and scan_error tables are that small, just
forget TRUNCATE TABLE, there is no performance difference between delete and
truncate for such small tables.
Second, you have used cursors. In SQL you are usually dealing with
sets.Using a loop to go through all the records in a table and process them
one by one is not a set-based solution and is often (or may be always)
slower than processing all the records at once in a DML command.
For example if you want to change the salary of employees (let say 10%
raise) you can do it with one of these scripts:

-- 1. Using cursor --------------------------------
declare @EmpID int,
@Salary decimal(10,2)

declare EmpCrsr cursor for
select EmpID, Salary
from Employee

open EmpCrsr
fetch next from EmpCrsr into @EmpID, @Salary

while @@FETCH_STATUS = 0 begin
update Employee
set Salary = @Salary * 1.1
where current of EmpCrsr
fetch next from EmpCrsr into @EmpID, @Salary
end

close EmpCrsr
deallocate EmpCrsr
-- End of Script ----------------------------------

-- 2. Set-Based solution --------------------------
update Employee
set Salary = Salary * 1.1
-- End of Script ----------------------------------

Well, the set-based solution is not always that simple. It can be
complicated if you want to update a table based on data from other tables
and considering a variety of conditions.
Actually I haven't studied your problem carefully but it seems that you can
convert it so you don't need to use a cursor. In some cases we have to use a
cursor, like when you need to call a procedure for every single record in
your table, but your problem doesn't seem to be one of these cases.

Cheers,
Shervin

"deprins" <ma***@netlane.com> wrote in message
news:59*************************@posting.google.co m...
First of all thanks for your reply's. :-)

The table scan_main has 64.000 records, the table scan_import varies
in number of records. The table scan_result doesnt have more then 5
records. And the table scan_error doesnt have more records then max.
50.

But what do you mean by a set-based solution??? I dont understand what
you mean by that? Could you please explain that to me?

Many thanks in advance.

Marc.
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message

news:<vp************@corp.supernews.com>...
Marc,

As Kristofer mentioned in his post, you need to convert your stored
procedure to use a set-based solution. I just want to add a tiny little
thing to it. I don't know how big your tables are. If scan_error and
scan_result tables are big, and if you don't have a transaction to rollback after deleting records from these two tables, then you would better to use TRUNCATE TABLE <TableName> instead of DELETE FROM <TableName> to delete all the records. TRUNCATE is much faster than DELETE because it doesn't log
records, so be carfeul when using it.
But again, your main problem is that cursor.

Shervin

"deprins" <ma***@netlane.com> wrote in message
news:59*************************@posting.google.co m...
Hello,

I have wrote a stored procedure but its real slow. Its activated by a
button on web page but its takes to long to process and the web server
gives a timeout message after 5 minutes.

Is there anyway to speed up this stored procedure? What am I doing
wrong here?

--------------------------------------------------------------------------
----

if exists(select 1 from sysobjects where type='P' and
name='sp_scan_import')
begin
drop proc sp_scan_import
end
go
create procedure sp_scan_import
as
/* SCAN_IMPORT */
declare @waarde1 varchar(20) /* barcode */
declare @waarde2 varchar(6) /* location_id */
declare @waarde3 varchar(10) /* scan_time */
declare @waarde4 datetime /* scan_date */
/* SCAN_MAIN */
declare @waarde11 varchar(20) /* barcode */
declare @waarde12 varchar(6) /* location_id */
declare @waarde13 datetime /* scan_date */
declare @waarde14 int /* record_id */
/* SCAN_LOCATIONS */
declare @waarde21 varchar(6) /* location_id */
/* COUNTERS */
declare @countMain int /* counter records to scan_main */
declare @countSub int /* counter records to scan_sub */
declare @countError int /* counter records to scan_error */
declare @countTotal int /* Total processed records */

select @countMain=0
select @countSub=0
select @countError=0
select @countTotal=0

delete from scan_error

declare c_scan_import cursor for select barcode, location_id,
scan_time, scan_date
from scan_import for read only

open c_scan_import
/* get first record from scan_import */
fetch c_scan_import into @waarde1, @waarde2, @waarde3, @waarde4
while (@@fetch_status=0)
begin
/* check data for error's and process data */
select @waarde11=barcode,
@waarde12=location_id,
@waarde13=scan_date,
@waarde14=record_id
from scan_main where barcode=@waarde1

select @waarde21=location_id
from scan_locations where location_id=@waarde2

if (@waarde1=@waarde11 and @waarde2=@waarde21 and
(convert(varchar,@waarde4,120)>=convert(varchar,@w aarde13,120))
and
(convert(varchar,@waarde4,120)<convert(varchar,get date(),120)))
begin
/* copy old record to scan_sub (history) */
insert into scan_sub(main_record_id, location_id, scan_date,
scan_time)
select record_id,
location_id,
scan_date,
scan_time
from scan_main
where barcode=@waarde1
/* update old record in scan_main with new data from scan_import
*/
update scan_main
set location_id=@waarde2,
scan_date= convert(datetime,
/* date part */
substring(convert(varchar,@waarde4,105),7,4)+'-'+
substring(convert(varchar,@waarde4,105),4,2)+'-'+
substring(convert(varchar,@waarde4,105),1,2)+' '+
/* time part */
substring(convert(varchar,@waarde4,108),1,2)+':'+
substring(convert(varchar,@waarde4,108),4,2)+':'+
substring(convert(varchar,@waarde4,108),7,2)
,121),
scan_time= substring(convert(varchar,@waarde3,108),1,2)+':'+
substring(convert(varchar,@waarde3,108),4,2)+':'+
substring(convert(varchar,@waarde3,108),7,2)
where barcode=@waarde1
select @countMain=@countMain+1
end
else
if (@waarde1=@waarde11 and @waarde2=@waarde21 and
(convert(varchar,@waarde4,120)<convert(varchar,@wa arde13,120)))
begin
insert into scan_sub
(
main_record_id,
location_id,
scan_time,
scan_date
)
values
(
@waarde14,
@waarde2,
@waarde3,
@waarde4
)
select @countSub=@countSub+1
end
else
begin
insert into scan_error
(
barcode,
location_id,
scan_time,
scan_date
)
values
(
@waarde1,
@waarde2,
@waarde3,
@waarde4
)
select @countError=@countError+1
end

/* get next record in scan_import for processing */
fetch c_scan_import into @waarde1, @waarde2, @waarde3, @waarde4
end
close c_scan_import
deallocate c_scan_import

select @countTotal=@countMain+@countSub+@countError

delete from scan_result
insert into scan_result(description,result) (select 'in
SCAN_IMPORT',count(*) from scan_import)
insert into scan_result values('to SCAN_MAIN',@countMain)
insert into scan_result values('to SCAN_SUB',@countSub)
insert into scan_result values('to SCAN_ERROR',@countError)
insert into scan_result values('Total processed',@countTotal)
go

--------------------------------------------------------------------------
----

Please help...

Marc,
Database administrator,
:-)

Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.