SQL Server Performance Forum – Threads Archive

DTS active-x script vs Stored Procedure

Hi all, I am using a DTS active-x vbscript to read data from database, process it and store to destination table. The total number of records that I will be processing will be more than 1,00,000. Normally it runs for 2 hours. But now since the total no. of records is growing it is taking more than 5 hours. I am opening the database connection and processing all the records and closing it. Whether there is any way to use disconnected records or some other methods to read the data and process the records. Whether the performance will be improved when we convert to a stored procedure and just call the stored procedure from the DTS package since it has to run periodically. Please let me know on the best solution for performance optimization. Please any body help in this issue. Thanks and Regards,
Ram Kumar N D

Yes the process into a stored procedure will have major difference in the performance, on the higher side.
As the connections from the DTS task will capture the resource on server, so it is better to control the process inside SQL using SPs. Make sure the Sp is compiled whenever it is modified.http://www.sqldts.com/default.aspx?234 fyi. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>