| Author |
Topic |
|
LEOx037
Starting Member
12 Posts |
 Posted - 2010-03-04 : 09:34:57
|
| Hello to all,I have a question regarding the use of a Stored Procedure vs. a DTS package...I currently have a DTS package setup to execute via a batch job but I'm seeing strange results so I'm not thinking about building the function/process in a Stored Procedure. Here's the issue:The batch job calls the DTS package and it executes 14 steps. Step 1: Delete data from a version table labeled 7.Step 2: Copy data from table 6 to table 7.Step 3: Delete data from table 6.Step 4: Copy data from table 5 to table 6.Step 5: Delete data from table 5.Step 6: Copy data from table 4 to table 5.Step 7: Delete data from table 4.Step 8: Copy data from table 3 to table 4.Step 9: Delete data from table 3.Step 10: Copy data from table 2 to table 3.Step 11: Delete data from table 2.Step 12: Copy data from table 1 to table 2.Step 13: Delete data from table 1.Step 14: Load new data from table 1.All the data is the same size about 80,000 rows. Yet, at various steps the process seems to low down. Most of the steps execute with in a sec but sometimes the step take about 600 seconds. Also, it seems the log files are showing that different task are executing before others. Which is not good. Not sure if this is just an error in the reporting into the log files.I can "see" why this happens. So I decided to build the process in a Stored Procedure. What do you all think?Should I leave it in DTS or should I build a Stored Procedure version? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-04 : 09:43:53
|
In DTS you have to use the workflow-arrows to manage the order of executing the steps.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-04 : 11:12:52
|
| i cant understand the actual reqmnt why you need to transfer data b/w tables. can you elaborate on why you need to do this?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
LEOx037
Starting Member
12 Posts |
Posted - 2010-03-05 : 14:38:25
|
| workflow arrows are being used to manage the order of executing the steps.The process is uses to maintain historical tables. The bottom line, my problem seems to be that the process slows down at the midway point and for some reason it takes too long on Saturdays. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-06 : 05:32:29
|
You could hold the data in only one table with an additional column to identify the data (maybe a date).But if you really need the 7 tables then think about renaming the tables rather than delete and insert.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-06 : 05:40:11
|
maybe like this:drop table table7goEXEC sp_rename 'table6', 'table7'goEXEC sp_rename 'table5', 'table6'goEXEC sp_rename 'table4', 'table5'goEXEC sp_rename 'table3', 'table4'goEXEC sp_rename 'table2', 'table3'goEXEC sp_rename 'table1', 'table2'gocreate table table1(column1 int, ...)goinsert newdata into table1
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
LEOx037
Starting Member
12 Posts |
Posted - 2010-03-15 : 09:31:18
|
| Thanks webfred,I never thought about just renaming the tables. A major "duh!" move on my part.I rebuilt the process, renaming the tables, and that seems to do the trick. The process of renaming ran much faster then moving the data around.Thanks! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-15 : 09:50:19
|
I'm glad it works for you 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
DTS vs. Stored Procedure