Conditional Count and group by with realtime data

Question

I was able to find similar and close-match questions regarding my query. But I want to know if there are better ways of doing the same.

I have a database table which is being populated by call to a web-service end-point. The end-point is called when certain event occurs and a new record is inserted to this table. The events take place in real-time and there is no set frequency or pattern of occurrence of the events.

The table looks like:

CREATE TABLE MyTbl(id int, type nvarchar(10), timestamp datetime, category nvarchar(50));

I am fetching data from the table as:

SELECT category,
COUNT(CASE WHEN type = 'sent' THEN 1 END) sent,
COUNT(CASE WHEN type = 'received' THEN 1 END) received,
COUNT(CASE WHEN type = 'blocked' THEN 1 END) blocked,
COUNT(CASE WHEN type = 'opened' THEN 1 END) opened
FROM MyTbl
WHERE timestamp >= '2013-01-01 00:00:00' AND timestamp < '2013-02-01 00:00:00'
GROUP BY category

The details about the database schema, sample data and select-query for the report is available here.

Given that the:

data is fed into the table in real-time

table will store huge volume of data approx. 10,00,000+ records

structure of the report-query will not change

data would be filtered on category, date-from and date-to (all are optional parameters)

time is not relevant, only date part is

Will it be a good idea to run a scheduled task that would run periodically and update a new table with values from MyTbl? The new table would look similar to the report query:

Date | Category | Sent | Received | Blocked | Opened

and this table would be queried by applying category and date filters.

Negatives to this approach:

We still have to maintain data on daily basis

We still have to apply GROUP BY and SUM

There may be more database operations required than the original approach

We will not get all the data that comes in real-time.

Positives to this approach:

May speed-up the records fetching from the database which can speed-up the process of display, sorting and paging

Is this a viable approach? Are there any other ways to speed up the process? Please help!

you can also try stored procedure . they execute much faster then sql query — sourabh devpura, Aug 06 '13 at 08:32
@sourabhdevpura, this is executing using stored procedure only. I used the SQL query to make it simple. — Vivek Jain, Aug 06 '13 at 08:34

podiluska · Accepted Answer · 2013-08-06T08:51:54.463

2

Yes, this is a viable approach.

You may also want to consider reconfiguring your query as a PIVOT.

select *
from 
    (select [date],category, [type] 
       from yourtable 
       where timestamp between '2013-01-01' and '2013-02-01') d
pivot 
(count (type) for [type] in (sent,blocked,received,opened)) p

edited Aug 06 '13 at 08:51

answered Aug 06 '13 at 08:33

podiluska

50,950
7
98
104

Thanks for this! This query executes faster than the original query by ~37 times! It was hard for me to believe but now I know it is awesome. Thanks again! – Vivek Jain Aug 06 '13 at 13:03

Conditional Count and group by with realtime data

1 Answers1