DTS performance and ActiveX Component

[ehague]

I have a DTS package where I am deleting data in a table and then transferring about 80,000 rows over to the empty table. I wound up writing most of it in VBScript with the ActiveX component. The proccess seems to be taking longer than I would like. Would dragging over the connection icons and transferring the data be faster than using VBScript to transfer the data?

Thanks- Elizabeth

 

[tlbroadbent]

1) Are you deleting all rows in the table with a Delete statement or Truncate Table? If the job runs under a system admin or db owner accout, use Truncate table. It is non-logged and much faster than Delete table_name.

2) Have you set the Use Fast Load option on the Transform Data Task? Have you set the Table Lock property?

3) Are you doing transformations or verifications in the VB script? If not you can use a Bulk Insert Task rather than a Transform Data Task in SQL 2000.

4) Try dropping indexes after truncating the tables. Then rebuild the indexes after the import. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ehague]

Wow! you are full of great suggestions. I am using the truncate table option. It is the transfer of data that is taking so long. by the way, i am transfering from a flatfile to an as400. why would i want to set the table lock option? all my transformations right now are through vbscript. i can change that. if i use the bulk insert task can i set the use fast load option? what does that option do?

Thanks-

Elizabeth

 

[tlbroadbent]

You Can't do Bulk Insert Task to any destination except SQL Server. Also, the Fast Load and Table Lock options only apply when loading to SQL Server. Fast Load only applies to the Data Transformation task. Table Lock causes SQL Server to lock the table immediately while loading to avoid the cost and time of page locks.

I assumed you were loading to SQL Server so my reply was based on that assumption. I'm afraid I don't have any suggestions for your particular scenario. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.