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
