Tracking High Volume of Impressions

[Arsen V.]

Hello,

Did anyone have some experience with the following:

1) Need to track high volume of impressions - 20,000,000+ per day
2) Backend is SQL Server 2000
3) Webfarm of IIS with ASP.NET
4) Need to track the data as follows:
DATE, TIME (resolution should be 1 hour), ID, NUMBER OF IMPRESSIONS, SOME
OTHER COUNTS
05/01/2004, 13:00, 123adfc1234, 34567, ...
05/01/2004, 13:00, 444adfc1234, 567, ...
05/01/2004, 13:00, 222adfc1234, 67, ...
05/01/2004, 14:00, 123adfc1234, 32012, ...
....

I want to avoid hitting the database as much as possible. I would like to
accumulate the summary data somewhere else and only hit the database every
few minutes.

If I use an in-memory DataTable on the webfarm, I am worried about threads
locking each other when updating it.

I am thinking I need some kind of an shared MemoryStream which will server
as a buffer with minimal locking. This buffer would be used by a background
thread (how to PROPERTLY do this in ASP.NET?) to generate the DataTable
which will be kept in Application scope. Once every X minutes that DataTable
would be committed to backend SQL server.

While the background thread processes the MemoryStream a new one would need
to be immediatelly available to the tracking web servers.

Another solution I was thinking about was TCP/IP Remoting or even stateless
UDP packets sent to a central server that would be listening for them and
updating an DataTable object. However, I am worried that all those requests
will be too much for a DataTable object to process.

Thanks in advance,
Arsen

[Andrew J. Kelly]

How about microsoft message queue (MSMQ)?

--
Andrew J. Kelly SQL MVP
"Arsen V." <ar****************@emergency24.com> wrote in message
news:uD**************@tk2msftngp13.phx.gbl...

Hello,

Did anyone have some experience with the following:

1) Need to track high volume of impressions - 20,000,000+ per day
2) Backend is SQL Server 2000
3) Webfarm of IIS with ASP.NET
4) Need to track the data as follows:
DATE, TIME (resolution should be 1 hour), ID, NUMBER OF IMPRESSIONS, SOME
OTHER COUNTS
05/01/2004, 13:00, 123adfc1234, 34567, ...
05/01/2004, 13:00, 444adfc1234, 567, ...
05/01/2004, 13:00, 222adfc1234, 67, ...
05/01/2004, 14:00, 123adfc1234, 32012, ...
...

I want to avoid hitting the database as much as possible. I would like to
accumulate the summary data somewhere else and only hit the database every
few minutes.

If I use an in-memory DataTable on the webfarm, I am worried about threads
locking each other when updating it.

I am thinking I need some kind of an shared MemoryStream which will server
as a buffer with minimal locking. This buffer would be used by a background thread (how to PROPERTLY do this in ASP.NET?) to generate the DataTable
which will be kept in Application scope. Once every X minutes that DataTable would be committed to backend SQL server.

While the background thread processes the MemoryStream a new one would need to be immediatelly available to the tracking web servers.

Another solution I was thinking about was TCP/IP Remoting or even stateless UDP packets sent to a central server that would be listening for them and
updating an DataTable object. However, I am worried that all those requests will be too much for a DataTable object to process.

Thanks in advance,
Arsen

[Scott Allen]

Hi Arsen:

I wouldn't use a DataTable, but hand craft a small class with strongly
typed properties to hold information for each impression.

For a container, you could chose an ArrayList, and Add an object for
each impression. You will need threads to acquire a lock before adding
an object reference to the list.

I wouldn't keep the list in Application state, as this only adds to
additional locks (Application Get and Set use a reader / writer lock).
Instead, I'd keep it encapsulated away as a static field inside of a
class devoted to managing these impressions.

One way to kick off a background thread periodically is to start a
System.Threading.Timer in Application_OnStart in global.asax. The
timer will periodically "fire" at an interval you define call a method
on thread from the thread pool.

Last bit of advice:

Design everything so that the final implementation is hidden. Abstract
everything so that you can easily switch from non-cached impressions
to cached impressions to whatever else might fit the best. Then start
with the simple case where the impression is not stored in a list at
all but sent directly to the database. From there, do some stress
testing and make some improvements to meet the performance goals. With
some up front planning you'll be able to devise an interface where
switching to using a collection and a periodical background thread
won't require any changes in the application code. In other words,
start simple but design for flexibility.

Make any sense?

HTH,

--
Scott
http://www.OdeToCode.com
On Mon, 21 Jun 2004 10:55:30 -0500, "Arsen V."
<ar****************@emergency24.com> wrote:

Hello,

Did anyone have some experience with the following:

1) Need to track high volume of impressions - 20,000,000+ per day
2) Backend is SQL Server 2000
3) Webfarm of IIS with ASP.NET
4) Need to track the data as follows:
DATE, TIME (resolution should be 1 hour), ID, NUMBER OF IMPRESSIONS, SOME
OTHER COUNTS
05/01/2004, 13:00, 123adfc1234, 34567, ...
05/01/2004, 13:00, 444adfc1234, 567, ...
05/01/2004, 13:00, 222adfc1234, 67, ...
05/01/2004, 14:00, 123adfc1234, 32012, ...
...

I want to avoid hitting the database as much as possible. I would like to
accumulate the summary data somewhere else and only hit the database every
few minutes.

If I use an in-memory DataTable on the webfarm, I am worried about threads
locking each other when updating it.

I am thinking I need some kind of an shared MemoryStream which will server
as a buffer with minimal locking. This buffer would be used by a background
thread (how to PROPERTLY do this in ASP.NET?) to generate the DataTable
which will be kept in Application scope. Once every X minutes that DataTable
would be committed to backend SQL server.

While the background thread processes the MemoryStream a new one would need
to be immediatelly available to the tracking web servers.

Another solution I was thinking about was TCP/IP Remoting or even stateless
UDP packets sent to a central server that would be listening for them and
updating an DataTable object. However, I am worried that all those requests
will be too much for a DataTable object to process.

Thanks in advance,
Arsen