Contents
- 1 What are the pros and cons of using stored procedures Sprocs or functions?
- 2 What are the advantages of using SSIS packages over stored procedures?
- 3 What is the difference between SQL and SSIS?
- 4 Are there any disadvantages to using a stored procedure?
- 5 When to use stored procedures in data flow tasks?
What are the pros and cons of using stored procedures Sprocs or functions?
Any data errors in handling stored procedures are not generated until runtime….Portability –
| Advantages | Disadvantages |
|---|---|
| It is faster. | It is difficult to debug. |
| It is pre-compiled. | Need expert developer, since difficult to write code. |
| It reduces network traffic. | It is database dependent. |
| It is reusable. | It is non-portable. |
What are the advantages of using SSIS packages over stored procedures?
First, it manages memory very efficiently, which can result in big performance improvements compared to T-SQL alone. Second, the graphical interface lets you build large, complex and reliable transforms much more easily than hand-crafted T-SQL.
What is the disadvantage of using stored procedures in MySQL?
MySQL Stored Procedure Disadvantages Difficult to debug − It is difficult to debug stored procedures. Difficult to maintain − It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required a specialized skill set that not all application developers possess.
Why we should not use stored procedures?
Stored procedures are difficult to unit test. With an ORM, you can mock your database code so as to be able to test your business logic quickly. With stored procedures, you have to rebuild an entire test database from scratch. Stored procedures offer no performance advantage whatsoever.
What is the difference between SQL and SSIS?
SQL Server is a relational database and SSIS is a ETL (Extraction, Transforming, Loading), two completly different server applications.
Are there any disadvantages to using a stored procedure?
Some relational databases such as SQL Server have some debugging capabilities. Version control is not supported by the stored procedure. An extra developer in the form of DBA is required to access the SQL and write a better stored procedure. This will automatically incur added cost.
What are the advantages and disadvantages of SSIs?
The advantage of SSIS is the flexibility of processing data in an environment designed for data flow, which in many cases is needed in the project and doing that in stored procedures would turn nightmare. Old thread, but a pertinent topic.
Why are SSIS packages more readily recognized as ” jobs “?
I have also found that packages (SSIS and DTS) are more readily recognized as “jobs” – the stored procedures that are executed by scheduled jobs are often overlooked by developers because they can’t see the scheduled jobs.
When to use stored procedures in data flow tasks?
One of the piece of best practice I came away with, was to ALWAYS use stored procedures in data flow tasks in SSIS. I guess there is an argument around security, however the tutor said that as the stored procedures performed all of the work “native” on the SQL Server there was/is a significant performance boost.