Slow Data Explorer queries

Community Building Data & reporting

AI-generated summary

The original poster, n1bff, is experiencing slow Data Explorer queries. They report a query execution time of 7.9 ms in the admin backend, but a much longer time (150 ms) when accessing the query result via the browser, and an even longer time (around 200 ms) when using curl.

Falco suggests that the slow performance is due to the large amount of data being transferred and de/serialized. They propose solutions such as lowering the row counts per page, building a custom plugin with caching and optimized data format, or querying the underlying database directly.

n1bff notes that even with a single row result, the query takes significantly longer to retrieve via the API. They would like to avoid building a custom plugin or querying the database directly, as they were drawn to Data Explorer for its ease of use and well-working API.

sam mentions that “magical lookups” for certain column names might be causing the issue, but n1bff notes that their query only uses these lookups for JOINs, not in the result set.

sam asks if the issue could be network-related, and n1bff reports that the server time for the request is around 10 times the query execution time, suggesting that the issue may indeed be related to network latency.

1

I created some Data Explorer queries.

If I execute them in the admin backend, the GUI shows me a very quick execution time for the DB query itself, e.g. “Query completed in 7.9 ms.”

The network log of my browser shows that e.g. https://example.com/admin/plugins/explorer/queries/1/run was accessed and that it took a 1-2 magnitudes longer to get the result, e.g. 150ms.

When I curl such a query it takes even longer, about twice the time compared to running the query in the admin panel.

How can I have Data Explorer queries that I can retrieve fast without that much overhead?

2

From the information you provided, I’d say that those are simple queries (fast to execute in the RDBMS), that return many rows (slow to de/serialize in the backend and transfer).

If the current performance is blocking your use case, there are many different solutions you can explore:

  • Lowering row counts per page, so each page is faster.

  • Building a plugin that exposes the same information in a new API endpoint and is smarter about caching and data format.

  • Querying the underlying database directly.

3

We have magical lookups for certain column names (user_id, post_id, etc) alias them to something else like post_id1 etc..

4

Thanks guys!

It is the same if I have just one single row as result of a query. E.g. a query that returns a single integer, ran in the Data Explorer panel in the admin backend it takes 9 ms, ran from the outside against the Data Explorer API via curl it takes 200 ms.

I would very much like to avoid that, I use Data Explorer because it was suggested on these forums as a nice way to get information from the DB and a well working API for that.

My query uses them for JOINs but not in the result set. The SELECT only has one column and that is a newly calculated one with a arbitrary name.

5

What does NGINX say the server time is for the request, could this be a network issue?

6

Assuming that those are the floating point numbers 3 places right of the HTTP status code (e.g. ... 200 642 "-" **0.107 0.108** "system" ..., highlighted with stars by me) they are about 10 times the duration of the query time reported in the backend and pretty much exactly half the time it takes to curl from my client.