sql server dts: using sql 2k!

I run a DTS job every night to load a table called "MISC" that brings
over 30 millions records in the database A in server A. The job is
taking over 5 hours. The probelm is the # of records are keep
increasing. I am trying to minimizing the total execution time. Is that
all possible?

This is what I have:
The job truncates the table "MISC" first, drop indexes, do import (thru
DTS) and recreate indexes.

The DTS package has two data pumps and this is the sequence:

serverB------> server A --------->(complete(blue)--------> server
C-------> server A

please note: server B and C are source servers (sql servers 2k). server
A is a destination sever. I am using "complete" workflow when the
import from B to A completes. (can not do parallel bc the destination
table is same)

The "fast load" and "tab lock" options are turned on but I am not using
"batch size" option. currently it is set to 0. should I use it (inset a
value in it) for improving performance? how does it work? what are other
ways to minimize the total execution time? in the database properties,
the "autoshrink" option is off and the recovery model is set to
"simple".

thanks for your help!







*** Sent via Developersdex http://www.developersdex.com ***

Allan,

Thanks for your reply!

I understand 2 data pumps tasks you described but doesn't there is
"completion" precedence in between them. I mean doesn't server C to A
task has to complete first before server B to C start?. If not, then it
would be parallel inst it? would it be possible in my situation when
both tasks are writing to a same table? (if it goes parallel, that would
be great)

I'll also try with turning 'fast load' option off to see how it goes. I
am already complying these:
1. Setting the DB to Simple recovery
2. Make sure there are no indexes on the destination
3. Make sure there are no triggers on the destination.

Ideally, I like to identify the records that have changed and move only
those without truncating the table and loads it. thats the best option.
but I've never done anything like this (using triggers) before. is there
any example out there?

thanks for your help!!!





*** Sent via Developersdex http://www.developersdex.com ***

OK You can multi load a table

You need 4 connections

2 - Server A (1 and 2)
1 - Server B
1 - Server C

Two Datapump tasks

1. Server B - Server A (1)
2. Server C - Server A (2)

If you have fast load on you will note that the two pumps take it in turns
to load the table in Server A. If you take this off then the row counts for
transfer seem to go up in parallel.

You can try

1. Setting the DB to Simple recovery
2. Make sure there are no indexes on the destination
3. Make sure there are no triggers on the destination.

30 million records is still an awful lot of rows to load.

A better option if you can do it is to identify the records that have
changed and move only those. You can do this through triggers on the source
table.



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

[quoted text, click to view]

My example was in parallel. I was loading the destination using two sources
at the same time. I simply had two destination connections pinting to the
same Server/DB combo.

Fast load off was simply so that it doesn't lock up the table (try both
scenarios and watch the row counts)

Trigger examples. We can walk through them here if you want. I personally
use this method to load my DW. I then use a Data Driven Query task to apply
my changes (Note to Self: - Must write that article)

Let's create a simple table

CREATE TABLE MySource(colPK INT Identity(1,1) PRIMARY KEY, colOther
varchar(20))
GO
CREATE TABLE LogTable(ColPK int , colOther varchar(20), ActionType CHAR(1)
CHECK(ActionType IN('I','D','U')))
GO
CREATE TRIGGER tr_CaptureChanges_i ON MySource
FOR INSERT
AS
INSERT LogTable(ColPK, ColOther, ActionType)
SELECT ColPK, ColOther, 'I' FROM INSERTED
GO
CREATE TRIGGER tr_CaptureChanges_u ON MySource
FOR UPDATE
AS
INSERT LogTable(ColPK, ColOther, ActionType)
SELECT ColPK, ColOther, 'U' FROM INSERTED
GO
CREATE TRIGGER tr_CaptureChanges_d ON MySource
FOR DELETE
AS
INSERT LogTable(ColPK, ColOther, ActionType)
SELECT ColPK, ColOther, 'D' FROM DELETED
GO
INSERT MySource(ColOther) VALUES('SQL Server Rocks')
GO
INSERT MySource(ColOther) VALUES('.Net is cool')
GO
UPDATE MySource SET ColOther = 'C# is coolest' WHERE ColOther = '.Net is
cool'
GO
DELETE FROM MySource WHERE ColOther = 'C# is coolest'
GO
SELECT * FROM LogTable


You now have all the information you need to match against your destination.

Using a DDQ you can specify a DELETE statement where the ActionType = 'D',
an UPDATE where the ActionType = 'U' and an INSERT where the ActionType =
'I'

Have a read on the DDQ.


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

[quoted text, click to view]

Thank you very much for your help, Allan. THANX!!!







*** Sent via Developersdex http://www.developersdex.com ***