CODEWITHSUNDEEP
mysqlspring-bootmysql-clir2dbc-mysql
titanium
titanium

Reputation: 55

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?

Upvotes: 0

Views: 51

Answers (1)

Maulana Aghni
Maulana Aghni

Reputation: 1

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.

Upvotes: 0

Related Questions