SQL: using count() vs. keeping a separate field

I have a question regarding SQL performance a开发者_开发知识库nd was hoping someone would have the answer.

I have the database table tbl_users and I want to get the total number of users I have. I could write it as SELECT COUNT(*) FROM tbl_users. I presume such query would have performance implications were I to have a handful of users vs. several millions of them. (So, assumption #1 is that the more rows I have, the more resources this query will consume).

In this particular case I need to run this query at a relatively high frequency and each time I need to get up-to-date data (so, caching is not an option).

Assuming my assmption #1 is correct, I then thought of structuring it the following way:

• create tbl_stats with a field userCounter
• each time there is an insert in tbl_users, userCounter is updated +1
• each time I need to get my user count, I can pull that one field from tbl_stats

Now, I realize that by doing it this way, the data in userCounter is technically a duplicate, which is bad form.

So, will my first query (assuming millions of rows of data) consume that many resources to warrant me to implement my alternative design? If so (or if possibly yes), then is my alternative design consistent with best practices?

If your table is indexed, which it almost certainly will be, then the performance of select count(*) probably will not be as bad as you might anticipate - even if you have millions of rows.

But, if it does become a concern, then rather than roll your own solution, look into using an indexed view.

I have a database table with almost 5 million records, the following query returns in less than a second

select count(userID) from tblUsers

This query returns in 2 seconds

select count(*) from tblUsers

I'd personally just go with select count() rather than creating a duplicate field

I think this is one of those scenarios where you really need to measure the performance to make a good decision. I would wager that a simple COUNT() isn't going to create enough latency that you would need to implement your proposed work-around.

If you are worried I would encapsulate your COUNT() in a function or stored procedure so you can quickly swap it out later if performance does become a problem.

On some systems you can ask the system to maintain the counts for you. For example, in SQL Server you can have an indexed view on the count:

create view vwCountUsers
with schema binding
as
select count_big(*) as count
from dbo.tbl_users;

create clustered index cdxCountUsers on vwCountUsers (count);

The system will maintain the count for you and will always be available at nearly no cost.

If you have a desperate need and a real business case for up to the minute accurate counts, then the trigger would be the way to go. Just make sure it caters for all multi-user issues such as concurrency and transactions.

It could become a bottleneck because instead of 5 transactions being able to insert into a new table, they will queue up waiting to update the userCounter table, and you may even get deadlocks.

There are other options for less accurate counts, but if you want accurate then there are very few other choices, but I'll try to think of some:

1. You could partition the data and in userCounter store a count by day. If the data only gets added for the current day, do a select sum(dailycount) from counter + select count(*) from table where {date=today}

2. You could at least use the nolock or readpast options to lessen resource usage:

select * from tbl with (readpast)
select * from tbl with (nolock)

There are somethings it makes sense to precalculate for performace reasons (comlex calculations over years of data). That's why data warehouses exists much of the time to speed reporting. Select count(*) is generally not one of them if you have any indexing on the table at all. There are far worse performance problems to solve than that. I get 1 second to return the count on a table with 13 million rows.

I'm all about writing code that will is more likely to perform well than the alternative (avoiding correlated subqueries, using set-based operatiosn instead of cursors, having sargable where clauses), but this is a mirco optimization that should not be addressed until there is a real performance problem.