Re: Stored Procedures vs DTS vs Jobs
- From: "Rick Gittins" <rgittins@xxxxxxxxxxxxx>
- Date: Thu, 11 Aug 2005 15:12:33 -0500
A stored procedure is complied code meaning that SQL Server has already
found the optimal way of executing the SQL code. A stored procedure is
faster the running SQL code which is happening if all the code is placed in
a SQL Execute task.
As far as having all the code in one stored procedure: If you have the
code broken up to small stored procedures then the code is easier to debug
and have some else look at it and not get confused by the sheer length of
the sp.
Putting all the code in 1 SQL Execute task makes it hard to debug. When it
is scheduled to run and if it fails you know which steps fails and it is
much easier to track do what step actually failed instead of knowing all the
sp's failed.
Rick
"Mnemonic" <MICHAEL_SUNLIN@xxxxxxxxxxxxxxx> wrote in message
news:1123781385.519029.128810@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>I was curious about the associated pros and cons between DTS packages,
> SP's and Jobs (executing T-SQL directly). I am using SQL2000.
>
> Basically I prefer building everything into DTS packages, and using the
> workflow functionality to encourage parallel processing (Have a quad
> and another oct-processor server) so it can really chew through
> simultaneous calculations.
>
> I have a co-worker who writes stored procedures, calls them in a dts
> package (one sp per task), and then wants that scheduled.
> My issues with this is:
> Why not just write one stored procedure to call them all, and execute
> that one(no variables are being cast, and it's purely sequential
> slicing and dicing of data)?
> Why not have one SQL task within the DTS to execute them in order
> (instead of task1 then upon completion task2, etc)
> Also why not just call the stored procedures in the job schedule
> directly?
>
> It's pretty simple stuff, but they are claiming stored procedures are
> the ultimate. I understand it's a core functionality, but it's a lot of
> backtracking when I see a job has failed. (I.E. Check the job, then see
> the dts it runs, then check out the SQL tasks and any logging, followed
> by opening up the stored procedure listed in each step)
>
> Is it all semantics, any "best" practice?
>
> BOL states:
> "SQL Server 2000 and SQL Server version 7.0 incorporate a number of
> changes to statement processing that extend many of the performance
> benefits of stored procedures to all SQL statements. SQL Server 2000
> and SQL Server 7.0 do not save a partially compiled plan for stored
> procedures when they are created. A stored procedure is compiled at
> execution time, like any other Transact-SQL statement. SQL Server 2000
> and SQL Server 7.0 retain execution plans for all SQL statements in the
> procedure cache, not just stored procedure execution plans. The
> database engine uses an efficient algorithm for comparing new
> Transact-SQL statements with the Transact-SQL statements of existing
> execution plans. If the database engine determines that a new
> Transact-SQL statement matches the Transact-SQL statement of an
> existing execution plan, it reuses the plan. This reduces the relative
> performance benefit of precompiling stored procedures by extending
> execution plan reuse to all SQL statements."
>
> Does this mean stored procedures aren't all they are cracked up to be?
>
.
- Prev by Date: Re: dts schedules
- Next by Date: Re: Executing Crystal Reports thru DTS package
- Previous by thread: Scheduling Jobs 64-Bit
- Next by thread: Making sure the package is one transaction.
- Index(es):
Relevant Pages
|
