SQL Server Performance Forum – Threads Archive

Views run slow the first time they are referenced

I 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?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
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
http://toponewithties.blogspot.com

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.

]]>