Re: Stored procedure VS Views
Yes, but how does one go about creating a materialized view on SQL Server? I've heard of it being done all the time in Oracle contexts, but I've not heard a lot about it in the MS SQL Server world. Incidentally, if you want to run ad-hoc queryies and still want the performance of an Sproc, there's a particular SQL Server built-in sproc to parameterize ad-hoc SQL. It's called sp_execsql and it's got some nifty, but challenging methodology behind it. If you want to know more, see the articles in SQL Server Books Online entitled: sp_executesql Using sp_executesql I've used it, and it's nice. It caches it's execution plans for each combination of variables, consequently, it has a tendency to accellerate performance when executing ad-hoc queries based on it much more quickly. Eventually, the theory is, most of your parameters will be cached. If someone knows more on the subject, please feel free to crush my happy news... I'd rather know more than continue to be blissfully ignorant. Besides, learning makes me happy. And... well... I think I've had enough coffee for the day. Laterz! J On Tue, 18 Jan 2005 14:03:06 -0500, Adrocknaphobia <[EMAIL PROTECTED]> wrote: > Here is the skinny. A stored procedure is faster than an inline query > because it is precompiled on the database and most databases make > 'plans' on the fastest way to execute the query. > > A view is compiled as well. However when you query a view it executes > the view's sql then it executes your sql on the returned results. So > essentialy a stored procedure calling a view is like running a stored > proc on a stored proc. > > Now a materialized view (or snapshot) is _executed_ on set intervals > or manually and actually physically creates a table. So when you run a > stored procedure on a materialized view, you are essentialy only > running a single SQL procedure. > > -Adam > > Donations & Support: http://www.houseoffusion.com/tiny.cfm/54 > -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com http://cfobjective.blogspot.com ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191016 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored procedure VS Views
Here is the skinny. A stored procedure is faster than an inline query because it is precompiled on the database and most databases make 'plans' on the fastest way to execute the query. A view is compiled as well. However when you query a view it executes the view's sql then it executes your sql on the returned results. So essentialy a stored procedure calling a view is like running a stored proc on a stored proc. Now a materialized view (or snapshot) is _executed_ on set intervals or manually and actually physically creates a table. So when you run a stored procedure on a materialized view, you are essentialy only running a single SQL procedure. -Adam On Tue, 18 Jan 2005 06:35:42 -0400, Will Tomlinson <[EMAIL PROTECTED]> wrote: > With everyone's help I'm pretty much wrapping up the functionality of my > clothing app/SQL Server db. I was planning on converting much of my SELECT > s to stored procedures, and started reading some about views. Is a > view equal to a stored procedure in performance? Does it matter at all which > you use? Is one easier than the other? > > I'm trying to keep the load off of CF and place it on SQL Server since I'm > using shared hosting. > > Thanks, > > Will > > ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190972 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored procedure VS Views
Will, I believe that Stored Procs are more efficient since SQL will create execution plans for them, while for views, they have to be created for each execution. Andy -Original Message- From: Will Tomlinson s With everyone's help I'm pretty much wrapping up the functionality of my clothing app/SQL Server db. I was planning on converting much of my SELECT s to stored procedures, and started reading some about views. Is a view equal to a stored procedure in performance? Does it matter at all which you use? Is one easier than the other? I'm trying to keep the load off of CF and place it on SQL Server since I'm using shared hosting. ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190912 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
