Freezes on large databases (database with ~30k tables)

[fatherofinvention]

I was very excited to try Ultorg to help me make since of a large Microsoft SQL Server database (30k tables, 10k views) but it choked on the data. I am able to open the same DB using TablePlus and DBeaver pretty quickly, so I'm not sure what the bottleneck is. I am using a M1 MacBook Pro with 32GB RAM. Happy to provide logs or do tests.

[eirikbakke]

Greetings! Some questions:

1. At what step in the process is Ultorg freezing?
2. Am I understanding correctly that this database has 30,000 tables, rather than e.g. 30,000 rows in each table?
3. Are you able to see the list of tables?
4. Are the tables organized into different schemas (shown as subfolders under the connection icon), or all in a single schema/database?
5. Are there foreign key relationships between the tables?

[fatherofinvention]

Hey Eirik!

1. It freezes when I expand the dbo schema

2. Yes, that's right, this is a huge database for Dynamics AX.
3. Partially, you can see the screenshot above, but I can't scroll the list because presumably Ultorg is overwhelmed by the number of tables it is trying to load into the view.
4. There are multiple schemas in the DB, and I can browse the others because they have fewer tables, but when I expand dbo it locks up.

5. Honestly I'm not sure, possible yes. but this is a new DB for me so I was hoping to use Ultorg to get a handle on understanding it.

[eirikbakke]

Thanks for the screenshots! If you open up one of the other smaller schemas, are you able to double-click some of the tables there and see data without freezing?

And another question: While Ultorg is freezing, do you see high CPU consumption from the app in the MacOS Activity Monitor?

[eirikbakke]

I managed to reproduce this now, testing on a SQLite database with 30000 tables. Some operations in the "Folders" pane are sluggish, though the tables all do seem to appear (in the super-long scrollable list), and I am able to open a few tables.

Doing a bit of profiling, I found one over-eager sanity check which was bogging down the user interface when there's a large number of tables involved. I fixed that problem, which improved things considerably. I'll let you know when a new Ultorg version is available with this fix.

Eventually it would be good to make the UI work better with huge lists of tables like this. One planned feature is to automatically organize tables by auto-detected prefixes, e.g. "Accountant", "Acocostcenter" etc. in the schema in the screenshot. That way you don't actually have to scroll through a full list of 30,000 tables.

[fatherofinvention]

Hey Eirik, that’s exciting! Thanks for taking the time to explain, I look forward to trying out the version with the fix when it becomes available. Ultorg is such an impressive project, keep up the excellent work.

…

On Tue, Oct 3, 2023, at 4:02 PM, Eirik Bakke wrote: I managed to reproduce this now, testing on a SQLite database with 30000 tables. Some operations in the "Folders" pane are sluggish, though the tables all do seem to appear (in the super-long scrollable list), and I am able to open a few tables. Doing a bit of profiling, I found one over-eager sanity check which was bogging down the user interface when there's a large number of tables involved. I fixed that problem, which improved things considerably. I'll let you know when a new Ultorg version is available with this fix. Eventually it would be good to make the UI work better with huge lists of tables like this. One planned feature is to automatically organize tables by auto-detected prefixes, e.g. "Accountant", "Acocostcenter" etc. in the schema in the screenshot. That way you don't actually have to scroll through a full list of 30,000 tables. — Reply to this email directly, view it on GitHub <#60 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AF6T3WGDRGHHW5EEU4BJCVLX5R4PPAVCNFSM6AAAAAA5ADTVCOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONBVG4ZDCMBZGU>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[eirikbakke]

Hi again! There's a new Ultorg version available now, which should hopefully make Ultorg usable on the 30,000-table database. Certain operations are still a bit sluggish, but at least the app does not freeze up completely.

You can find the latest Ultorg version here, after first creating an account: https://www.ultorg.com/download/
(Sorry it took a bit of time to get you this version; the version change coincided with the transition of Ultorg from private beta to public release.)

At some point in the future, I'm planning to streamline the relevant code a bit more, as a cleanup is needed in preparation for other features. At that point the remaining sources of lag should go away for the case of databases with an extremely large number of tables.

[fatherofinvention]

Thank you @eirikbakke ! I’ll download the latest release and try it against this DB again soon.