Advice Please

Hello

Which is better and faster?? and WHY??
Writing Select Statement with joins in Stored procedure,
or creating view and calling it from stored procedure (select * from view)..

If the view has the same join in it as the select statement, then the speed will be the same.

|||

I heard thats using a view in such case will slow down my performance??

|||

I've never heard that. do you have a reference? You could test it to verify.

|||

It is likely faster to just use the stored procedure, though in your simple example, the benefit would be minimal. If you are applying where clauses or order by clauses (can't sort in a view) in the stored procedure, the benefit might be more significant. Regardless, the SQL engine has a query optimizer and caches the query plan in most cases, so your best bet is to write an ad-hoc query to call each case, and view the plan... see if it is different, and decide which would be more efficient. My guess is that in a lot of cases, the plan will be the same.

That said, an advantage of the view is reusability, and sometimes this is traded for a very slight performance hit (select some columns that aren't always used). Also, you can use indexed views, which will actually greatly increase performance for join intensive queries with some storage costs as well as a performance hit on insert / update / delete operations. These are probably most efficient when joining to lookup type tables that don't change much.

One other thing... I read a couple of times (don't remember where) that using the SELECT * is inefficient for some reason (don't remember why either...). It could be better to specify the columns explicitly.