DTS - Just Wondering ???

[JayKusch]

Afternoon Folk.

Wanted to see if anyone has an answer to a DTS question I have. Here is The scenerio: I have a script that loads 7.5 million records into a fact table. When i run the script thru Query Analyzer(QA), it takes 33 minutes to run. If i take the same script and use it in a DTS "Execute SQL Task" and then run the DTS package, it finishes in under 17 minutes. Almost 1/2 the time. Can anyone explain to me the performance gain i see when using a DTS package over running the script interactively in QA?
Thanks

J. Kusch

 

[sguslan]

Have no answer, but it would be helpful if you post the QA script, as there are many ways to load data into SQL server... Moreover, if you run DTS on the server itself, that cuts down any networking time, if there is any when running QA.

 

[foxdev]

DTS packages run on the machine executing them, which may or may not be the server. Differences in processing power, available memory, other tasks, etc. might explain it. --------------

http://www.lowcostprints.com

 

[MikePP]

Hi

It may be because your script is writing lots of messages to the window in SQL Server Query Analyzer.

If this is the reason, you can reduce the number of messages by select Query|Current Connection ... and ticking the No Count box.

Regards

Sadcow

 

[JayKusch]

I am happy w/ the results and the script is very straight forward. My source tables for the load are on remote sites, thus i have the script running on the server that is housing our data warehouse. I just wondered why there was such a performance diffrence between the two methods used. Thanks for the help! Thanks

J. Kusch

 

[JayKusch]

As an FYI ... i am running the script, in DTS and QA from the same server. No other processes, besides system, are running. Thus the two tests runs have the same, more or less, resources to pull from. I will however "tick" the nocount just to see if that is part of the diffrence. Seems to me that may be a part of it. I will relay my findings later. Thanks once again for all the great comments and suggestions. Thanks

J. Kusch

 

[JayKusch]

No Count was the issue. when disabled, i am within 40 seconds of each method used. Thanks

J. Kusch