SQL Server Performance Forum – Threads Archive

View or Stored Procedure

Which one is better or faster? Having a “view” or a “stored procedure” to generate the results. What is advantages or disadvantages for each of them?
CanadaDBA

---

There really isn’t disadvantages or advantages. They serve two different purposes.
Views are for exactly that, creating a view of the data
Store procedures are used to run transactions agianst your data and to return data based on criteria specified at the time of request Although, using stored procedures to obtain information wouldn’t really be any faster than using a view. SQL server saves all execution plans server side for ANY SQL statement, regardless if it is a view or a stored procedure. I have found this very interesting article that gives a lot of the bad about stored procedures, but make up your own mind about them: http://weblogs.asp.net/fbouma/archi…1/18/38178.aspx Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

There are several SPs in the server that each SP contains only a SELECT with many JOINed tables. I was wondering, if I recreate the results using view concept to increase performance. CanadaDBA

---

I was not able to get into the link and get error page but was able to go tohttp://weblogs.asp.net/fbouma. What is the tilte from then?

CanadaDBA

---

Then in that case I would go for Views instead of SPs. Somehow it(link) worked for me… anyways here is the full link:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Can you guess how much it would affect performance?
CanadaDBA

---

It depends on the data, user connections and number of views maintained on the database.
This is an highly debate question and always there are workarounds to overcome such performance issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

If number of views is increased, does it affect the performance? If yes, then isn’t it better to keep the SPs because based on my undrestanding, SPs are run whenever the application requests while views are always updated and maintained. Am I right?

CanadaDBA

---

The View Tips is right here on the site lol
http://www.sql-server-performance.com/views.asp My point of view don’t try to use View with SQL2000, I have run into many problems. I call the view from VB Application. It is running ok for a while but some time it return PARTIAL DATA not complete I missing some column it just return EMPTY column. Then I have to go into the view open it up CUT and PASTE the code back then it works again Beside I don’t even use SP either, since our internal software written by many and they all use inline code. Furthermore I don’t know how to use it(SP call) in VB hehehhe (this is my secret dont’ tell anyone.
May the best cheaters win

---

Check that out view is just a T-SQL
http://www.sqlteam.com/item.asp?ItemID=1015
INDEX VIEW IS BETTER in performance
http://www1.us.dell.com/content/topics/global.aspx/power/en/ps4q00_microsoft?c=us&cs=555&l=en&s=biz May the best cheaters win

---

Hey tdong,
I don’t think your problem regarding "view" is a SQL2000 issue. If it was, there should be a lot of screams to Microsoft from all around the world. There should be a problem in your application or the way it is retrieving data. Regarding "SP call" from VB, I know that there are lots of information about how to connect to SQL Server from within VB application and it is very easy. For a sample code, have a look at the following:
http://msdn.microsoft.com/library/d…n/html/vbtskcreatingconnectiontosqlserver.asp Thank you for the links about indexed views.

CanadaDBA

---

probably it was in the datareport in the VB we are using Crystal Report now so it doesn’t matter anymore. By the way I know how to do those thing already just don’t know about the SP parameter etc. see for now I am using INLINE code. sql ="…" and execute that. beside it is ok I like inline code better since I created those table too and I already wrote all the script to create tables for DB (need only one USER for the DB) and the software just run no need for a DBA to do much work(isnt’ that what we all want ? ) . Beside I am controlling user account through employees table user create and destroy there. Another thing is we have many division using the same software but different database. would it it be better to give the DBA here is the script to create the table execute it. then the user install the application and run. Furthermore if I have SP I still have to embeded the code for calling the SP and passing parameter to it I dont see the point of doing all the extra work. Private Function SaveChanges() As Boolean
Dim sql As String
Dim rs As ADODB.Recordset
If Validate() Then
sql = "insert into employees (employee_code, employee_name, pwd,phone_number, fax_number, " & _
"email_address, admin_lev) " & _
"values(‘" & txtUserid & "’,’" & txtName & "’,’" & txtPassword & "’,’" & txtPhone & "’,’" & txtFax & "’,’" & _
txtEmail & "’,’" & txtAdminlv & "’)"
If ExecuteSql(sql) Then
SaveChanges = True
Else
MsgBox "Cannot add to database! Please contact administrator.", vbCritical, "BLOTTTER – ERROR"
End If
End If End Function May the best cheaters win

---

There are many reasons to use stored procedures. I cant think of any reason to inline SQL into your application (laziness isnt considered a valid excuse hehe!)<br /><br />If you use stored procedures, they can be treated very much like a black box, its much easier to maintain. You can upgrade the stored procedure and the upgrade takes effect immediately. For inline SQL, you must recompile the application and then have everyone restart the application. There is no clean cut over with multiple users using an inline SQL application.<br /><br />Although SQL2000 will attempt to cache ad-hoc sql statements, you are causing more work for it since it must parameterise the adhoc statements in order to break it down into a cacheable operation. I dont know what the success rate for SQL is in doing this, but I would estimate not all adhoc statements can be cached (or rather, reused!) in such a way, whereas if they were in stored procedures they could be cached as normal.<br /><br />Additionally by embedding values into inline SQL, you are more vunerable to passing invalid data to SQL server. If you at least use ADO parameters the invalid data can be trapped in the connectivity layer (ie ADO), preventing the call to SQL in the first place.<br /><br />Anyway this isnt an attack on you tdong! just you raised some interesting points I thought Id address <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />To answer the original post Id recommend SP over non indexed views. A non indexed view is basically just ‘saved’ SQL which SQL Server expands out when the view is referenced. You could think of a non indexed view as similar to a macro in C. <br />You may be able to use indexed views, in which case the indexed view may well perform better than a stored procedure. It all depends on what youre querying and how, you will need to investigate.

---

Thanks Chappy,<br />I am going to try implement the SP (which as I have said, is only a SELECT) with indexed view. I’ll post help request if i felt in problem <img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br /><br />CanadaDBA

---

I don’t get what you mean by embedding values into inline code ? Mainly my application would take input from the user (do some financial compuation) then insert those into the database or update the database no delete operation. What is the different between passing those value through inline code and SP ? even with SP I still have to create variable and pass the value to the SP. Beside I have to do lots of calculation before having the final value to store in DB May the best cheaters win

---

The difference is that with an SP the data types of the parameters are defined and thus you have another layer of security and error handling. That is if you call the SP the "correct" way using functions like CreateParameter in ADO (and not like "sql = "sp_myInsert ‘" & FirstName & "’, ‘" & LastName & "’"). Not only does it help you with error handling (i.e. A is not a number) but it will also handle the security issues with single quotes ‘. You want to avoid issues with people submitting stuff like admin’– or ‘; drop table users– in your web form variables without having to handle it on every single page. Another thing is that you have the option of using output and return parameters with SPs. This helps with error handling in your sql statments. A third thing is that you can set execute rights on the stored procedures without giving the sql user rights to read and write in the underlying tables.

---

Thanks I will give it a try <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />May the best cheaters win

---

]]>