Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a few big tables with about 6 billion rows that I was looking to optimize. Clustered key is Epoch (unix date time which is the number of seconds that has passed after 1970) and customer ID. This table records usage data per customer per product type.

For example, if this were for a Telco, TypeID 1 is a local call and the value is how many minutes used for that customer. TypeID2 is a international call and is the value how many minutes were used in that hour for that customer. Let's say TypeID3 is a special discounted rate for domestic calling.

The data is stored in 1 hour intervals. I want the indexed view to store the aggregated 24 hour value so when we run a query for 1 day per customer, it has to only look up 1 row in the indexed view instead of 24 rows in the base table.

This is the base table:

ColRowID (bigint)
AggregateID (int)
Epoch (int)
CustomerID (int)
TypeID  (tinyint)
ErrorID (smallint)
Value (int)

We don't care about Aggregate or RowID for our reporting purposes, so I figure the indexed view will look like this:

CREATE VIEW [ixvw_AggTbl]
WITH SCHEMABINDING
AS
SELECT Epoch, CustomerID, TypeID, ErrorID, SUM(Value)
FROM DBO.BaseTbl
-- GROUP BY Epoch  (what goes here?? Epoch/86400?  If I do that I have to 
-- put Epoch/86400 in the SELECT list as well)

EDIT:

Sample base data ( i left out the columns we don't need in this case, just assume the ID columns are there). Each "TypeID" will have a value assigned to it, which the value can be 0.

For example,

    Epoch / Customer ID / TypeID / Value
    /* Epoch 90,000 is day 2 1am */

90000 (1am  day 2) / 1 / 1 / 200
90000 (1am  day 2) / 1 / 2 / 100
90000 (1am  day 2) / 1 / 3 / 120

/* Customer ID 2 as well */
90000 (1am  day 2) / 2 / 1 / 100
90000 (1am  day 2) / 2 / 2 / 50
90000 (1am  day 2) / 2 / 3 / 310

... (repeat for 30,000 customers)

/* Customer ID 1 2am day 1) */
93600 (2am day 2) / 1 / 1 / 150
93600 (2am day 2) / 1 / 2 / 0
93600 (2am day 2) / 1 / 3 / 550

/* Customer ID 2 2am day 2) */
93600 / 2 / 1 / 80
93600 / 2 / 2 / 150
93600 / 2 / 3 / 300
... (repeat for 30,000 customers)

Let's assume all the other VALUE columns are 0 for the remainder of the day since the system went down and no one could use their phones after 2am. I want my indexed view to record the value column aggregated per day, per customerID and TypeID.

Sample would be:

172800 (Day 3 midnight) / 1 / 1 / 350  --Cust ID 1 aggregated all type id 1 in the past 24 hours
172800 (Day 3 midnight) / 1 / 2 / 100
172800 (Day 3 midnight) / 1 / 3 / 670
172800 (Day 3 midnight) / 2 / 1 / 180  --Cust ID 2 now
172800 (Day 3 midnight) / 2 / 2 / 200
172800 (Day 3 midnight) / 2 / 3 / 610
--Repeat by adding 86400 to the epoch to gather the summary data of the rows for the previous day.
share|improve this question
2  
Well, what do you want the output to look like? Do you want the output for Epoch to be Epoch/86400, or a date? It can't be the original value obviously, because then you couldn't group by it... –  Aaron Bertrand Jul 9 '13 at 19:17
1  
I'm confused. You're going to GROUP BY Epoch, and also by customer, too, right? But that means you need to aggregate the other columns in the SELECT list somehow -- TypeID and ErrorID, so there is already a problem in the proposed view definition. Could you please clarify? –  Jon Seigel Jul 9 '13 at 19:20
    
@Jon I assumed the rest of the non-aggregated columns in the SELECT list will also make it into the GROUP BY once he knows how to aggregate Epoch. –  Aaron Bertrand Jul 9 '13 at 19:24
    
Thanks Aaron/Jon. Yes, i will put those in the GROUP BY but currently I'm trying to figure out how to setup a indexed view to aggregate all the values per day instead of per hour. I updated my original question with the requested output. Please let me know if it's sufficient. Thanks. –  Ali Razeghi Jul 9 '13 at 19:37
1  
Adding TypeID to your example output makes it impossible to guarantee 1 row per customer per day, for the reasons I mention in my answer. –  Jon Seigel Jul 9 '13 at 23:20
show 19 more comments

1 Answer 1

up vote 4 down vote accepted

I think there's some misunderstanding about what you're attempting to do here.

Since your current design is to return all 24 rows from the base table, presumably all the supplementary fields are returned as well (to display in a grid, or something).

In order to fully aggregate the Value column, all the supplementary columns cannot be included in the SELECT list. Alternatively, if those columns are included in the GROUP BY clause, the view would represent only a partial aggregation, as there would be one row for each unique combination of the columns in the GROUP BY column list.

The only way I see something like this being useful is if the supplementary columns aren't included in the view, and there is some other process that requires only the daily aggregated values, without the base row data. Such a view could be defined like this:

CREATE TABLE [dbo].[BaseTbl]
(
    ColRowID bigint NOT NULL,
    AggregateID int NOT NULL,
    Epoch int NOT NULL,
    CustomerID int NOT NULL,
    TypeID tinyint NOT NULL,
    ErrorID smallint NOT NULL,
    Value int NOT NULL,

    PRIMARY KEY CLUSTERED(Epoch, CustomerId)
);
GO

CREATE VIEW [dbo].[ixvw_AggTbl]
    WITH SCHEMABINDING
AS
    SELECT
        t.Epoch / 86400 AS EpochDay,
        CustomerID,
        TypeID,
        SUM(t.Value) AS TotalValue,
        COUNT_BIG(*) AS __RowCount
        FROM [dbo].[BaseTbl] t
        GROUP BY
            t.Epoch / 86400,
            CustomerID,
            TypeID;
GO

CREATE UNIQUE CLUSTERED INDEX IX_ixvw_AggTbl
    ON [dbo].[ixvw_AggTbl](EpochDay, CustomerID, TypeID);

Unfortunately, you can't go farther and convert the EpochDay column to an actual date within the indexed view because DATEADD is non-deterministic (see Aaron's comment below for why), so you'd have to convert it in the actual SELECT query against the view. But that's not too difficult.

In any event, as I said before, I'm not sure how useful this would be for your specific application.

share|improve this answer
    
DATEADD is non-deterministic on whole because certain operations (like WEEK/WEEKDAY) are dependent on session-specific settings like SET DATEFIRST. –  Aaron Bertrand Jul 9 '13 at 21:10
    
Thank you John! I updated my original question to clarify further. It looks like your solution would be very useful to convey to the customer just the value and media type ID, which is still very useful for us. I'm going to test this out right now and get back with a marked answer or further clarification. I'm going to make a smaller size table to work on, this table is very large as it is. It'll take several hours or tomorrow to get back. –  Ali Razeghi Jul 9 '13 at 21:13
    
...or possibly because you used DATEADD where the base date was a string, the interpretation of which could depend on regional settings etc. –  Aaron Bertrand Jul 9 '13 at 21:17
    
@Aaron: Yep, that's a good point. I didn't think of that. Thanks. –  Jon Seigel Jul 9 '13 at 21:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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