SQL Server Performance Forum – Threads Archive
Views run slow the first time they are referencedI have a view that when referenced in a query the first time will run for minutes for a small result set. After it has been referenced once, then successive queries can pull back much larger datsets in seconds. Is the view being "compiled" or cached in some way that makes other queries work as expected? If so is there a way to force this to occur on a regular interval. The first execution against the view takes much longer than it should and the data result set is quite different from the successive queries so it does not appear to be a data caching issue. The result of this is that when end users run queries on this view they fail if they are the first to reference the view. ANy ideas would be appreciated.
Just to clarify: 2000 or 2005?
SQL-Server-Performance.com All in Love is Fair
All postings are provided â€œAS ISâ€ with no warranties for accuracy.
minutes seem way high for a compile try using DBCC FREE PROCCACHE, DROPCLEAN BUFFERS SET STATISTICS IO &
SET STATISTICS TIME to do some investigation note: stat IO gives disk reads, stat TIME include compile time
this is sql 2005
Have you tried running again with DBCC FREEPROCCACHE and DROPCLEANBUFFERS Roji. P. Thomas
SQL Server MVP
Will run the DBCC FREE PROCCACHE, DROPCLEAN BUFFERS today and see what happens. The problem is knowing when the "first time" will be. We ran the query through the profiler and it appears that 98% of the time is being spent in an index search.