spring boot - Query run fast from Mysql CLI But very slow on R2dbcTemplate Code

Currently, I having a query

SELECT
  al.category,
  IFNULL(jt.name, 'Ungrouped Devices') AS device_group_name,
  IFNULL(jt.id, '00000000-0000-0000-0000-000000000000') AS device_group_id,
  jt.color_code AS color_code,
  COUNT(*) AS total
FROM
  alerts AS al,
  JSON_TABLE( CAST(al.device_groups AS JSON),
    '$[*]' COLUMNS ( name VARCHAR(255) PATH '$.device_group_name',
      id VARCHAR(255) PATH '$.device_group_id',
      color_code VARCHAR(255) PATH '$.color_code' ) ) AS jt
WHERE
  account_id = 1000000000
GROUP BY
  category,
  device_group_name,
  device_group_id,
  color_code

When I run the query from mysql cli give execution path

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (actual time=41540.274..41554.003 rows=1956 loops=1)
    -> Aggregate using temporary table  (actual time=41540.265..41540.265 rows=1956 loops=1)
        -> Nested loop inner join  (actual time=0.192..15244.892 rows=676934 loops=1)
            -> Filter: ((al.alert_type_id <> 40) and (al.alert_type_id <> 90) and (al.event_time >= TIMESTAMP'2024-11-22 00:00:00') and (al.event_time < TIMESTAMP'2025-02-20 00:00:00'))  (cost=7946.96 rows=75045) (actual time=0.034..6171.038 rows=676934 loops=1)
                -> Index lookup on al using PRIMARY (account_id=1091000508)  (cost=7946.96 rows=216200) (actual time=0.031..4714.982 rows=676934 loops=1)
            -> Materialize table function  (actual time=0.012..0.013 rows=1 loops=676934)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (41.90 sec)

However, when I run the same query in code

    template
        .getDatabaseClient()
        .sql(sql)
        .map(EVENT_CATEGORY_METRIC_ROW_MAPPER)
        .all();

which takes more than 5 mins.

I try to rerun the query from DBeaver client which give result (query run more than 5 mins same as from code).

-> Table scan on <temporary>  (actual time=355837.677..355838.448 rows=1956 loops=1)
    -> Aggregate using temporary table  (actual time=355837.668..355837.668 rows=1956 loops=1)
        -> Nested loop inner join  (actual time=0.120..7819.864 rows=676934 loops=1)
            -> Filter: ((al.alert_type_id <> 40) and (al.alert_type_id <> 90) and (al.event_time >= TIMESTAMP'2024-11-22 00:00:00') and (al.event_time < TIMESTAMP'2025-02-20 00:00:00'))  (cost=7207.37 rows=67649) (actual time=0.050..3442.970 rows=676934 loops=1)
                -> Index lookup on al using PRIMARY (account_id=1091000508)  (cost=7207.37 rows=216200) (actual time=0.043..2399.595 rows=676934 loops=1)
            -> Materialize table function  (actual time=0.006..0.006 rows=1 loops=676934)

Note the cli and code/DBeaver run in a same host. It seem that execution paths are the same. Why would the query from cli run much faster than the one from other code/DBeaver?

Currently, I having a query

SELECT
  al.category,
  IFNULL(jt.name, 'Ungrouped Devices') AS device_group_name,
  IFNULL(jt.id, '00000000-0000-0000-0000-000000000000') AS device_group_id,
  jt.color_code AS color_code,
  COUNT(*) AS total
FROM
  alerts AS al,
  JSON_TABLE( CAST(al.device_groups AS JSON),
    '$[*]' COLUMNS ( name VARCHAR(255) PATH '$.device_group_name',
      id VARCHAR(255) PATH '$.device_group_id',
      color_code VARCHAR(255) PATH '$.color_code' ) ) AS jt
WHERE
  account_id = 1000000000
GROUP BY
  category,
  device_group_name,
  device_group_id,
  color_code

When I run the query from mysql cli give execution path

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (actual time=41540.274..41554.003 rows=1956 loops=1)
    -> Aggregate using temporary table  (actual time=41540.265..41540.265 rows=1956 loops=1)
        -> Nested loop inner join  (actual time=0.192..15244.892 rows=676934 loops=1)
            -> Filter: ((al.alert_type_id <> 40) and (al.alert_type_id <> 90) and (al.event_time >= TIMESTAMP'2024-11-22 00:00:00') and (al.event_time < TIMESTAMP'2025-02-20 00:00:00'))  (cost=7946.96 rows=75045) (actual time=0.034..6171.038 rows=676934 loops=1)
                -> Index lookup on al using PRIMARY (account_id=1091000508)  (cost=7946.96 rows=216200) (actual time=0.031..4714.982 rows=676934 loops=1)
            -> Materialize table function  (actual time=0.012..0.013 rows=1 loops=676934)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (41.90 sec)

However, when I run the same query in code

    template
        .getDatabaseClient()
        .sql(sql)
        .map(EVENT_CATEGORY_METRIC_ROW_MAPPER)
        .all();

which takes more than 5 mins.

I try to rerun the query from DBeaver client which give result (query run more than 5 mins same as from code).

-> Table scan on <temporary>  (actual time=355837.677..355838.448 rows=1956 loops=1)
    -> Aggregate using temporary table  (actual time=355837.668..355837.668 rows=1956 loops=1)
        -> Nested loop inner join  (actual time=0.120..7819.864 rows=676934 loops=1)
            -> Filter: ((al.alert_type_id <> 40) and (al.alert_type_id <> 90) and (al.event_time >= TIMESTAMP'2024-11-22 00:00:00') and (al.event_time < TIMESTAMP'2025-02-20 00:00:00'))  (cost=7207.37 rows=67649) (actual time=0.050..3442.970 rows=676934 loops=1)
                -> Index lookup on al using PRIMARY (account_id=1091000508)  (cost=7207.37 rows=216200) (actual time=0.043..2399.595 rows=676934 loops=1)
            -> Materialize table function  (actual time=0.006..0.006 rows=1 loops=676934)

Note the cli and code/DBeaver run in a same host. It seem that execution paths are the same. Why would the query from cli run much faster than the one from other code/DBeaver?

Share Improve this question asked 16 hours ago titanium 551 silver badge6 bronze badges

1. fetch size (default is 10, increase to something larger), 2. mapping, the cli doesn't do mapping whereas your r2dbc code does and you are counting this as well. – M. Deinum Commented 16 hours ago
I don't think the mapping is the problem here. The result is only for more than 1000 rows, mapping these should not take that much time. Moreover, when I do in DBeaver (as I mentioned) the result still show more than 5 minutes run time. And from the execution paths, the two clients already show different results which I can't find out why – titanium Commented 15 hours ago
Enable General log and compare the queries texts literally. Enable profiling and compare execution profiles. Also compare retrieve and compare SQL modes. PS. If device_groups contains valid JSON then CAST is excess. PPS. Do not use a comma, use CROSS JOIN instead. – Akina Commented 15 hours ago
Does both plans were obtained on the same MySQL server instance? – Akina Commented 15 hours ago
@Akina The log from Cloud logging show the same code and from the execution plans the two are same but run time are significant different – titanium Commented 15 hours ago

| Show 2 more comments

1 Answer 1

Sorted by: Reset to default 0

in general something that use GUI will be heavier, i bet

GUI tools like DBeaver retrieve and display results within a user interface, which can introduce additional processing time.
CLI tools output results as plain text, making the process significantly faster.
Executing the query in the CLI first may prime the database cache, improving performance.
When the same query is later run in DBeaver, it may seem slower due to the absence of cached data.

Last, more memory will be used for rendering.

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

spring boot - Query run fast from Mysql CLI But very slow on R2dbcTemplate Code - Stack Overflow

1 Answer 1

与本文相关的文章

评论列表(0)