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
[quoted text, click to view] "Greg D. Moore (Strider)" wrote:
>
> "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
>
>
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
[quoted text, click to view] >-----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.
>
>raj
>
>
>"Greg D. Moore (Strider)" wrote:
>
>>
>> "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
>>
>>
>>
>.
[quoted text, click to view] "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.
[quoted text, click to view] >
> thanks
>
> raj
Don't see what you're looking for? Try a search.
