Regarding to DTS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Regarding to DTS

I have to transform quite a few tables (40+) from sqlA to B (tables are
with the same structure between servers) with some condition (basically,
some where clause, not all rows). I like the UI provided by DTS, plus
a lot of nice things such as batch copying, transaction, etc. But
in the designer, draw 40+ data pump lines between two connections makes
me dizzy. It’s almost a mess. Is there any good ways to avoid doing this?
If for each tables, I create two connections (A and B), will the performance
worse then all tables share the same two connections? BTW, from profiler, when I transform one table from A to B, such as
select * from table1 where user_id = ? The profiler for A shows that before getting the data, it runs an extra
query:
select user_id from table1 The profiler for B also runs extra query except for bulk insert:
select * from table1 I am a little bit confused. Why SQL server have to do something extra,
and if the table1 is big in both servers, the performance will definately
be affected.

Do you have any index or primary key defined on user_id, if so its a selection of field before executing the query and in any case it will not have any performance affect on the process.
In DTS why not make only one transformation and under the query define your own conditions to run the process. HTH
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

for performance use to seperate connections also might want to consider a straight sql approach using linked servers anytime you use the DTS transformation task you occur the overhead of COM a SELECT will always be faster assuming no actual transformations just a filter criteria espcialy if you have an idenx on the filter critteria
Yes, I do have the index on the user_id. Does it require to select
the user_id from the table before transferring data? Can I define multiple tables in the query for one transformation?
Such as
select * from table1 where user_id = ?
select * from table2 where user_id = ?

select * from table40 where user_id= ? I don’t think it will work, will it?
quote:Originally posted by satya Do you have any index or primary key defined on user_id, if so its a selection of field before executing the query and in any case it will not have any performance affect on the process.
In DTS why not make only one transformation and under the query define your own conditions to run the process. HTH
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

In the DTS wizard using the QUERY option you can define the set of TSQL statements. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

A T-sql View or table valued function might also be helpful from a maintainability Standpoint
]]>