T-SQL, View or Stored Procedure
-
T-SQL, View, or Stored Procedure, which is the best way to code the following? Which is more efficient? Which is more secure? Which is more flexible?
select OrderId from northwind.dbo.orders
go
create view v_test as
select OrderId from northwind.dbo.orders
go
create procedure usp_test as
select OrderId from northwind.dbo.orders
go
Gregory A. Larsen, MVP
-
In what context ?
One might be inclined to answer "It depends..."
I use all three techniques depending on the requirements at hand.
Ok,
"select OrderId from northwind.dbo.orders
go"
that is the one we don't let / (or sometimes want) clients to use
/rockmoose
You must unlearn what You have learnt -
I am here all 100% with rockmoose I try A LOT to use sp as much as I can. Why? The indirection level that it provides (Isolation from data structure) plus (Compiled plan) are two of my favorites. Again there might be cases in which flexibility or lazyness
may come in the way
* Noel -
I'm with you on the straight SELECT Statement.
But isn't a view just a little more flexible. You can use a view in a FROM clause very easy, whereas that is not the case with a SP. Also you can update a view.
But what I really wonder does a SP perform faster than a view?
From books online the following can be found in the "CREATE VIEW" statement:
This is similar to a stored procedure; when a view is executed for the first time, only its query tree is stored in the procedure cache. Each time a view is accessed, its execution plan is recompiled.
So if a view is recompiled on each access, is a SP faster?
Gregory A. Larsen, MVP
-
Hi all,
The reason that someone will use Sp is to pass the parameters to the statement like if he needs to select order IDs for particular customer, different each time. Again, as you say, you can select from a View. There is something that combines the advantages of both: Table-Valued Function. Please, read the following BOL article that list cases when you want to use the inline table-valued function:
"Rewriting Stored Procedures as Functions"
Regards,Yelena Varsha
-
Good point on passing parms to SP's. What I'm really trying to find out is will a view that does a SELECT, out perform a SP that does the same static SELECT. Will a table-valued function out perform a view, or Stored Procedure?
Gregory A. Larsen, MVP
-
Greg,
You have a good site with examples!
I am not really sure what will out perform what. The only thing that I know that both a view and a function do hide a complexity of SQL Statement from the view/ function user. This means that a user may underestimate the complexity, use it in the more complext statements wich may lead to poor performance. Additionally: it is rare that you are not using selective subsets (parameters!) and you can not use Order By in a view unless you use TOP N like Select TOP 5 * from...
Unless you are really interested in theoretical answer, there is a lot you can do to improve performance of any statement by using proper indexes, Prepare, sp_executesql, desiging a proper database layout placing friequently joined tables on the different drives, placing tr log on the separate drive and a lot more for the layout, design a correct front end, add more hardware, monitor for bottlenecks, watch locking problems and so much more!
Yelena
Regards,Yelena Varsha
-
Hi there,
if you want to check what is better - load the profiler and see how fast each one is - this should give you an indication - just bare in mind that if you a smaller table then it will not give you a result that you can use - hope this help
-
The stored procedure will run faster as long as the cached plan remains cached. At the point a recompile must occur, it should be about even. The BIG exception to this is if you index the view. Then, the view would be faster.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
If your client software has to run a SELECT on your view then it will be slower than if your client simply executes the procedure.
I don't know if it is true for ADO.NET etc but in the old days the ODBC layer used to have to parse the SQL statement then transmit it to the server, then the server had to compile it, then execute it.
A stored procedure simply received an instruction to execute.
If your view is used by other objects on the server then there really isn't much (if any) difference.
-
Hi,
I made an internal ranking..
T-SQL, View, or Stored Procedure,
Which is more efficient?
Which is more secure?
Which is more flexible?
efficient secure flexible sumpoints
select 1 1 2 4
view 2 2 3 7
sp 4 4 4 12
table function 3 3 1 7
Anyone else ?
/rockmoose
You must unlearn what You have learnt -
Just remember that if you are testing two queries to see which one is faster, a good idea is to flush the server's data cache after the first query. There is a DBCC command to do this, though I can't remember it offhand. Brian Knight will know, as I read it in his book.
-
See
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
/rockmoose
You must unlearn what You have learnt -
Thank you for all the replies. I guess I got what I expected. Speed of view verses sp procedure depends on a number of factors.
Gregory A. Larsen, MVP
-
Let me add one more thing when the complexity of the operations are high there is NOTHING like a stored procedure. With stored procedure you can ENFORCE Filters (parameters). You can also reuse with efficiency in other words you are in control
* Noel
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply