oshanahan (Us************@dbForumz.com) writes:
I'm using DTS to populate a staging table with raw streams of data (a
lot of it) from one source. I thought about imbedding the SQL
somewhere in the VB script that DTS uses. Our resident DTS man said
he didn't think that was possible here.
The problem essentially is that a large field on the staging table
must be substringed to populate other tables, which have no
relationship to the staging table. The substringing is where the
performance drag is, but there is no way around that. I'm looking to
somehow shave a little time on each populate transaction to help cut
down processing.
Had you been on SQL 2005, you could have written an user-defined
function in C# of VB to decode this large field. It is not unlikely
that that would be faster than SQL builtins.
Using cursor to load handle one by one is definitely not a good idea.
What sometimes can be a good idea is to do, say, 10000 at a time. Batching
can be achieved with SET ROWCOUNT or TOP, but also be achieved by using
ranges in the source data. Important here is that the selection of a
batch follows a clustered index, or else the selection itself will kill it.
But this is more of interest if you get problems with the transaction
log. When the problem is with decoding a field, I don't think batching
is going to help you much. Do you need to have the data in the table
when you substring the field? Can't you substring the field before you
load it into the database? Doing the substringing in T-SQL is not the
most optimal for performance.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
