Hi there,

I'm counting web page hits.

Upon the first request of a page I must do an insert of the row holding the count. Ater that its an update.

I think the best way is to say..

1. Try an update.
2. If no rows updated try an insert.
3. If there's a primary key violation then do an update.

Any ideas?

Cheers, Ian.

Hello Ian,

I use the GetDbCount() function to know if a record already exists or not. It costs a roundtrip to the DB but it doesn't degrade performance.

In your case, if I correctly understand, you need to do an Insert query only the first time a page is accessed, then for months you're going to do updates.... Your idea :

1. Try an update.
2. If no rows updated try an insert.

is not bad if you think that an insert is exceptional.

Anyway I would solve the problem by creating a stored procedure which would test if the row exists and take the appropriate action. It seems cleaner to me but it's personal.

Hi,

The problem with doing that is that in between the call to GetDbCount and doing an insert, someone else might have inserted the row. So one's insert will fail due to a duplicate primary key.

Ian.

How about a stored procedure with a high level of isolation (lock)?

I don't think its possible to lock a row that doesn't exist.

IMHO a better approach is to have a row pre-created in the database for each page, with a count of 0. (at deployment time) That way you can use a single UPDATE statement and be sure it's gonna work. This seems to me like the fastest approach.

Well I prefer my way at the moment. Having to pre-create a row mixes the tracking code with the code for creating a news article. (That's what's being tracked).

Also, given that the hits for the articles are counted for each day then pre-creating a row would actually mean creating one row for every news article every day which isn't very sensible given that the vast majority of the articles won't even get any hits on a given day.

Anyhow, given that with my method an insert is only required once then once the row has been created its just a straight update every time.