Data layout question

[azzazzello]

Greetings,

I have a an ads data table which has, say, 7 columns:

Date,AdvertiserID,AdID,PublisherID,PublisherSiteID,Impressions,Clicks

"Date" field contains just the Date part (time is zeroed out)
Primary key is composite, consisting of first 5 columns: Date,AdvertiserID,AdID,PublisherID,PublisherSiteID

Suppose I get a million hits an hour (well, a lot, basically), and I update this table immediately. Writes are happening with ReadCommited Isolevel

I need to provide for an interface that advertisers and publishers can go to and look at the impressions and clicks generated. Also, and more importantly, I must watch out for writing (recording data coming in) not interfering with reading (customer queries).

The customer-facing-data can be delayed up to 1 hour

Here is where my questions begin. Do I need to set up a read only database that had data replicated to it at regular intervals from the main (incoming-data processing) database?
If so, what is the best technique to use? Transactional replication?

Or would it be sufficient to have views against the main table, and query against them? If I understand views correctly, they hold pointers to data in tables, and update their references as data comes in. Would just having views be sufficient? Please advise, thank you

 

[FabianS]

Azzazz:

I think it would be sufficient to have views against the main table and query against them.

Best of luck!



Fabian S.

http://www.freeconferencecall.com/

 

[mrdenny]

You can do it with views, or with straight queries hitting the tables. Just make sure that your queries against the tables are using the NOLOCK hint or using Read uncommitted isolation level.

If you are using normal views they so not store data, they simply store the query and the data is queried from the main tables each time the view is queried against.

With a very high load site it will be important to make sure that you have enough RAM to keep the table in memory (or at least the active part) so that the writes happen faster without having to wait for the data to be pulled from the disk.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[azzazzello]

thank you, I appreciate the commentary