November 6, 2012 at 5:26 am
Hi All,
I am working on a project where developers are considering using a Stored Proceedure as data source in SSIS packages. The stored proc will call a SQL View.
Example:
SSIS Data source:
EXEC spSourceData
In SQL Server 2008 SSMS
1. stored procwill have been created as:
CREATE PROCEDURE spSourceData
AS
SELECT * FROM viewData
2. SQL VIEW will have been created as:
CREATE VIEW viewData
AS
SELECT * FROM TABLE A
(will be joined to other tables)
I am just wondering there would be benefits developing the data source like this?
Or would just a stored procedure be better as a source in terms of performance?
A SQL VIEW is re-compiled everytime it is executed, so would there be any benefit to this approach?
Any advise on this would be much appreciated.
November 6, 2012 at 8:34 am
No, a view is not "recompiled" every time it's called. A view is just a stored Select statement. Queries of a view will store their execution plans as per usual plan-caching in SQL Server.
A proc that's just "SELECT * FROM dbo.MyView" is just a waste of code, really. It has the single advantage that you can rewrite the proc to do more, and so long as the inputs (you didn't list any) and outputs (the dataset) are the same, code calling it doesn't have to be rewritten. But without anything beyond a select from a view in it, your opportunities for that are pretty much the same as they would be for rewriting the view and calling it directly.
There is no automatic performance benefit to procs over views. There are functionality benefits, in terms of what you can do with a proc vs what you can do with a view. Things like input and output parameters, error-handling, flow control, logging, etc., in a proc, are advantages for a proc, functionality-wise. Things like being able to Select From, Insert Into, Delete From, and Update, are advantages to a view (if the view is written to allow those things, or has the necessary Instead Of triggers on it to make those things possible even if the view violates the usual rules). Views can also (in some cases) be indexed, while procs can't. But there is no inherent speed difference between the two types of objects.
The idea that views are recompiled every run while procs are "precompiled" is an old "urban legend" in the SQL Server world. Lots of developers believe it, but lots of people believe in alligators in the sewers, Santa Clause, and that Elvis is still alive. Same thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2012 at 10:06 am
Absolutely views are not recompied at run time,
Try this.
Create a table (myTable)
Create view myView as
SELECT * FROM myTable
Modify the table structure
SELECT * FROM myView
You won't see the additional columns until you run the ALTER VIEW command
Even worse, if you have removed columns from the table the view won't run at all.
This is another reason for not using select * in production queries.
Sorry to hijack the thread
November 9, 2012 at 11:20 am
Not a hijack. It's a valid and relevant point.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply