best practice question on providing calculations on the fly

Question

best practice question on providing calculations on the fly

sqlPirate_Fl

SSC-Addicted

Points: 442
More actions
July 22, 2010 at 4:14 pm

#139925

I have a question for the guru's.
When operating a website, expecting n - unlimited users (hopeful I know), I just wanted to get the feel on the common practices. Say you have items that belong to categories. I want the hopeful client when doing a search to come to my category list and see as accurate as possible the number of items they'll find under the respective categories. So if I have category A, B and C and A has 10,000 active items, B has 4,800 active items and C has 2,300 then I want the client to see this beside the link before they click the category link to then be transferred to their "items list".
I think I know the answer, but I wanted a confirmation. In a perfect world I could do the calculation each time a user comes to this category search page, assuming I had unlimited ram and cpu power. However I don't have that, and doubt I'd want to waste the resources if I had.
I've read of people creating an extra field, or table, where they stored the counts and just pulled them up when the user goes to the cat field. And then they'd figure out how often to update those counts based on the activity of the site (activity as in how often will they change).
I was just curious if this was the common approach, and if not, what would you do to ensure you were as accurate as possible without bringing down your SQL server because of CPU usage.
Thanks All...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

This might be a case where a trigger is useful. Have the trigger update the "Total" table whenever there's a change in inventory.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 2

I agree with Jeff - a trigger would be useful to calculate the amount next to each category. It comes down to how often you'll be calculating this value (likely a lot - every single page display!) compared with how often the data will change (not quite as much).

A trigger would keep it fairly accurate, but if there's a lot of updates, you might be calculating it way too much. In this case, a scheduled job to run every few minutes to calculate the correct amounts might be the way to go.

Of course then you might be calculating categories that haven't changed, so maybe you need a flag to say "recalculate me".

sqlPirate_Fl SSC-Addicted Points: 442 More actions · Answer 3

Thanks. I thought about triggers but was concerned about the over head from the possible updates.

Didn't think about the option of a re-calculate flag.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

chuck80 (7/22/2010)
Thanks. I thought about triggers but was concerned about the over head from the possible updates.
Didn't think about the option of a re-calculate flag.

The trigger would only calculate the running totals for the new rows assuming that you don't backdate anything.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)