Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data Source - Stored Proc calls VIEW Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 5:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:46 PM
Points: 17, Visits: 237
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
Posted Tuesday, November 6, 2012 8:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Friday, November 9, 2012 10:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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
Posted Friday, November 9, 2012 11:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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 »

Add to briefcase

Permissions Expand / Collapse