Designing Temporal Separation for Reusable Queries

1. Problem Situation

I was implementing a function to view a list of specific users on the admin page, using a retrieval logic that had many conditions and complex sorting criteria.

In the retrieval process, not only was data simply being fetched, but also target condition calculations, state determinations, and sorting processes were being performed simultaneously,The same operations are repeated every time a request comes in.was.

At first, I thought it was just a simple query function, but I started to notice that it keeps recalculating the same data every time a request is made.

Although the data does not change in real-time due to its nature, Rebuilding the entire dataset with each request is inefficient in terms of operational efficiency.I felt that way.

This structure had the following problems.

• Repeated calculation of the same data

• Increased unnecessary DB queries

• Repeated execution of complex sorting and filtering logic

• Potential performance degradation as data volume increases

Rather than simply optimizing the query, it was necessary to rethink the entire flow of generating and using data.

2. Approach

The data had the characteristic of not undergoing significant changes over the course of a day.

Based on this point, instead of calculating the data with each request, Data is generated on a daily basis and structured to be reused later.has been applied.

In other words, instead of a structure that calculates data at each query point, we have changed the approach to generate data only when needed and to reuse already generated data.

The most important aspect considered in this process is When will the data be generated? was.

I concluded that a structure that allows for clear management of the creation time is needed, rather than a simple caching method that temporarily stores data in memory.

3. Implementation method

3.1 Data Structure Separation

We divided the data into two types to apply a daily reusable structure.

The reference date data serves to determine whether data generation has already been performed as of the current date.

On the other hand, the data to be queried stores the data that is reused during the actual query.

In this way, Separating data that manages the creation status from the actual query dataI tried to keep the data flow simple.

Moreover, since situations such as application restarts or multiple requests can occur in the operating environment, I concluded that managing based on the database is more appropriate than memory-based management.

3.2 Data Generation and Reuse Flow

When a request comes in, we first check if data based on the current date already exists.

If there is no data for the specified date, the entire data will be generated on the first request.

Subsequently, the system is configured to use the already generated data for queries.

The processing flow is as follows.

1. Check for the existence of data based on the current date

2. If there is no data, perform full data generation

3. If data exists, perform a query without creation

4. Return of query results

Through this structure Data generation is performed only once initially, and thereafter follows the reuse flow completed.

In this structure, especially, instead of having a separate scheduler, it is designed to generate data naturally at the initial request time.I wanted to keep the flow simple.

3.3 Data Generation Logic

When generating data, it is not simply a matter of deleting all existing data and creating new ones,but rather reorganizing based on the latest data while maintaining some of the existing state.Designed to do so.

The following tasks are performed in the generation process.

• Remove obsolete existing data

• Reorganize target data based on the latest standards

• Maintain some existing status values

• Register reference date data

I particularly considered the part that maintains the existing state value to be important. If the data is completely initialized each time, the information modified by the administrator could also be lost.It is. Therefore, I configured it to keep only the necessary values after checking the existing data.

4. Design Intent and Effects

The comparison between the existing method and the improved structure is as follows.

Not only reducing the number of queries through this structureSeparation of data generation and retrieval timingI was able to.

In particular, this structure takes into account the data characteristics where real-time performance is not particularly importantDesigned to prioritize reuse efficiency over computational costI did it.

Also, it was possible to manage the data based on the creation time while keeping the query flow itself simple.

5. Retrospective

At first, I thought it was just a simple query function, but during the implementation process, I encountered the question, 'Why am I continuously recalculating the same data?'

And as I followed that question, I realized that what is more important than simple query optimization is the question of 'when to create data and how to manage it.'

Through this work, I found that performance improvement is not just about making retrieval speed faster, but I was able to learn that it is the process of redesigning the data flow itself.I was able to learn that.

I also experienced that in addition to the query logic, considering the data generation timing, state maintenance methods, and reuse structures is essential to create a stable operational structure.

zero