Cache View? (Hard to describe)

[Moebius01]

I'm working with a realtime reporting database built on sql server. In this particular case I'm working on data from an Avaya swtich, but that's less important.

Basically, the data is stored in two formats. Historical which is sumarized by half hour and realtime which covers the current half hour. Right now, I have a job set to pull the historical data every half hour (previous completed interval) and keep a running summary of the midnight to last completed interval. The the realtime system feeds data every 15 seconds on the current interval. Finally, I have a view to summarize the data into a cumulative total (midnight to present). The problem is, said view takes about 3-4 seconds to generate. The Web pages that query this data sometimes have 30-40 hits to this view and can take 20+ seconds to load. As they refresh every 30 seconds this is not really workable.

So, I'm trying to speed up the view, or find another method. The realtime data updates almost constantly (every 15 seconds from multiple switches) which means the view has to re-sumarize every time it's accessed. What I'm looking to do is either 1) force the view to stay cached for say 20 seconds or 2) update a table with the cumulative data every 20 seconds.

Obviously (I think), I can't schedule a job to run every 20 seconds, so is there another way I can achieve this?

 

[Jamfool]

if you cant index the view.

umm create a holding table, insert your data via a proc dependent on a time period in seconds like:.

Create View DataIWant as
select name from sysobjects 
GO

CREATE TABLE [MyResults](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](200) COLLATE Latin1_General_CI_AS NULL,
	[crdate] [datetime] NOT NULL CONSTRAINT [DF_MyResults_crdate]  DEFAULT (getdate())
)
GO

Create Procedure DelayedResults
@SecondsToKeepData int = 20
as

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[MyResults]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN

	IF 
	(SELECT DATEADD(ss,@SecondsToKeepData,max([crdate])) FROM [MyResults]) < getdate() or 
	(SELECT max([crdate]) FROM [MyResults]) is null

		BEGIN
			DELETE FROM [MyResults]

			INSERT INTO [MyResults] ([name])
			SELECT name 
			FROM DataIWant

		END
SELECT * FROM [MyResults]
END

exec DelayedResults 20

 

[Moebius01]

I could (may have already, don't remember) index the view, but it still gets so much new data that it takes a couple seconds to run each time.

On your code suggestion, that would delay putting the data in to a table, but how would I execute it on a frequent enough basis. If I was going to populate a table with the data from the view, it'd need to be re-populated every 20 seconds or so. How can I schedule a proc to run that frequently?

 

[Jamfool]

The proc i wrote will return the data held in [MyResults] table. If the data contained in this table is more than X seconds old, then it will first grab the latest data then return that.
Therefore you do not need to continually run the proc, as it will repopulate when needed.

e.g

exec DelayedResults 60

when this fires if the data is more than 60 seconds old, it will remove the data held in the table and get the latest.