Really really slow cursor

I'm stumped on this.
My developers proc left to run all night consumes tons of cpu but does no
updates at all and I have to kill the connection in the morning.
The select in the cursor declaration returns 357 rows so not an outrageous
result set. When I run the select in Qry Analyser it runs in under 6 seconds
.
When I run the stored proc in QA it takes forever. Debug print statements
appear up until the initial fetch and then nothing.
Can a cursor loop indefinitely?
CREATE PROCEDURE dbo.stc_Insert_Instrument AS
set nocount on
declare
@.rc int -- returncode
, @.errmsg varchar(250)
, @.msg varchar(50)
select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
-- create temp tables
select
<Snip>
into #moodystaging
from moodystaging
select
<Snip>
into #spstaging
from spstaging
declare ins_cursor cursor read_only for
select
iss.IssuerId,
st.SECURITY_DES,
case isdate(st.maturity)
when 1 then st.maturity
else null
end as maturity,
case isdate(st.issue_dt)
when 1 then st.issue_dt
else null
end as issue_dt,
case isnumeric(st.cpn)
when 1 then st.cpn
else null
end as cpn,
cp.CouponTypeId,
st.CRNCY,
case isnumeric(st.AMT_ISSUED)
when 1 then st.AMT_ISSUED
else null
end as amt_issued,
case isnumeric(st.AMT_OUTSTANDING)
when 1 then st.AMT_OUTSTANDING
else null
end as amt_outstanding,
r.RatingId,
sprt.RatingId as spRatingId,
st.id_bb_unique,
st.id_isin,
st.id_cusip
from
staging st
left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
left join external e on st.id_bb_unique = e.externalid and
e.externaltype='BB'
left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
left join rating r on mt.rtg = r.rating and r.type = 'Moody'
left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
where
e.externalid is null
and iss.sourceid is not null
open ins_cursor
declare
@.issuerid int,
@.instrument_name varchar(50),
@.maturitydate datetime,
@.issuedate datetime,
@.coupon float(8),
@.coupontypeid int,
@.currency varchar(3),
@.amountissued float(8),
@.amountoutstanding float(8),
@.moodyratingid int,
@.spratingid int,
@.bb_id varchar(50),
@.isin varchar(50),
@.cusip varchar(50),
@.instrumentid int
fetch next from ins_cursor into
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid,
@.bb_id,
@.isin,
@.cusip
print 'after first fetch'
while @.@.fetch_status = 0
begin
print 'processing'
insert into instrument (
issuerid,
instrumentname,
maturitydate,
issuedate,
coupon,
coupontypeid,
currency,
amountissued,
amountoutstanding,
moodyratingid,
spratingid)
values (
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into Instrument failed.'
goto errhandler
end
set @.instrumentid = @.@.identity
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'BB', @.bb_id)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for BB type.'
goto errhandler
end
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'ISIN', @.isin)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for ISIN type.'
goto errhandler
end
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'Cusip', @.cusip)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for Cusip type.'
goto errhandler
end
fetch next from ins_cursor into
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid,
@.bb_id,
@.isin,
@.cusip
end
close ins_cursor
deallocate ins_cursor
drop table #moodystaging
drop table #spstaging
return 0 -- success
errhandler:
raiserror ('%s %s',16,1,@.errmsg,@.msg)
if @.@.trancount > 0
rollback transaction
drop table #moodystaging
drop table #spstaging
return @.rc
GOMaybe one of the tables that you are updating is locked and the stored
procedure is waiting for the lock to free up?
"Si" <Si@.discussions.microsoft.com> wrote in message
news:4A962893-F4D1-4BDA-814E-9DB0E891EA6F@.microsoft.com...
> I'm stumped on this.
> My developers proc left to run all night consumes tons of cpu but does no
> updates at all and I have to kill the connection in the morning.
> The select in the cursor declaration returns 357 rows so not an outrageous
> result set. When I run the select in Qry Analyser it runs in under 6
seconds.
> When I run the stored proc in QA it takes forever. Debug print statements
> appear up until the initial fetch and then nothing.
> Can a cursor loop indefinitely?
>
>
> CREATE PROCEDURE dbo.stc_Insert_Instrument AS
> set nocount on
> declare
> @.rc int -- returncode
> , @.errmsg varchar(250)
> , @.msg varchar(50)
>
> select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
> -- create temp tables
> select
> <Snip>
> into #moodystaging
> from moodystaging
>
> select
> <Snip>
> into #spstaging
> from spstaging
>
> declare ins_cursor cursor read_only for
> select
> iss.IssuerId,
> st.SECURITY_DES,
> case isdate(st.maturity)
> when 1 then st.maturity
> else null
> end as maturity,
> case isdate(st.issue_dt)
> when 1 then st.issue_dt
> else null
> end as issue_dt,
> case isnumeric(st.cpn)
> when 1 then st.cpn
> else null
> end as cpn,
> cp.CouponTypeId,
> st.CRNCY,
> case isnumeric(st.AMT_ISSUED)
> when 1 then st.AMT_ISSUED
> else null
> end as amt_issued,
> case isnumeric(st.AMT_OUTSTANDING)
> when 1 then st.AMT_OUTSTANDING
> else null
> end as amt_outstanding,
> r.RatingId,
> sprt.RatingId as spRatingId,
> st.id_bb_unique,
> st.id_isin,
> st.id_cusip
> from
> staging st
> left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
> INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
> left join external e on st.id_bb_unique = e.externalid and
> e.externaltype='BB'
> left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
> left join rating r on mt.rtg = r.rating and r.type = 'Moody'
> left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
> left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
> where
> e.externalid is null
> and iss.sourceid is not null
>
> open ins_cursor
> declare
> @.issuerid int,
> @.instrument_name varchar(50),
> @.maturitydate datetime,
> @.issuedate datetime,
> @.coupon float(8),
> @.coupontypeid int,
> @.currency varchar(3),
> @.amountissued float(8),
> @.amountoutstanding float(8),
> @.moodyratingid int,
> @.spratingid int,
> @.bb_id varchar(50),
> @.isin varchar(50),
> @.cusip varchar(50),
> @.instrumentid int
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> print 'after first fetch'
> while @.@.fetch_status = 0
> begin
> print 'processing'
> insert into instrument (
> issuerid,
> instrumentname,
> maturitydate,
> issuedate,
> coupon,
> coupontypeid,
> currency,
> amountissued,
> amountoutstanding,
> moodyratingid,
> spratingid)
> values (
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into Instrument failed.'
> goto errhandler
> end
> set @.instrumentid = @.@.identity
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'BB', @.bb_id)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for BB type.'
> goto errhandler
> end
>
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'ISIN', @.isin)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for ISIN type.'
> goto errhandler
> end
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'Cusip', @.cusip)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for Cusip type.'
> goto errhandler
> end
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> end
> close ins_cursor
> deallocate ins_cursor
> drop table #moodystaging
> drop table #spstaging
> return 0 -- success
> errhandler:
> raiserror ('%s %s',16,1,@.errmsg,@.msg)
> if @.@.trancount > 0
> rollback transaction
> drop table #moodystaging
> drop table #spstaging
> return @.rc
> GO
>|||One thing is clear - this can be done without a cursor. Or have I missed
something?
What else is going on in there during the night?
ML
http://milambda.blogspot.com/|||The reason for the cursor was to extract the identity value from the
instrument table to enter it into the external table. (Sorry, I didn't
include table definitions)
I'd be very happy if there isanother way to achieve this and get rid of the
curse, I mean cursor!
There is nothing else going on overnight apart from backups.
"ML" wrote:

> One thing is clear - this can be done without a cursor. Or have I missed
> something?
> What else is going on in there during the night?
>
> ML
> --
> http://milambda.blogspot.com/|||Thanks Jim,
I don't think this is the case but I'll double check.
Progress seems to halt before then, right after the initial fetch.
Simon
"Jim Underwood" wrote:

> Maybe one of the tables that you are updating is locked and the stored
> procedure is waiting for the lock to free up?
> "Si" <Si@.discussions.microsoft.com> wrote in message
> news:4A962893-F4D1-4BDA-814E-9DB0E891EA6F@.microsoft.com...
> seconds.
>
>|||So the 'after first fetch'
is never reached?
"Si" <Si@.discussions.microsoft.com> wrote in message
news:E15851B6-2879-453D-8F01-C96375BF7A1C@.microsoft.com...
> Thanks Jim,
> I don't think this is the case but I'll double check.
> Progress seems to halt before then, right after the initial fetch.
> Simon
>
> "Jim Underwood" wrote:
>
no
outrageous
statements|||Si wrote:

> The reason for the cursor was to extract the identity value from the
> instrument table to enter it into the external table. (Sorry, I didn't
> include table definitions)
> I'd be very happy if there isanother way to achieve this and get rid of th
e
> curse, I mean cursor!
>
At least three possible solutions that don't need a cursor.
1. Create an insert trigger on the Instrument table to populate
External.
2. Use a table variable or temp table (SQL Server 2000):
DECLARE @.instrument TABLE ...;
INSERT INTO @.instrument (...)
SELECT ...
FROM staging ...;
INSERT INTO instrument (...)
SELECT ...
FROM @.instrument;
INSERT INTO external
(instrumentid, ...)
SELECT I.instrumentid, ...
FROM instrument AS T
LEFT JOIN @.instrument AS I
ON ... etc
3. Use a table variable and INSERT ... OUTPUT (SQL Server 2005):
DECLARE @.instrument (instrumentid INTEGER);
INSERT INTO instrument (...)
OUTPUT Inserted.instrumentid INTO @.instrument
SELECT ...
FROM staging ...;
INSERT INTO external
(instrumentid, ...)
SELECT instrumentid, ...
FROM @.instrument;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||curse (n) - an unnecessary cursor
I like that. :) Maybe it should be added:
http://www.webster.com/dictionary/curse
ML
http://milambda.blogspot.com/|||You don't need a cursor for a select.. insert..
insert into TableA( a, b, c )
select a, b, c from TableB
Meanwhile, the next time it seems to freeze, use the procedure sp_who2 to
determine if the process is blocked.
"Si" <Si@.discussions.microsoft.com> wrote in message
news:717A26A2-8862-49D0-A78D-44879CD2FA3B@.microsoft.com...
> The reason for the cursor was to extract the identity value from the
> instrument table to enter it into the external table. (Sorry, I didn't
> include table definitions)
> I'd be very happy if there isanother way to achieve this and get rid of
> the
> curse, I mean cursor!
>
> There is nothing else going on overnight apart from backups.
> "ML" wrote:
>|||Some re-thinking on the design and cursor no longer needed.
Thanks very much everyone for your time and replies.
Simon.
"Si" wrote:

> I'm stumped on this.
> My developers proc left to run all night consumes tons of cpu but does no
> updates at all and I have to kill the connection in the morning.
> The select in the cursor declaration returns 357 rows so not an outrageous
> result set. When I run the select in Qry Analyser it runs in under 6 secon
ds.
> When I run the stored proc in QA it takes forever. Debug print statements
> appear up until the initial fetch and then nothing.
> Can a cursor loop indefinitely?
>
>
> CREATE PROCEDURE dbo.stc_Insert_Instrument AS
> set nocount on
> declare
> @.rc int -- returncode
> , @.errmsg varchar(250)
> , @.msg varchar(50)
>
> select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
> -- create temp tables
> select
> <Snip>
> into #moodystaging
> from moodystaging
>
> select
> <Snip>
> into #spstaging
> from spstaging
>
> declare ins_cursor cursor read_only for
> select
> iss.IssuerId,
> st.SECURITY_DES,
> case isdate(st.maturity)
> when 1 then st.maturity
> else null
> end as maturity,
> case isdate(st.issue_dt)
> when 1 then st.issue_dt
> else null
> end as issue_dt,
> case isnumeric(st.cpn)
> when 1 then st.cpn
> else null
> end as cpn,
> cp.CouponTypeId,
> st.CRNCY,
> case isnumeric(st.AMT_ISSUED)
> when 1 then st.AMT_ISSUED
> else null
> end as amt_issued,
> case isnumeric(st.AMT_OUTSTANDING)
> when 1 then st.AMT_OUTSTANDING
> else null
> end as amt_outstanding,
> r.RatingId,
> sprt.RatingId as spRatingId,
> st.id_bb_unique,
> st.id_isin,
> st.id_cusip
> from
> staging st
> left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
> INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
> left join external e on st.id_bb_unique = e.externalid and
> e.externaltype='BB'
> left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
> left join rating r on mt.rtg = r.rating and r.type = 'Moody'
> left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
> left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
> where
> e.externalid is null
> and iss.sourceid is not null
>
> open ins_cursor
> declare
> @.issuerid int,
> @.instrument_name varchar(50),
> @.maturitydate datetime,
> @.issuedate datetime,
> @.coupon float(8),
> @.coupontypeid int,
> @.currency varchar(3),
> @.amountissued float(8),
> @.amountoutstanding float(8),
> @.moodyratingid int,
> @.spratingid int,
> @.bb_id varchar(50),
> @.isin varchar(50),
> @.cusip varchar(50),
> @.instrumentid int
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> print 'after first fetch'
> while @.@.fetch_status = 0
> begin
> print 'processing'
> insert into instrument (
> issuerid,
> instrumentname,
> maturitydate,
> issuedate,
> coupon,
> coupontypeid,
> currency,
> amountissued,
> amountoutstanding,
> moodyratingid,
> spratingid)
> values (
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into Instrument failed.'
> goto errhandler
> end
> set @.instrumentid = @.@.identity
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'BB', @.bb_id)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for BB type.'
> goto errhandler
> end
>
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'ISIN', @.isin)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for ISIN type.'
> goto errhandler
> end
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'Cusip', @.cusip)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for Cusip type.'
> goto errhandler
> end
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> end
> close ins_cursor
> deallocate ins_cursor
> drop table #moodystaging
> drop table #spstaging
> return 0 -- success
> errhandler:
> raiserror ('%s %s',16,1,@.errmsg,@.msg)
> if @.@.trancount > 0
> rollback transaction
> drop table #moodystaging
> drop table #spstaging
> return @.rc
> GO
>