How to run my Curser more faster?

I have a stored procedure written for number of updates, inserts to the
database. This stored procedure takes too long to run. I have created few
indexes around few tables, but the response time is not that defferent.
THere is a Curser "CURSOR FAST_FORWARD FOR" in the stored procedure, and
another stored procedure is called within this Curser. I found that lot of
time been taken to run this Curser. So I want to run/make it faster the
curser. So how should I improve the Curser to run more faster
I would greatly appreciate anyone's idea or suggesions.
thanks
raj
"raj" <raj@.discussions.microsoft.com> wrote in message
news:618E892D-9327-41F8-8160-3E84EE32303A@.microsoft.com...
> I have a stored procedure written for number of updates, inserts to the
> database. This stored procedure takes too long to run. I have created few
> indexes around few tables, but the response time is not that defferent.
> THere is a Curser "CURSOR FAST_FORWARD FOR" in the stored procedure, and
> another stored procedure is called within this Curser. I found that lot of
> time been taken to run this Curser. So I want to run/make it faster the
> curser. So how should I improve the Curser to run more faster
> I would greatly appreciate anyone's idea or suggesions.
Best Advice: Eliminate the Cursor.
But, without a DDL and some sample table folks here won't be able to help
you with that.
So, if you can, post your DDL and some data and see what folks can do.

> thanks
> raj
|||this is the part of the query takes too long to run
BEGIN
DECLARE CustCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT D.CustID FROM tblInv D INNER JOIN #Invoice I ON D.InvID =
I.InvID
OPEN CustCursor
FETCH NEXT FROM CustCursor INTO @.CustID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
EXEC sp_UpdateDunning @.CustID
IF @.@.Error <> 0
BEGIN
CLOSE CustCursor
DEALLOCATE CustCursor
ROLLBACK
RAISERROR('Error executing - > sp_UpdateDunning.', 16, 1)
RETURN
END
FETCH NEXT FROM CustCursor INTO @.CustID
END
CLOSE CustCursor
DEALLOCATE CustCursor
END
All the tables involved in this curser, indexes have been created.
raj
"Greg D. Moore (Strider)" wrote:

> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:618E892D-9327-41F8-8160-3E84EE32303A@.microsoft.com...
> Best Advice: Eliminate the Cursor.
> But, without a DDL and some sample table folks here won't be able to help
> you with that.
> So, if you can, post your DDL and some data and see what folks can do.
>
>
>
|||The following approach would sacrify the transactional
opertion on the batch, but is likely the fast.
-- ===============
-- Run the script at QA
-- ===============
declare @.CustID int, @.cmd varchar(255)
delcare tmp_cur cursor for
select distinct D.CustID
from tblInv D inner join #Invoice I
on D.InvID = I.InvID
open tmp_cur
fetch next from tmp_cur into @.CustID
while @.@.fetch_status = 0
begin
select @.cmd = 'exec sp_UpdateDunning ' + convert
(varchar, @.CustID)
print @.cmd
print 'go'
fetch next from tmp_cur into @.CustID
end
close tmp_cur
deallocate tmp_cur
-- ===============
-- Run the result of script at QA
-- ===============
For example:
exec sp_UpdateDunning 1
go
exec sp_UpdateDunning 2
go
...
^^
Regards,
Alfred

>--Original Message--
>this is the part of the query takes too long to run
>----
>BEGIN
>DECLARE CustCursor CURSOR FAST_FORWARD FOR
>SELECT DISTINCT D.CustID FROM tblInv D
INNER JOIN #Invoice I ON D.InvID =
>I.InvID
>OPEN CustCursor
>FETCH NEXT FROM CustCursor INTO @.CustID
>WHILE (@.@.FETCH_STATUS <> -1)
>BEGIN
>EXEC sp_UpdateDunning @.CustID
>IF @.@.Error <> 0
>BEGIN
>CLOSE CustCursor
>DEALLOCATE CustCursor
>ROLLBACK
>RAISERROR('Error executing - >
sp_UpdateDunning.', 16, 1)
>RETURN
>END
>FETCH NEXT FROM CustCursor INTO @.CustID

>END
>CLOSE CustCursor
>DEALLOCATE CustCursor
>END
>----
--
>All the tables involved in this curser, indexes have been
created.[vbcol=seagreen]
>raj
>
>"Greg D. Moore (Strider)" wrote:
3E84EE32303A@.microsoft.com...[vbcol=seagreen]
updates, inserts to the[vbcol=seagreen]
run. I have created few[vbcol=seagreen]
not that defferent.[vbcol=seagreen]
stored procedure, and[vbcol=seagreen]
Curser. I found that lot of[vbcol=seagreen]
run/make it faster the[vbcol=seagreen]
more faster[vbcol=seagreen]
suggesions.[vbcol=seagreen]
won't be able to help[vbcol=seagreen]
what folks can do.
>.
>