Question: Slow loading of Explorer pagination

[nickchomey]

Describe the bug

When I bulk insert many records via an sdk, and then try to access the table in Surrealist, I can see in the surrealdb server logs that it is just rapidly sending requests, seemingly to fetch each record one by one. See the attached screen recording where I added 34k and 67k records into two tables (which really isn't very much). It takes a very long time to load those tables. I've done it on other data that has millions of rows and it takes like 10 minutes.

Code_JbdI6c0V22.mp4

(note, ignore that it shows how many records and pages there are right away for the 1st table - I think that info was cached in surrealist already. You can see how it normally behaves when I switch to the 2nd table)

Steps to reproduce

1. Bulk add a lot of records
2. Try to load the table in surrealist

Expected behaviour

I expect it to be fast, or at least tolerable.

Surrealist Environment

Version: 3.0.8
Flags: feature_flags: false, query_view: true, explorer_view: true, graphql_view: true, designer_view: true, auth_view: true, functions_view: true, models_view: true, apidocs_view: true, cloud_view: true, themes: true, newsfeed: true, database_version_check: true, highlight_tool: false, legacy_serve: false, cloud_endpoints: production, cloud_access: false, cloud_killswitch: true, changelog: auto

Contact Details

No response

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct

[macjuul]

Hey! This might be a slightly misguided conclusion since I don't believe the log messages you refer to are directly tied to individual query requests. When navigating to a table in the Surrealist Explorer, only two queries are executed in parallel.

The first query will fetch the records for the current page

SELECT * FROM [table name] LIMIT [page max] START [page start];

The second, and likely slowest query, will count the total amount of records within a table in order to know how many pages you can retrieve

SELECT count() AS count FROM [table name];

The first query will likely finish first, while the second query needs a longer time to complete, which is what the loading indicators in the UI represent. I believe this second query is also triggering these log messages.

For context, which version of SurrealDB are you connecting to?

[nickchomey]

Ok, my guess could very well have been wrong! But it was the only thing that I could think of to explain why it takes so long to count and populate the data. Why would a simple count query take dozens of seconds for 30-60000 rows (and MUCH longer for millions)? Moreover, it should probably not lock up the UI.

I have the latest version of surrealdb and surrealist (and it has been happening since the very first v2/v3 releases, I just haven't reported it til now. It might have happened before that, but that's when I started working with surrealdb).

[macjuul]

Interesting, that's indeed unexpected. I have some follow up questions

• In which way does it block the UI? In the video it appears the loading spinners are animating and moving. Are you able to open the records visible on the first page?
• Does the database have a schema applied?
• Does this also happen in a database without a schema?
• When executing the count() query I provided earlier, is it equally as slow when executed manually in the Query view?

[nickchomey]

Sorry, I wasn't clear enough. I'm on my phone right now, but I'm pretty sure that you can navigate the ui, but can't perform other operations while it is locked up with the spinner. I'll test again later to confirm.

No schema, no indexes.

I'll try the count query via the query view later. Though I did do a count query on a large table yesterday and it wasn't too slow (though, oddly, it was slower than simply returning fields for each result - I'll provide details on that later too)

[tobiemh]

Hi @nickchomey there are a few things going on here:

• In Surrealist the statements are not currently run in any worker thread, so a long-running query can make the ui freeze briefly. This is something we would like to improve, but generally isn't causing the issue you are seeing here.
• The issue is that the count function is being checked that it can be run (depending on permissions), and every invocation of count() (which happens on every record) is being logged to the console. trace logging is only designed for development, or for tracking down bugs, by logging as much as possible to the console. As a result, you should never expect high-performance when running a server with trace logging, and even debug level logging can be problematic if performance is what you are after. On a side note, when running with heavy logging levels inside a container, the container can keep all of the logs, resulting in an increase of memory over time, which doesn't necessarily have anything to do with SurrealDB, but rather with the logging output, and the container service.

Could you check what the performance is like when running with info or debug logging?

[nickchomey]

Trace was the issue! You know your product well.

It doesn't happen with any other log level (other than, obviously, full).

I dont recall why I turned on trace, but it might just be because it is in most of the commands in the docs. eg https://surrealdb.com/docs/surrealdb/installation/running/memory

I'll use info going forward unless I need more details for an issue

[tobiemh]

Thanks @nickchomey. We'll update the documentation so that this isn't as confusing. Thanks for your suggestion 🚀 🎉 !