Save Items Count to database or run Select Count statement?

Question

0.00/5 (No votes)

See more:

, +

Hello everyone!

I have 5 items for which 5 different tables are made in the database. On login, the page showing the no. of items the user has saved will be shown to him.

For this,
(1) should I save the no. (int) in the database adding one more column for each one and updating the no. each time an item is added or deleted

OR

(2) should I run a Stored Procedure containing 5 different SELECT COUNT(*) queries each time that page is called?
Please clear my doubt.

According to me, I should go with the first option to add columns to the database and update them and Avoid running SELECT COUNT(*) queries every now and then.
Please correct me if I am wrong.

Posted 12-Feb-12 2:22am

Tech Code Freak

Updated 12-Feb-12 2:34am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-02-12T02:28:00

Solution 1

I would not add a column to tell how many items - use count as it can't get out of step.

But either your database design is very odd, or you have described it very badly. Why would you have five tables for different objects if (as it seems from the description) you can only have one row in each table?

Posted 12-Feb-12 2:28am

OriginalGriff

Comments

Tech Code Freak 12-Feb-12 8:32am

Thanks for the reply!
I have 5 tables for the items which individually contain 8-10 columns each. Maybe my description was a little vague..
Please reply.
I think that select count(*) will take more time for execution. Also, I will have to use output parameters for the procedure which lengthens the code too. Instead, saving count in the table will take comparatively lesser time to run select itemCount from tab1 where id=@id.

OriginalGriff 12-Feb-12 9:27am

I still don't exactly see what you are trying to do - do you intend that each table should have one row of 8-10 columns?

Tech Code Freak 12-Feb-12 11:43am

Not one row. The table will contain a list of different users and the specifications that they provide with that type of their item.
eg: TableItem1
ItemId UserId Spec1 Spec2 Spec3 .....
1 1 sp83 sp54 sp54 .....
2 1 sp76 ......................
3 2 .... .........

and so on..

OriginalGriff 12-Feb-12 14:07pm

Then I would definitely go with the "count it as you go along" route, rather than include a count in the row itself. It may seem faster to count them and store them, but the difference is going to be pretty trivial, except when you want to update the record - when you would have to read it first, count it and write the count as well. There is also a risk of someone forgetting to update the count when they add or remove a specification.

Tech Code Freak 13-Feb-12 0:06am

Thanks again for the reply!
While updating, the no. will just be incremented/decremented from the existing no. in that column. The updating is all done by coded Stored Procedures so there's no chance of forgetting to update it.
I have made a mistake in describing it. Instead of adding the 5 columns to the 5 tables, they must be added to the User table so that each time he adds/removes an item, then that column will be incremented/decremented. And also, there will be no chance to count the records every now and then. I'll just have to read one particular cell each time(select NoItem1 from User where Id=@id).
Sorry for that mistake!

Tech Code Freak 13-Feb-12 3:20am

Pls reply.

OriginalGriff 13-Feb-12 3:28am

Sorry - it's 08:20 here, and I'm just wading through my emails... :laugh:

I would still suggest that an error-proof count-on-demand method is a better solution. At present there is no rtisk of mistakes in your system, but that doesn't mean that someone else doesn't modify your code in a months time and bugger the lot up!
Plus the very up/down logic needs to have checks in to ensure that if someone does manage to make a mistake, it doesn't propagate through the system - so it isn't (or shouldn't be) just a case of
UPDATE myTable SET counter=counter-1, ...
and
UPDATE myTable SET counter=counter+1, ...
Which means a read, check, write cycle on the DB.

Unless you are counting serious numbers of whole records it really isn't worth the hassle!

Tech Code Freak 13-Feb-12 3:33am

Thank You so much!