I have 4 dts's which transfer data from SQL Server (2000 SP4) to Oracle
8.1.4.7

The distance is large (Europe to Asia) so timing isn't great. However one of
the DTS's runs extremely slow, compared to the others for no reason that I
can see.

Each of the 4 dts's transfer roughly 5-200 rows, and 3 out of the 4 dts's
take around 1-2 minutes. However the 4th dts takes AT LEAST 1 hour to run,
even if a very small number of rows are being sent. The timing doesnt' seem
to change at all whether its sending 2 or 200 rows, so it seems to be the
actual connection rather than the row insert taking the time.

The slow DTS has been on the gradual increase since it started - so there is
no major "spike" in the timings. Initially the same amount of data would
insert in around 4-5 minutes, and its slowly (over about 1 year) gone up to
1 hour - with no change in the number of rows being inserted.

Has anyone eny insights or thoughts?

Many Thanks, Ben

Does sound strange.

Couple of questions

Are all the DTS packages using the same Provider?
The same connection?
Are they going to the same server?

What about copying a file or using a different medium to transfer the
data using the same connection means/driver?

Allan



"Ben Rum" <bundyrum75 (AT) yahoo (DOT) com> wrote

Ben Rum wrote:
Could you try transferring a similar amount of data over the same link
through an alternate means? Copy a file or FTP for example?

If all 4 DTS packages use the same link it is rather strange. Do some
basic tests on the network link first. Maybe transfer the data as a
file, and use the Oracle loader at the other end?


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

FTP was my first alternative, but I'm just curious as to the cause of the
delay.. I'd like to see if I can resolve this issue, and if not, then I'll
FTP the data instead.

In answer to Allan's questions in his post:

All using the EXACT same source server and destination server. Only
difference is the actual source tables and dest tables, but can't see how
that would affect anything.

The fact that it has gradually increased over time has me puzzled, the other
DTS's have been running for around 6 months-1 year and upload time has
remained fairly constant..

Actually.. The destination table may have some index's/triggers/unique
constraints which are being evaluated upon insert - as the number of rows in
the destination table has gradually increased, maybe it has to check more
rows for duplicates & so forth.. let me check that.. I might see if I can
get the DBA at the other end to empty out/archive the data in the
destination table.

rgds, Ben.

"Ben Rum" <bundyrum75 (AT) yahoo (DOT) com> wrote

The high rowcount on the destination table was causing the delays... 190K
rows already on the destination server, and thus taking along time to
evaluate constraints/keys etc.

Have truncated the rows on dest table & now inserting faster.