0

Currently my team develop additional web app to let customers analyze user statistics to theirs portals.

So we have a table with all logins. It contains about 100 millions of rows. One user could return to the portal during the day multiple times.

This table structure something like:

| UserId | LoginDate |

Also we have a hierarchy of customers, portals and countries, it looks like:

Users           | Customers      | Portals         | Countries
FK-> CustomerId   
                  FK -> PortalId   
                                   FK -> CountryId   

Base on user activity we are building daily incremental snapshots to prevent using big login history table. And we store there Total Logins, Unique logins etc. This allows us show nice chart to the end users.

But now we are facing on interesting issue with this. Currently we need implement total Unique login for period. So the user select period, and see this total.

And this cause the story goes on. We can't just sum snapshots total Unique logins, it won't be a real unique. And we can't count each time from history table, because of performance. And I don't have idea how to cache totals.

1 Answer 1

1

How about an indexed view?

CREATE VIEW dbo.LoginsByDate
WITH SCHEMABINDING
AS
  SELECT 
    UserID, 
    LoginDate = CONVERT(DATE, LoginDate), 
    LoginCount = COUNT_BIG(*)
  FROM dbo.LoginTable
  GROUP BY UserID, CONVERT(DATE, LoginDate);
GO
CREATE UNIQUE CLUSTERED INDEX IX_LoginsByDate
  ON dbo.LoginsByDate(UserID, LoginDate);
GO

What this does is pre-aggregate the login data for you (you pay the cost at write time), and flattens it into one row per UserID per LoginDate. Depending on how many times a user logs in in a given day, and how often you run these reports, this can be substantially more efficient than calculating those at query time (and much less hassle than running your own nightly report that aggregates the data and stores it somewhere).

Now, to get the total login events by day:

SELECT LoginDate, SUM(LoginCount)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

To get the distinct logins by day:

SELECT LoginDate, COUNT(UserID)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

Of course if you're looking for individual user activity, you can do that too, and you can easily aggregate the data to get whatever "total unique" really means to you. If UserID = 1 logs in today 4 times and tomorrow twice, is that 1 total unique (unique per entire time frame) or 2 total uniques (unique per day)?

If the former:

SELECT COUNT(DISTINCT UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd'
    AND LoginDate < 'yyyymmdd';

If the latter:

SELECT SUM(c) FROM
(
  SELECT LoginDate, c = COUNT(UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd' 
    AND LoginDate < 'yyyymmdd'
  GROUP BY LoginDate
) AS x;
2
  • Looks good. Im gonna add to view more fields with ids of Customer, Portals and Countries. Also Could this make impact on insertion to the history table?
    – Pavel
    Jul 21, 2015 at 14:46
  • @Pavel if you add too many columns to the indexed view, you defeat the purpose - it becomes almost as large as the source table. Are customer/portal/country something that is constant for a given UserID? Is it normalized? If so, just join during the query, there is no gain to storing that kind of data redundantly. If those are different types of reports, potentially create separate indexed views. Jul 21, 2015 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.