Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-25 : 11:00:35
|
John writes "I am trying to move data between three databases on two servers, I have written an app to call the stored procedures in order. The problem is that these sp's run very slowly. The server has many sp's on it that run like you would expect them to - very quickely.
CREATE PROCEDURE sp_cartman_delete_show_prospect_channelside(@iproduct_id as int,@iphase as int,@iLocation as int) AS
declare @ishow_id as numeric
declare @cST as cursor
set @cST = cursor for
select distinct show_id from cartman.knowledgefactory_master.dbo.show where product_id = @iproduct_id and phase_num = @iphase and show_id <> 0
open @cST
fetch from @cST into
@ishow_id
while (@@fetch_status = 0)
begin
begin
if @iLocation = 1
delete from cartman.knowledgefactory.dbo.show_prospect where show_id = @ishow_id and product_id = @iproduct_id
end
begin
if @iLocation = 2
delete from templet.knowledgefactory.dbo.show_prospect where show_id = @ishow_id and product_id = @iproduct_id
end
fetch from @cST into
@ishow_id
end
close @cST
deallocate @cST
return
This sp will run and complete, eventually. It is a select from less that 100000 and will move or update 100 or less records. If I run just the query in the query analizer it completes quickely, If I run the entire sp in the query analizer it takes the same time as if it had been called. I am calling them from VB6 sp4, aginst SQL 7 sp3 on NT4 sp6. Any ideas???
John" |
|
|
|
|
|
Slow Stored Procedure using 4 part name