Geek Answers Handbook

Creating a variable in the database to store global statistics

Suppose I have a social network. I always show the user how many users are registered and activated their profile. Thus, every time one user logs in, he goes to the database and does:

select count(*) from users where status = 'activated'

therefore, if 5,000 users log in or just refresh the page, this will make 5,000 SQL queries higher. I was wondering if it’s better to have a variable in some place (which I still don’t know where to put), that every time a user activates his profile, he adds 1, and then when I want to show how many users are registered in this social network, I only get the value of this variable.

How can i do this? Is this really the best solution for what I have?

+5
source share
5 answers

You can use an indexed view that SQL Server will automatically support:

create table dbo.users (
    ID int not null,
    Activated bit not null
)
go
create view dbo.user_status_stats (Activated,user_count)
with schemabinding
as
    select Activated,COUNT_BIG(*) from dbo.users group by Activated
go
create unique clustered index IX_user_status_stats on dbo.user_status_stats (Activated)
go

These are just two possible states, but can expand to more use using a different data type. As I said, in this case, SQL Server will support backstage calculations, so you can simply request a view:

SELECT user_count from user_status_stats with (NOEXPAND) where Activated = 1

and he won’t have to query the base table. You need to use the tooltip WITH (NOEXPAND)for the editions below (Enterprise / Developer).

, @Jim, COUNT (*) , () , , .

+2

- , - .

, , , , , .

, SQL .

# 2 - MemCache , , , .

+1

, :

1), , , , . "" .

2) "" , , " " - . , . , "" , , , , .

, . , - , .

0

SQL Server , . , , Facebook. (, ) . , , ...

, . SQL , . ( ). int, smallint tinyint, , , -. ( "" ..), . , SQL .

, , ( ), . ( SQL , ), asp.net, Application, . Session_Start Session_End. , .

0

You can also use the global temporary table . You will always get a quick search. Even if you set a 30 second ping. Example Trigger Link1 , Example Trigger Link2 will support such actions in this table.

0
source

More articles:


All Articles