Long running DTS package

29 Dec 2005 3:28 PM

Patrice

Hello,

I have a DTS package that took 52 hours to run. Within the package is a
task that runs a sproc which is a procedure to calculate premiums recvd and
has to run through over 2 million records. The procedure uses a cursor to
move through the records. My question is - is it possible that there were
memory leaks in the server while the package ran (there are a lot of other
things that go on on that server) making some of the data incorrect and some
of it correct? Because, I can take a portion of the data (a months worth)
and run that same process and it works just fine. Any ideas would help
tremendously.

Thanks,
Patrice

29 Dec 2005 4:00 PM

Jeff A. Stucker

My goodness, that is a long-running process. I've had DTS packages going
through millions of records in a matter of 2-3 hours on an older machine.
There was some cursor activity in those processes as well. My guess is that
with some thoughtful redesign you can eliminate the cursor on a bunch of it.
(Can you precalculate some information and then handle the rest with joins
and aggregate functions?) That will make it easier to debug, as well as
run faster.

--
Cheers,

'('     Jeff A. Stucker
\
        Senior Consultant
        www.rapidigm.com

Show quote

"Patrice" <Patr***@discussions.microsoft.com> wrote in message
news:1EC80DAD-CC19-447F-B9B5-A344A5E417E5@microsoft.com...
> Hello,
>
> I have a DTS package that took 52 hours to run. Within the package is a
> task that runs a sproc which is a procedure to calculate premiums recvd
> and
> has to run through over 2 million records. The procedure uses a cursor to
> move through the records. My question is - is it possible that there were
> memory leaks in the server while the package ran (there are a lot of other
> things that go on on that server) making some of the data incorrect and
> some
> of it correct? Because, I can take a portion of the data (a months worth)
> and run that same process and it works just fine. Any ideas would help
> tremendously.
>
>
> Thanks,
> Patrice
>

29 Dec 2005 6:01 PM

jxstern

On Thu, 29 Dec 2005 07:28:02 -0800, "Patrice"
<Patr***@discussions.microsoft.com> wrote:

>Hello,
>
>I have a DTS package that took 52 hours to run. Within the package is a
>task that runs a sproc which is a procedure to calculate premiums recvd and
>has to run through over 2 million records. The procedure uses a cursor to
>move through the records. My question is - is it possible that there were
>memory leaks in the server while the package ran (there are a lot of other
>things that go on on that server) making some of the data incorrect and some
>of it correct? Because, I can take a portion of the data (a months worth)
>and run that same process and it works just fine. Any ideas would help
>tremendously.

Can you post the cursor code?

Please look into the forward_only and fast_forward options. When you
have a (default) dynamic cursor, it can scale very poorly.

Josh