View or SP? 2

[cLFlaVA]

Hi all,

First, I'd like to explain my understanding of Views and SPs (which may or may not be right).

lets say we have a view "MyView" that is compiled like this:

select <column listing>
  from table1 join table2 ...
       <several other joins>

It is my understanding that, when writing a simple select like this:

select * from MyView where MyCol > 2000

will execute the entire view SQL statement, then once the entire data set is returned to the view, return only those records where MyCol is greater than 2000.

now, it is also my understanding that you can also have a stored procedure with the same exact SQL that defines the view above, with the added ability to pass in a variable (2000) and incorporate it into the SP sql, which will effectively return the same data.

however, i feel like there are some cases where the SP is faster. Is it acceptable (by general standards and experts like you) to use an SP for this situation, even though the underlying premise of a SP is to perform transactions on data, rather than simply returning a data set?

I appreciate your time and comments.

Cory



*cLFlaVA
----------------------------
[tt]mr. pibb + red vines = crazy delicious![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[SQLSister]

I use sps to return data sets all the time. An indexed view might be faster but often the sp is. An sp is usually faster than an unidexed view. It is best if concerned about performance to test both.

Views are also sometimes used to restrict users only to the data inthe view, so you need to be aware of mainainting that if you switch to using sps.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[rac2]

I have a largish table with a DATETIME column which is not indexed. I created a VIEW with the primary key and the date column and a stored procedure that searches the table using the date column and a VARCHAR input parameter.

Testing yielded about 59,000 rows after 30 seconds in Query Analyzer for three statements, SELECT from the table which is essentially the same as the statement inside the stored procedure, SELECT from the view, and EXECUTE the stored procedure. So I would say there is no performance advantage.

Also using Query Analyzer, Display Estimated Execution Plan gave estimates of 33.14%, 33.14%, and 33.73% with the stored proc taking more resources.

 

[cLFlaVA]

Thanks SQLSister and rac2, very helpful.

rac2, true, that simple view and sp could easily have the same run time, but when you introduce SEVERAL joins, generating hundreds of thousands of rows of data, the run time (at least in my particular case) was overwhelmingly beneficial from the SP side...



*cLFlaVA
----------------------------
[tt]mr. pibb + red vines = crazy delicious![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[maswien]

In terms of efficiency of using execution plan, I would say there won't be any difference between view or SP, they are all saved code in SQL server, all precompiled.

 

[rac2]

maswien, yes the statement defining the view and the statements in the stored procedure are compiled. But Query Analyzer estimates an execution plan based on the source code, on the uncompiled SQL statements, so it might show a difference.

cLFlaVa could report whether the execution plans differ for his example. That would be of interest.

 

[Luzian]

How do you make a view indexed? If my view was:

CREATE VIEW [vw_SelectUsers]
AS
SELECT *
FROM [Users]

And suppose the [tt][Users][/tt] table is indexed, will the view also be indexed?

And to clarify an index, would the [tt][id][/tt] column be indexed in the following example?:

CREATE TABLE [Users]
(
[tab][blue][id] int NOT NULL IDENTITY PRIMARY KEY[/blue],
[tab]...
)

 

[Pampers]

I thought a big difference between a view and sp, is that the execution plan of the sp is cached, ready to use for the next time... or is that no so?

Pampers
Just back from holiday...
that may it explain it.