Preventing overload of SQL hits

  • Thread starter Thread starter Greg Collins [InfoPath MVP]
  • Start date Start date
G

Greg Collins [InfoPath MVP]

I'm designing a series of ASPX pages (actually one page with a series of parameters) which call a SQL SPROC to update a visit count.

This is great right now because the pages are not visited very much. I anticipate eventually having a large flow of traffic through these pages. My worry is that if I eventually have 100,000+ visiters to these pages per day, I'm going to overload SQL by calling the SPROC per visit per page to perform an increment to the visit count.

I thought of a solution, and I'm seeking either clarification that this is a good idea, or alternate ideas from those who've been there. My idea is this:

Have each page update a hashed value in a Application variable. Every five minutes or so, have the Application visit counts would be sent to the SPROC for a single update, and would pull back down the counts for the top-10 list being built.

It seems to me like this would save a lot of traffic and wear-and-tear on the SQL database. Am I headed the right direction, or is there something better I should be looking at?

Thanx!
 
your approach is ok. but 100K visits a day is not very high, the sqlserver
should handle the load fine.


-- bruce (sqlwork.com)



"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message I'm designing a series of ASPX pages (actually one page with a series of
parameters) which call a SQL SPROC to update a visit count.

This is great right now because the pages are not visited very much. I
anticipate eventually having a large flow of traffic through these pages. My
worry is that if I eventually have 100,000+ visiters to these pages per day,
I'm going to overload SQL by calling the SPROC per visit per page to perform
an increment to the visit count.

I thought of a solution, and I'm seeking either clarification that this is a
good idea, or alternate ideas from those who've been there. My idea is this:

Have each page update a hashed value in a Application variable. Every five
minutes or so, have the Application visit counts would be sent to the SPROC
for a single update, and would pull back down the counts for the top-10 list
being built.

It seems to me like this would save a lot of traffic and wear-and-tear on
the SQL database. Am I headed the right direction, or is there something
better I should be looking at?

Thanx!
 
Back
Top