Performance Improvement for Querying Records and Summing Total Amount

[YuechengHao626]

Description:

Currently, the application is experiencing performance issues due to inefficient querying of records, specifically when calculating the total amount of moneyAdded over a certain time range. All records have to be traversed, even when they fall outside the required date range, leading to unnecessary computations and reduced performance as the data size grows. I'm not going to do this improvement now, because it need revise a lot of things, which breaks our code structure and database structure. However, team members can do this optimazation in the next stage.

Additionally, there is no easy way to determine the chronological order between the setDate (goal creation date) and the creation of individual records on the same day. This creates ambiguity when trying to decide which records should be included in the total amount for a given time period.

Current Structure:

Each record in the database is identified by a recordId, and records contain several fields, such as amount, category, and date (in yyyy-mm-dd format).

No timestamps are currently being used, which makes it difficult to perform efficient range queries based on time.

In the above structure, the date field uses a yyyy-mm-dd format. Due to the lack of precision (no time component), it is impossible to distinguish whether a record was created before or after the setDate on the same day, and calculating totals requires fetching and traversing all records in the database.

Issues:

Performance:

Full traversal required: In order to calculate totals for a specific date range, the system currently has to iterate through all records, including those that do not fall within the desired time range. This leads to performance degradation, especially as the dataset grows.

No chronological precision:

Without timestamps, it's challenging to determine the exact order of events when records and setDate fall on the same day, making it difficult to accurately compute totals based on creation order.

Proposed Solution:

Add a timestamp field:

Each record should store a timestamp (in Unix time, seconds or milliseconds) representing the exact moment the record was created. This would allow for efficient range queries and would make it easy to differentiate records created on the same day.

Create an index on the timestamp field:

By indexing the timestamp field in Firebase Realtime Database rules, range queries can be optimized, ensuring that only records within the required time range are fetched.

Improve querying logic:

Implement orderByChild('timestamp') and use startAt() and endAt() for querying records based on the timestamp range. This would avoid the need for a full scan of all records.

Modify existing records:

Migrate existing records by adding a timestamp field based on the available date information or by assigning the current time where precise creation time is unavailable.