Really really slow cursor

microsoft.public.sqlserver.programming

20 Jan 2006 4:29 PM

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

20 Jan 2006 4:49 PM

Jim Underwood

Maybe one of the tables that you are updating is locked and the stored
procedure is waiting for the lock to free up?

Show quote

"Si" <S*@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
>

20 Jan 2006 5:15 PM

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

Show quote

"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" <S*@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
> >
>
>
>

20 Jan 2006 5:27 PM

Jim Underwood

So the 'after first fetch'
is never reached?

Show quote

"Si" <S*@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:
>
> > Maybe one of the tables that you are updating is locked and the stored
> > procedure is waiting for the lock to free up?
> >
> > "Si" <S*@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
> > >
> >
> >
> >

20 Jan 2006 4:52 PM

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/

20 Jan 2006 5:12 PM

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.

Show quote

"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/

20 Jan 2006 5:38 PM

David Portas

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 the
> 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
--

20 Jan 2006 5:59 PM

curse (n) - an unnecessary cursor

I like that. :) Maybe it should be added:
http://www.webster.com/dictionary/curse

ML

---
http://milambda.blogspot.com/

20 Jan 2006 9:11 PM

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.

Show quote

"Si" <S*@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:
>
>> 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/

23 Jan 2006 2:21 PM

Some re-thinking on the design and cursor no longer needed.

Thanks very much everyone for your time and replies.

Simon.

Show quote

"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 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
>

23 Jan 2006 4:20 PM

Always nice to see an unnecessary cursor crumble into the void. :)

ML

---
http://milambda.blogspot.com/