SQL Server Performance Forum – Threads Archive

Does a Job execute MUCH faster???

Hi, Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes. However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs. Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that’s why I have this terrific improvement in terms of execution times. I’ll appreciate comments. Thanks

As far as I know, a dts scheduled by a job simply calls ‘dtsrun’ which is the same process used when you execute the dts manually. I dont know why the job would appear to be quicker, is it working with the same amount of data each time?

True and may be you can compare the execution process with PROFILER for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hmmm… that’s strange becuase the underlying data is exactly the same.

Let me explain the background of the process: A scheduled DTS package is executed by SQL Server Agent as a job. Because SQL Server Agent controls the underlying automation for scheduling, it must be running for any scheduled packages to execute. DTS is a client side application. DTS package when scheduled using SQL Agent as a job, it runs on the server. When you run the package interactively, then the package runs on the local machine. That is, you connect to a remote SQL Server using Enterprise Manager on workstation, the DTS packages you run will run on your workstation. This will result in slower package execution as the data flows over the network, between your local machine and the server. If you cannot log onto the server directly, then you need to make sure all the files, servers referenced in the DTS package are accessible from your machine as well. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya that clarifies things. The key sentence that clarified everything is pasted below. Many thanks once again satya <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> "…When you run the package interactively, then the package runs on the local machine. That is, you connect to a remote SQL Server using Enterprise Manager on workstation, the DTS packages you run will run on your workstation. "

]]>