CREATE TABLE dbo.logging
	(
		datetimeinserted	DATETIME NOT NULL			CONSTRAINT df_logging_datetimeinserted DEFAULT GETDATE()
		, uniqifier			INT IDENTITY(1, 1) NOT NULL
		, datacol			SMALLINT
		, CONSTRAINT pk_logging		PRIMARY KEY CLUSTERED (datetimeinserted, uniqifier) WITH (FILLFACTOR = 100)
	)
GO

quote:

---

Originally posted by pootle_flump

Hi Mike

Can you show the queries you will use (particularly what you will filter on)? If this is so narrow then presumably their can't be many.... Also, are you pulling up singletons or ranges?

---

quote:

---

Originally posted by Lumbago

To be honest I really didn't understand all of your requirements but what I can say is that caching can be a very good friend. If you can grind your data once every 15 minutes and populate the results to a caching table it will be ALOT faster to query than to run the full query for each request to a website or whatever.

You could also give partitioning a glance, look up "partitioned tables" in BOL. It's only supported on enterprise edition but you can easily create your own custom version using views. Sharing the data load between tables can be a real performance booster if done right.



--
Lumbago

---

quote:

---

Originally posted by Peso

Have a job that each day aggregates the previous days "clicks" and store in a history table.
Delete the records from the source.

And then have a clustered index over date for history table with fillfactor 100%.

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by pootle_flump

You can absolutely prepopulate. The main problem is the "etc." bit. There is no such thing as an optimal indexing strategy without making consideration of the queries you will make on the table. No good having an index on a column that never appears in a where predicate (or join or group by or order by....).

It sounds like you will always be looking for an aggregation of data between x time and y time which, for starters, would mean that you would want the dateinserted column to be the leading column of either the clustered or covering index for that particular query. Would you be looking at one user at a time or all users? Grouping by user or lumping them all together? ... and so on

Pre-aggregating is a great strategy however if the date range you use for your queries might vary then you can only aggregate up to the smallest range (possibly by day and user) which is not, I suspect, especially useful. A datamart might be of use to you here but I'm afraid I have no experience of something like this.

HTH

---

quote:

---

Originally posted by spirit1

in scenarios like this i'd suggest that you don't delete the source data, but transfer it to another database, backup that database and then drop that database. you never know when you might need the old data. and with a backup you can always restore it. if you can you don't even have to transfer the data to another db. just backup the current db and then delete the data from tables.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

---

quote:

---

Originally posted by Peso

Have a job that each day aggregates the previous days "clicks" and store in a history table.
Delete the records from the source.

And then have a clustered index over date for history table with fillfactor 100%.

---