Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Source - Stored Proc calls VIEW


Data Source - Stored Proc calls VIEW

Author
Message
AarionSSQL
AarionSSQL
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 253
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
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
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 898
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search