Running a query in Page Load a bad idea?

Question

I'm running an ASP.NET app in which I have added an insert/update query to the [global] Page_Load. So, each time the user hits any page on the site, it updates the database with their activity (session ID, time, page they hit). I haven't implemented it yet, but this was the only suggestion given to me as to how to keep track of how many people are currently on my site.

Is this going to kill my database and/or IIS in the long run? We figure that the site averages between 30,000 and 50,000 users at one time. I can't have my site constantly locking up over a database hit with every single page hit for every single user. I'm concerned that's what will happen, however this is the first time I have attempted a solution like this so I may just be overly paranoid.

Answer 1

Do it Async.

Create a dll that handles the update, and in the page load do a fire and forget with parameters.

Answer 2

I think updating the database in the begin session and end session will do the job. that will reduce the count of statements dramatically.

I think it makes no difference if you track hits or begin/end session. with hits you'll also need additional logic to subtract inactive users

EDIT: session end is not fired always. I would suggest to call an update statement/stored procedure in another session begin event (in addition to the other insert statement) that will fix invalid sessions.

I don't think that calling this "fix routine" is necessary in every page load event because I think you cant exactly count "current no. of visitors".

Answer 3

What's wrong with IIS Logs?

2009-05-01 12:30:31 207.219.27.35 GET /assocadmin/ibb-reg.asp - usernameremoved 544.566.570.575 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+6.0;+SLCC1;+.NET+CLR+2.0.50727;+Media+Center+PC+5.0;+.NET+CLR+3.5.30729;+.NET+CLR+3.0.30618) 200 0 0 40058

EDIT: I'd like to close this answer, but I want the comments to stay. Consider this answer withdrawn.

Answer 4

Insert-Based designs have less locking than Update-Based designs.

So if a user logged-in and then logged-out, in an Insert-Based design you would have multiple rows with a SessionID in each, one for each activity whereas in an Update-Based design, you would have a SessionId, LoginTime and a LogoutTime column and you would update the LogoutTime based on the SessionId.

I have seen many more locking and contention problems caused by Update activity more than Insert activity.

Activities such as counting and linking logins to logouts etc take more complex queries and a little more resources.

It goes without saying that your queries, especially the ones that run on every page, should be as fast as possible so that the site doesn't appear slow to users.

Answer 5

To keep track of how many users are currently on your site you could use performance counters. What you describe though sounds more like a full fledged logging of every page hit.

Lets say you realy have 50k users connected at any one time.

As long as you don't have contention between the updates (trying to lock the same record) a database can track a very high number of inserts and updates. You need to do some capacity planning to assure the load can be carried. 50k users visiting a page every minute will give you 50k inserts and 50k updates per minute, roughly 850 inserts and 850 updates per second, which have to commit (flush the log). Does your DB I/O subsytem support such a write pressure load, in addition to responding to all the requests (reads)?

Also 50k users doing 1 page hit per minute adds up to 72 mil hits per day, 72 mil. logging inserts, at such a rate you need to carefully plan the size capacity of the database and consider what kind of analysis you'll do on the collected data since querying ad-hoc 2 billion rows (one month data) will get you nowhere fast (actually... quite slow).

Doing it async can give you some relief over very short spikes, but not on the long run. If your DB system cannot handle the load then doing async calls will just create a backlog queue in the application process (in the ASP app pool) and this will grow until out of memory, at which moment the all vigilant IIS will 'recycle' the app pool, thus loosing all pending async updates.

Answer 6

I would keep this in Application state instead - if possible. On ApplicationStart create some data structure saved to App state that you can update from anywhere in your application - session start, page load, wherever. Keep it out of the database. You are just using it to track "currently online" info anyway it sounds like.

If you have multiple instances of your app, or if there is a requirement to maintain historical info beyond the IIS logs, this won't work obviously. Go with chris' fire-and-forget solution in that case.

Answer 7

How about adding a small object to the session?

Something like LoggedInUserFlag:IDisposable

In the constructor, increment your counter however you decide to implement it.

Then in the Dispose method, decrement the counter.

This way, regardless of how the session is ended, the counter will always be (eventually) decremented.

see:

http://weblogs.asp.net/cnagel/archive/2005/01/23/359037.aspx

for info on using IDisposable.

Answer 8

I am not an ASP guy at all, but what about rather than logging all that other info, and insert their IP address?

If they have an IP address already in there, have a last_seen timestamp, and on each refresh just delete any row that isn't 10 minutes ago?

This is how I would take a shot at it. It is much more space efficient, but I am not sure about the checking and deleting so much on such a high profile site.

Answer 9

As a direct answer to your question, yes, running a database query in-line with every request is a bad idea:

1. Synchronous requests will tie up a thread, which will reduce your scalability (fewer simultaneous activities)
2. DB inserts (or updates) are writes to the DB, which will put a load on your log volume
3. DB accesses shouldn't be required in a single server / single AppPool scenario

I answered your question about how to count users in the other thread:

http://stackoverflow.com/questions/1822921/best-way-to-keep-track-of-current-online-users

If you are operating in a multi-server / load-balanced environment, then DB accesses may in fact be required. In that case:

1. Queue them to a background thread so the foreground request thread doesn't have to wait
2. Use Resource Governor in SQL 2008 to reduce contention with other DB accesses
3. Collect several updates / inserts together into a single batch, in a single transaction, to minimize log disk I/O pressure
4. Return the current count with each DB access, to minimize round-trips

In case it's of any interest, I cover sync/async threading issues and the techniques above in detail in my book, along with code examples: Ultra-Fast ASP.NET.