How to Speed Up Legacy Report Queries with Redis Archiving and Scheduled Tasks

Background

The client purchased a WAF firewall product, and as the system processed more requests, the volume of log data grew, causing report pages to load slowly and providing a poor user experience.

Technology Stack

SSM + Gateway + Redis + Kafka + MySQL. The Gateway handles security filtering and rate limiting, forwards request parameters and security status to Kafka, which stores the logs in the database.

Problem Statement

The existing reporting logic was a monolithic method of nearly 1,000 lines with obscure variable names (e.g., format1, data1), making it difficult to understand and maintain. Reports were generated by direct SQL queries; while acceptable for small datasets, performance degraded sharply as data volume increased, and index tuning offered no relief.

Optimization Idea

Without introducing new processing frameworks, the author chose to archive log data by hour and event status, storing aggregated counts in Redis. When a new log arrives, the system checks whether an hourly record for the same status exists; if so, it increments the count, otherwise it creates a new entry. This reduces the amount of data scanned during queries.

Implementation Details

The archiving method formats Redis keys to distinguish site‑specific and global statistics, then uses redisService.increment or redisService.setValueByHour to update counts. Two scheduled tasks synchronize cached data back to MySQL:

@Override
public void handleWebEventStatus(Log log) {
    String siteId = antispiderDetailLog.getSiteId();
    Date curr = new Date();
    DateTime beginOfHour = DateUtil.beginOfHour(curr);
    Integer eventStatus = log.getAntispiderRule().intValue() == 0 ? 0 : 1;
    String cacheKey = StrUtil.format(RedisConstant.REPORT_WEB_TIME_EXIST, siteId,
        DateUtil.format(beginOfHour, timeFormat), eventStatus.intValue());
    String cacheKeyAll = StrUtil.format(RedisConstant.REPORT_WEN_TIME_ALL,
        DateUtil.format(beginOfHour, timeFormat), eventStatus.intValue());
    if (redisService.exist(cacheKeyAll)) {
        redisService.increment(cacheKeyAll, 1L);
    } else {
        redisService.setValueByHour(cacheKeyAll, 1, 2L);
    }
    if (redisService.exist(cacheKey)) {
        redisService.increment(cacheKey, 1L);
    } else {
        redisService.setValueByHour(cacheKey, 1, 2L);
    }
}

@Scheduled(cron = "0 0/30 * * * ?")
public void synRedisDataToDB() {
    synchronized (lock) {
        reportWebEventStatusService.synRedisDataToDB();
        reportWebEventTopService.synRedisDataToDB();
        reportWebIpTopService.synRedisDataToDB();
    }
}

@Scheduled(cron = "0 0 0/1 * * ?")
public void synAllSiteWebEventDataToRedis() {
    synchronized (lock) {
        synReportWebDataToRedis();
    }
}

These tasks run every 30 minutes to flush aggregated statistics and every hour to refresh the 23‑hour window cache, ensuring that recent data stays in Redis while older data is persisted.

Result

After applying the archiving and caching strategy, the system reduced query time for 1.5 million log entries to under one second, achieving several‑fold performance gains compared with the original SQL‑only approach.

Conclusion

Archiving time‑based log statistics and leveraging Redis as a lightweight counter store can dramatically improve reporting performance without adding complex middleware. Projects that require daily, hourly, or custom period aggregations can adopt a similar pattern to achieve fast, scalable queries.