Asger Blond
ab-mi at post3.tele.dk
Mon Jun 30 16:58:11 CDT 2008
Also notice SQL Server's strategy for execution plan caching. Execution plans are saved in the cache whether you use ad hoc queries, views, udfs, or sprocs. But ad hoc queries, views, and udfs will normally outdate sooner from the cache than sprocs will. Only for sprocs SQL Server records the cost of query compilation and if the query in your sproc is reasonably complex, then the execution plan will survive longer in the cache than an equivalent ad hoc query, view, or udf. For that reason I normally would prefer sprocs. But of course plan caching is only one factor affecting performance. Implementing sprocs you still have to decide whether your sproc should query tables, views, udfs, or perhaps make use of the new CTEs (Common Table Expressions). For this I too would appreciate experiences/recommendations/opinions. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Francisco Tapia Sendt: 30. juni 2008 20:32 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] Views versus derived tables versus table UDFs small UDF tables are fast.. so there is not much of a performance hit since most of the data is loaded to RAM, but if you are dealing with thousands of rows then you will want to re-do those UDFs as sprocs or Views if possible :) imho On Mon, Jun 30, 2008 at 5:23 AM, Arthur Fuller <fuller.artful at gmail.com> wrote: > Does anyone have any opinions on the performance of views versus derived > tables versus table UDFs? I've been looking at some of the code here at my > new job and I see some pretty extensive use of derived tables. In the past, > I have always used views or table UDFs for such operations, pretty much > because it simplified the code, rather than because I did performance > checks. > > TIA, > Arthur > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- -Francisco http://sqlthis.blogspot.com | Tsql and More... _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com