Question 388950 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Should I convert timestamps in the database or service layer in a spring boot application?

I have a question about data performance

time value is stored as long in my spring boot application.

When I retrieve multiple data, I convert the time using Date_Format FROM_UNIXTIME like below

 @Query("SELECT new today.feeling.dto.response.emotion.EmotionMonthlyReportQuery(" +
        "s.id, " +
        "s.studentId, " +
        "s.name, " +
        "CAST(DATE_FORMAT(FROM_UNIXTIME(e.emotionTime / 1000, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d') AS string) AS emotion_date, " +
        "CAST(SUM(CASE WHEN e.emotionType = 1 THEN e.emotionLevel ELSE 0 END) / COUNT(CASE WHEN e.emotionType = 1 THEN 1 END) AS FLOAT), " +
        "CAST(SUM(CASE WHEN e.emotionType = 2 THEN  e.emotionLevel ELSE 0 END) / COUNT(CASE WHEN e.emotionType = 2 THEN 1 END) AS FLOAT), " +
        "CAST(SUM(CASE WHEN e.emotionType = 3 THEN e.emotionLevel ELSE 0 END) / COUNT(CASE WHEN e.emotionType = 3 THEN 1 END) AS FLOAT), " +
        "CAST(SUM(CASE WHEN e.emotionType = 4 THEN e.emotionLevel ELSE 0 END) / COUNT(CASE WHEN e.emotionType = 4 THEN 1 END) AS FLOAT)) " +
        "FROM Emotion e " +
        "JOIN Student s ON s.id = e.student.id " +
        "WHERE s.isRemoved = FALSE AND s.classroom.id = :classroomId AND s.year = :year AND e.emotionTime BETWEEN :startDate AND :endDate " +
        "GROUP BY s.id, emotion_date " +
        "ORDER BY s.studentId, emotion_date")
List<EmotionMonthlyReportQuery> getEmotionMonthlyReportList(@Param("classroomId") Long classroomId,
                                                            @Param("year") Integer year,
                                                            @Param("startDate") long startDate,
                                                            @Param("endDate") long endDate);

Is it better to convert time in DB directly or in service layer?

I would like to know which approach is better in terms of performance or code separation

Tags:

2

Answers


  1. 0

    Does using a function to convert data to a field cause index failure? Of course, if the amount of data is relatively small, it should be fine.
    Performance scalability is much better if the conversion is performed in the Service layer code after detection (such as using Redis caching to improve performance).
    The SQL you wrote, I took a look, may cause temporary tables, and even file sort oh. I’m afraid the performance will be a big problem.
    In short, without considering the premise of performance, it is quite convenient to use MySQL functions.

    Login or Signup to reply.
  2. 0

    (This was NOT AI generated!)

    SELECT expression ... versus SELECT columns, then evaluate an equivalent expression: The performance difference is negligable.

    If your question is about speed of that one query, there are several other issues that are more pressing — Let’s see the generated SQL.

    The WHERE clause is the first point for optimizing. Let’s see SHOW CREATE TABLE.

    Once we optimize it and a good composite index for it, maybe we need to work on the GROUP BY:

    • GROUP BY s.id, emotion_date is the same as GROUP BY e.student_id, e.emotion_date, correct?
    • But would this be just as good? GROUP BY e.student_id, e.emotion_date/1000, correct.

    Using expressions (such as FROM_UNIXTIME) in the SELECT partare not significantly faster or slower than in the app. On the other hand, expressions involving columns inWHERE, GROUP BY, or ORDER BY` may be detrimental to performance.

    For more discussion, also provide EXPLAIN SELECT ...

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search