私はSQLが苦手で本当に簡単なCRUDくらいなら書けますけどgroup byやらhavingやら結合やらが必要になると途端に書けなくなるポンコツ具合です。
あんまり覚える気もないので難しそうだったら誰かに頼んだり、なんとか調べて作ったり(そしてその後すぐ忘れる)。
そんな具合なものであまりにも遅かったSQLをAI Programmer(https://aiprogrammer.hashlab.jp/)に直してもらいました。
経緯
現在私はこのブログが乗っているVPSやらアプリやらの状態を監視する目的でGrafanaを使用しています。
CPUやメモリの使用率についてはPrometheusとかで見ているのでこちらはあまり関係ないのですが、アプリ側についてはDBの中身を監視しています。
そのSQLの1つがあまりにも遅かったのです。まぁ欲しい情報は取れていたのでしばらく放っておいたのですがAI Programmerが登場し、なんか改善できそうだったので試してみました。
問題のSQL
まずはテーブルの定義です。このテーブルは履歴テーブル的な感じで使用しています。
ちなみに使用しているDBはPostgreSQLです。
appdb=# \d tool_info
Table "public.tool_info"
Column | Type | Modifiers
--------------+-----------------------------+------------------------
execute_type | integer | not null
execute_name | character varying(256) | not null
name | character varying(256) |
user | character varying(256) |
memo | character varying(256) |
created_at | timestamp without time zone | not null default now()SQLがこちらです。
user、user毎の件数、created_atの最初の日時、最後の日時を取得しています。条件はexecute_typeが0以外。
長々と書いてますがこれが非常に遅かったです。
select ip_address as ip, count(*) as count, (select created_at - interval '9 hour' from tool_statistic_info where execute_type != 0 and ip_address = t.ip_address order by created_at asc limit 1) as first_use_date, (select created_at - interval '9 hour' from tool_statistic_info where execute_type != 0 and ip_address = t.ip_address order by created_at desc limit 1) as last_use_date from tool_statistic_info t where execute_type != 0 group by ip_address order by first_use_date desc;
AI Programmerに改善してもらう
AI Programmer(https://aiprogrammer.hashlab.jp/)にアクセスし、言語はSQLを選択。
処理内容として以下のように記載しました。この処理内容が変だったりうまく理解されないと何も返答してくれないのでお返事くるまで色々試してみましょう。
tool_infoというテーブルがあり、以下のカラムが存在します。 1. execute_type 2. execute_name 3. name 4. user 5. memo 6. created_at user毎の件数とuser、さらにcreated_atで昇順ソートし最初と最後のcreated_atを取得するSQLを作成してください。
生成されたSQLがこちらです。
SELECT user, COUNT(user) AS count, MIN(created_at) AS start_date, MAX(created_at) AS end_date FROM tool_info GROUP BY user ORDER BY user ASC
自分で書いてたSQLがあほに思える。というかGROUP BYの使い方わかってなさすぎだなと。
まぁそれは置いておいて出来上がったSQLでは元のSQLと少し条件が違うので手を加えていきます。
SELECT ip_address as ip, COUNT(ip_address), MIN(created_at - interval '9 hour') as first_use_date, MAX(created_at - interval '9 hour') as last_use_date FROM tool_statistic_info WHERE execute_type != 0 GROUP BY ip_address ORDER BY first_use_date DESC;
で、どれくらいSQLの速度が改善されたかというと 8984.580ms から 30.779ms です。
やっぱり元のSQLはip_address = t.ip_addressの辺りが遅かったのかな。これでサクッと情報見れるようになったのでめでたしめでたし。
しかし本当にこれすごいですね。
仕事の方でもちょっとだけ使ったことがあって、その時は正規表現でお世話になりました。
だれかが作った正規表現を別の言語に移植したかったのですがいちいち正規表現全部なんて覚えてないので都度検索して調べるのですが、AI Programmerにこの正規表現説明して！って書いたら大体の内容が把握できたのであとは移植先の言語で正規表現書きました。
使い方次第ではかなり有用だなと思いました。
コメント