update	qs_cache_times
	set		lastupdate	= getdate()
	where 	name = 'qs_update_sysfiles'
	and (getdate() > dateadd(second,cache_seconds,lastupdate)) -- cache_seconds = 5 seconds (say)

	if @@rowcount = 0
	begin
		if @Debug > 0
			print 'Cache is already up to date.'
	end
	else
	begin
		if @Debug > 0
			print 'Repopulating the cache table.'

		begin tran
		truncate table cache_table
		insert into cache_table select * from ....
		-- Lots of other repopulating code here.
		commit tran
        end

	if (@DoSelect <> 0)
		select * from cache_table

quote:

---

Originally posted by mohdowais

Brett, I think sql server only caches execution plans and not data.

---

insert into cache_table select * from ....
		-- Lots of other repopulating code here.

  -- this is the repopulating section from above:
  -- Note that I have Moved the truncate/insert to After the collecting into a temp table.
  -- This is new, and has stopped my problem with 'deadlock victims', however it means I'm
  -- Copying the data an extra time.
	begin
		CREATE TABLE #qs_dbcc_showfilestats (
			[DBName] [sysname] not NULL default '',
			[Fileid] [smallint] not NULL ,
			[FileGroup] [smallint] not NULL ,
			[TotalExtents] [int] NULL ,
			[UsedExtents] [int] NULL ,
			[Name] [sysname] NOT NULL ,
			[FileName] [char] (255) NOT NULL 
		)

		declare @DBName	sysname
		declare	@sql	varchar(2000)
		set	@sql = ''
		set	@DBName = ''
		while 1=1
		begin
			select @DBName = min(name) from master.dbo.sysdatabases with (nolock) where name > @DBName and status & 4096 = 0 and has_dbaccess(name) <> 0
			if @DBName is null
				break

			set @sql = 'use '+quotename(@DBName)+' dbcc showfilestats '
			insert into #qs_dbcc_showfilestats (Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName)
			exec(@sql)
	
			update	#qs_dbcc_showfilestats
			set		DBName = @DBName
			where	DBName = ''
		end
		
		begin transaction
		---------------------------
		-- START OF POPULATION CODE
		---------------------------
		truncate table qs_dbcc_showfilestats
		insert into qs_dbcc_showfilestats select * from #qs_dbcc_showfilestats
		commit transaction
	end 
  

Tables/Views:
	Cache_Table_0
	Cache_Table_1
	MetaLiveTable (LiveTB bit) 1 row, 1 column defines which table is live
	Cache_View

SPs:	select <colList> from Cache_View

repeating or continious process:
	truncate non-live Table
	re-populate non-live table
	begin tran
		alter view to point to re-populated table
		toggle MetaLiveTable
	commit or handle errors

quote:

---

Originally posted by TG

Jules,

Tables/Views:
	Cache_Table_0
	Cache_Table_1
	MetaLiveTable (LiveTB bit) 1 row, 1 column defines which table is live
	Cache_View

SPs:	select <colList> from Cache_View

repeating or continious process:
	truncate non-live Table
	re-populate non-live table
	begin tran
		alter view to point to re-populated table
		toggle MetaLiveTable
	commit or handle errors

---

quote:

---

Originally posted by TG

Jules,
I haven't worked enough with Spotlight to come up with a feature list :) but I was
impressed with it.

As far as how I've solved (or lived with) this issue you're asking about, it's really
on a case by case basis. Most of the solutions I've been involved with don't really
seem to apply to your situation. The closest thing might be something like this where
the advantage is that consumers never wait or are blocked by processing.

Tables/Views:
	Cache_Table_0
	Cache_Table_1
	MetaLiveTable (LiveTB bit) 1 row, 1 column defines which table is live
	Cache_View

SPs:	select <colList> from Cache_View

repeating or continious process:
	truncate non-live Table
	re-populate non-live table
	begin tran
		alter view to point to re-populated table
		toggle MetaLiveTable
	commit or handle errors

Be One with the Optimizer
TG

---