# SQL Query performance logging

**URL:** <https://discourse.rom-rb.org/t/sql-query-performance-logging/458>\
**Category:** Usage Stories\
**Created:** [April 29, 2021, 4:50pm UTC](https://discourse.rom-rb.org/t/sql-query-performance-logging/458 "2021-04-29T16:50:12Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![apohllo](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/apohllo/32/86_2.png) [@apohllo](https://discourse.rom-rb.org/u/apohllo)\
**Post date:** [April 29, 2021, 4:50pm UTC](https://discourse.rom-rb.org/t/sql-query-performance-logging/458/1 "2021-04-29T16:50:13Z")

</div>

I would like to know which parts of my application are slow. Is there a way to configure ROM or is there a separate tool, that would log the long queries?  
I am using Hanami 1.3, but I substituted its DB layer with ROM configured via dry-system.

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [May 4, 2021, 8:03am UTC](https://discourse.rom-rb.org/t/sql-query-performance-logging/458/2 "2021-05-04T08:03:34Z")

</div>

@apohllo everything that works with Sequel will work with rom-sql. How exactly are you planning to monitor this?

---

<div class="post-metadata">

**Author:** ![apohllo](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/apohllo/32/86_2.png) [@apohllo](https://discourse.rom-rb.org/u/apohllo)\
**Post date:** [May 4, 2021, 1:02pm UTC](https://discourse.rom-rb.org/t/sql-query-performance-logging/458/3 "2021-05-04T13:02:42Z")

</div>

Actually I have identified the primary reason for very bad application performance.

For an unknown reason hanami-reloader and shotgun sneaked into production env. causing e.g. ROM container to be instantiated for each request (even though the entry was memoized…). As a result at each request all DB-based metadata were queried for yielding a huge DB traffic.  
The performance gains of removing these two gems were dramatic.

But in future I would like to have a similar feature as in Rails, i.e. a number showing how much time did the request spend performing DB queries. One aggregate value for the full request would be enough. If there is an easy way to do so, I would like to know it. If there’s not, this is not a big issue at the moment.

BTW I know about logging queries to STDOUT or file, but it is not easy to parse and aggregate. It’s also a performance hit. I use it only for debugging purposes during development.

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [May 5, 2021, 8:09am UTC](https://discourse.rom-rb.org/t/sql-query-performance-logging/458/4 "2021-05-05T08:09:00Z")

</div>

@apohllo I’m deeply passionate about logging and monitoring, so you can be sure this will be 1st-class stuff in rom 6.0 and hanami 2.0. I already started working on a dedicated logger, we also have dry-monitor and a notification system in place. I just need to wire all of this together 🙂You _can_ do it already but it needs some manual setup:

```ruby

    rom_config = ROM::Configuration.new(:sql, container[:settings].database_url)

    rom_config.plugin(:sql, relations: :instrumentation) do |plugin_config|D
      plugin_config.notifications = notifications
    end

```

Where `notifications` is an instance of [`Dry::Monitor::Notifications`](https://github.com/dry-rb/dry-monitor/blob/master/lib/dry/monitor/notifications.rb), so you can subscribe to its events and you’ll get info about queries, time spent, context etc. For example, here’s a built-in [`Dry::Monitor::SQL::Logger`](https://github.com/dry-rb/dry-monitor/blob/master/lib/dry/monitor/sql/logger.rb#L53-L55) that subscribes to such notifications.

This is actually “old stuff” but I never got around to wrapping it all up and polishing it. It’s one of my priorities for H2 now so the time has come finally 🙂

---

<div class="post-metadata">

**Author:** ![apohllo](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/apohllo/32/86_2.png) [@apohllo](https://discourse.rom-rb.org/u/apohllo)\
**Post date:** [May 5, 2021, 9:36am UTC](https://discourse.rom-rb.org/t/sql-query-performance-logging/458/5 "2021-05-05T09:36:53Z")

</div>

Well, it looks really simple, so I will definitely try it out.

I am very thrilled about Hanami 2.0. I’m not asking about the timeline, but I hope it will be available soon 😉

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [May 10, 2021, 7:50am UTC](https://discourse.rom-rb.org/t/sql-query-performance-logging/458/6 "2021-05-10T07:50:52Z")

</div>

> [@apohllo](#):
>
> I am very thrilled about Hanami 2.0. I’m not asking about the timeline, but I hope it will be available soon 😉

Give 2.0.0.alpha2 a try and report any issues/suggestions!
