Limited SQL support?

[ITcanuck]

The sql_exporter works great with many simple queries. I'm trying to use a more complex query that takes maybe 10-15s in SQL but even with a 5 minute timeout never returns results with the exporter so am wondering if there are limitations to what is and what isn't supported.

My query goes something like this:

  WITH RankedData AS (
  select [Path], ON, CN, IN, SV, TS,
        ROW_NUMBER() OVER (PARTITION BY [Path], ON, CN, IN ORDER BY TS DESC) AS rn
  from database.table pdv
  inner join database.table1 pcv on pdv.something = pcv.somethingelse
  inner join database.table2 bme on pcv.something = bme.somethingelse)
  SELECT [Path], ON, CN, IN, SV, TS
  FROM RankedData
  where rn = 1

TIA

[ITcanuck]

reworked / optimized query and seeing results now

[burningalchemist]

@ITcanuck It shouldn't be limited (unless there's something special), which in turn makes it sensitive to the SQL query cost. 👍

The idea of this implementation (and my current vision) is that a database generally has resources to perform calculations/aggregations on its end efficiently, and sql_exporter's job is mainly to request, and prepare the received data to be ingested by Prometheus. This also makes sql_exporter use less resources, so it scales better.

[keithf4]

I found materialized views can help with more expensive queries that cannot be optimized well for monitoring