Hi, I know the functionality of Functions,StoredProcs and Views. I wan't to know the performance issues. I heard that views are really bad for performance. What about Functions vs Stored Procs?
SP tips: http://www.sql-server-performance.com/stored_procedures.asp http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp Views tips: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx http://www.sql-server-performance.com/indexed_views.asp Take care not to let the number of special case views explode in your database. Although it might be tempting to provide every users a view to meet specific filtering requirements, remember it is possible to SELECT from an existing view and provide additional filtering. A large number of views can be difficult to maintain, especially when breaking changes are required to the underlying schema. Functions: http://www.sql-server-performance.com/user_defined_functions.asp http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1063700,00.html?bucket=ETA 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.
Thanks a loooooooot Satya. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Given me a good insight. Will get back to you if need more.<br /><br />Thanks once more
Hi, For my problem where a base view is accessing many tables and returning huge result set and is used by other views and queries and has a performance issue, the ideal solution seems to be to convert my base view to indexed view than Function or Stored proc. Thanks.
BOL notifies: Indexed views can be created in any edition of SQL Server 2000. In SQL Server 2000 Enterprise Edition, the query optimizer will automatically consider the indexed view. To use an indexed view in all other editions, the NOEXPAND hint must be used. . . . . Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped, unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition. 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.
Yes I have Got information. Thanks a lot. I will try in test environment and implement it. Hopefully I will have no issues. I have three options: 1) Indexed views (modify tables, views ) - no front end change required 2) Modifying the views to join base tables directly instead of joining base view created 3) Modifying the views to join base tables directly instead of joining base view created and converting them to stored procs (involves front end code change to implement sp) Though I prefer option 1 i will have to try 2,3,1 in that Order. Is it good to spend all that time? Surly This requires that kind of tuning - crusial. Thanks again Satya.
Hi, I have tried option 2 and 3 I have reduced the cost of IO and CPU by changing nested view into just view and also to a stored proc. But have not gained much benefit in duration. What do I do? COnfused! Regards
Ensure the underlying indexes are reindexed and you've compiled SPs after any change in the data. 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.
Thank You. I have reduced the cost of IO and CPU by tuning indexes, improving join but yet! I will try again and check if I've overlooked somethig. I shall get back. Thanks a lot to all of you.
Hello! Will the following statement in a stored proc building the @SqlString reduce performance? The Stored proc actually takes parameters for Where clause and Order By clause. Hope am clear. EXEC sp_executesql @SqlString Thanks.
Using sp_ExecuteSQL may improve performance, but not if the query by itself is performing poorly because of any of the issues mentioned above. sp_ExecuteSQL is a way to allow more query plans to be re-used, but that depends mainly on feeding the parameters into it as true parameters, not as values concatenated into @SqlString.
http://www.sommarskog.se SQL Server MVP Erland Sommarskog has two very interesting articles about dynamic SQL on his site. http://www.sommarskog.se/dynamic_sql.html http://www.sommarskog.se/dyn-search.html Though these a quite long articles, they should give you a good background to make up your decision whether dynamic SQL is your way to go or not. -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
May post the code samples for a better solution. 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.
The only critical part of that procedure is the actual query that is run against the data. Copy the script, and make it print out the entire SELECT statement with all parameter values concatenated into the string. Copy that complete SELECT statement into another query window, and run the query to see the response time. Then run the Index Tuning Wizard on that query.
The link provided by frank has been given me good insight into dynamic SQL, thanks Frank. I think I will stick with simple SPs. My serious issue is performance tuning those nested views. Thanks to all of you.
