How to increase package performance?

Question

Post reply

How to increase package performance?

Mark_maddison

Right there with Babe

Points: 768
More actions
October 9, 2003 at 9:49 am

#107210

We have a DTS importing a rather straight .txt file of large size and need the best speed possible.
Anyone have insight on speeding up a DTS?
THanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

BCP is much fast to import text file comparing to DTS.

Mark_maddison Right there with Babe Points: 768 More actions · Answer 2

True true.

But we have to add in a few other columns of data as it is loading. I believe I am unable to manipulate the data as it is BCPing, is this true?

I initially thought of BCP but found I had to throw in some other values off the text file. Is there a way I don't know about?

Thanks

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Your are right on BCP. I am wondering you could import the text file first into temp table, manipulate the data and insert into destination tables. It could be fast or slow. You may try that.

Mark_maddison Right there with Babe Points: 768 More actions · Answer 4

Worthy of a try,

but problem is we might have up to 20 people hammering on this thing at a time trying to put files in.

Any issues with concurrency?

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

quote:
problem is we might have up to 20 people hammering on this thing at a time trying to put files in.

Don't quite understand. You may explain more?

Mark_maddison Right there with Babe Points: 768 More actions · Answer 6

Will have a Web front end enabling people to DTS text files to table. DTS Pack is being called from a stored proc.

philcart SSC-Forever Points: 47796 More actions · Answer 7

Don't use BCP, use BULK INSERT. Load the table in a staging table first. Make sure you check out "Optimizing Bulk Copy Performance" in BOL.

Then use stored procedures to manipulate the data. This will allow you more control over the inserts into the destination table. You can do it in defined batchs or by ID code, etc...

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

SeekQuel SSCommitted Points: 1818 More actions · Answer 8

Staging tables without indexes.

After load, transfer data into permanent tables. Build indexes on staging tables AFTER load should you have performance problems with full table scans.

Regards,

Geert

Mark_maddison Right there with Babe Points: 768 More actions · Answer 9

Current Proccess:

1) Users upload text file via asp.

2) DTS package is triggered via proc to dts file.

3) File Header info is inserted to tHeaderTable and @@IDENTIRY returned

4) Meat of text file is inserted into tDataTable with @@IDENTITY captured previously. Tables there after have a 1 to many relation.

* We could have potentially a dozen or more people submitting their file at a time, so data integrity and concurrency are concerns.

In this scenario is it worthy and data sound to use the proprosed Staging tables?

Any other way around this?

Thanks

sqlSushi Mr or Mrs. 500 Points: 516 More actions · Answer 10

Don't quite understand them myself but a friend mentioned a Lookup query could be helpful in this case.

Not sure how though.

philcart SSC-Forever Points: 47796 More actions · Answer 11

Yes, I'd still use staging tables. Instead of using @@IDENTITY use SCOPE_IDENTITY(). Check BOL, @@IDENTITY returns the last identity created across any scope in the current session, SCOPE_IDENTITY() relates only to the same stored procedure, function, or batch.

Also, don't use IDENTITY on your destination tables.

The process would go like this

Insert record into staging header table

Insert records into staging detail table

Insert records into tHeaderTable

Insert records into tDataTable

remove records from staging detail table

remove records from staging header table

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Mark_maddison Right there with Babe Points: 768 More actions · Answer 12

Thanks Phillcart,

I am almost there.

There is just one concept I am having trouble grasping:

Insert record into staging header table

Not a problem. Pull ID created via scope_identity().

Insert records into staging detail table

I may have 12 or more people sending data at one time.

Say I am using Bulk Insert for the meat of the data, which only allows insertion of the columns it finds in the Data Source (Text file).

2 users hit process at same time, User A had 10k of records, User B had 6k.

We now have 16k of records in the Staging details table, how does user A's 10k of records know it is link to the User A's header portion?

I guess basically my question is how do I maintain the relationship between the 2 tables when Bulk insert does not allow me to tag the rows being DTSed into the data meat table?

If I can understand that, then I got it!

Thank you

philcart SSC-Forever Points: 47796 More actions · Answer 13

So there's no relationship between the header and detail other than the IDENTITY?

Maybe you can add the current user and datetime to the tables. You can use a format file to specify the file format which will allow you to import into a table with more columns than the data file.

Then you can use the username and datetime to link the header and detail.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Mark_maddison Right there with Babe Points: 768 More actions · Answer 14

*****************************

Maybe you can add the current user and datetime to the tables.

*****************************

I would like to do something along this line, and I guess username, datetime data must be entered to the data Table in one of two ways:

After BULK INSERT has occured:

If I have multiple people DTSing data into 1 staging table,not a temp table, I see no way to tag one set of data with appropriate userName due to the fact that I can not tell where one user's block starts and finishes if two people have entered data at the same time.

During BULK INSERT:

I would then have to dynamically write the Format File each time on the fly to include the userName since the user name is not inside the text file being Bulk inserted?

Thanks for your patients!