Developing an optimal solution/design that sums many database rows for a reporting engine

Question

Problem: I am developing a reporting engine that displays data about how many bees a farm detected (Bees is just an example here)

I have 100 devices that each minute count how many bees were detected on the farm. Here is how the DB looks like:

So there can be hundreds of thousands of rows in a given week.

The farmer wants a report that shows for a given day how many bees came each hour. I developed two ways to do this:

1. The server takes all 100,000 rows for that day from the DB and filters it down. The server uses a large amount of memory to do this and I feel this is a brute force solution

2. I have a Stored Procedure that returns a temporarily created table, with every hour the amount of bees collected for each device totaled. The server takes this table and doesn't need to process 100,000 rows.

This return (24 * 100) rows. However it takes much longer than I expected to do this ~

What are some good candidate solutions for developing a solution that can consolidate and sum this data without taking 30 seconds just to sum a day of data (where I may need a months worth divided between days)?

Answer 1

If performance is your primary concern here, there's probably quite a bit you can do directly on the database. I would try indexing the table on time_collected_bees so it can filter down to 100K lines faster. I would guess that that's where your slowdown is happening, if the database is scanning the whole table to find the relevant entries.

If you're using SQL Server, you can try looking at your execution plan to see what's actually slowing things down.

Give database optimization more of a look before you architect something really complex and hard to maintain.