Data Source - Stored Proc calls VIEW

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Data Source - Stored Proc calls VIEW

Data Source - Stored Proc calls VIEW

Rate Topic

Display Mode

Topic Options

Author

Message

AarionSSQL

Posted Tuesday, November 06, 2012 5:26 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 9:07 AM
Points: 14, Visits: 158

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.

Post #1381519

GSquared

Posted Tuesday, November 06, 2012 8:34 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #1381607

aaron.reese

Posted Friday, November 09, 2012 10:06 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:33 AM
Points: 234, Visits: 441

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

Post #1383122

GSquared

Posted Friday, November 09, 2012 11:20 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #1383162

« Prev Topic | Next Topic »

Permissions