Libreoffice base is slow when editing big SQL queries

[SylvainDubois]

Hello,

When trying to edit very big queries (by right clicking on the query then “edit in sql view”), the editor takes a lot of times (more than 5minutes for my query). Editing the SQL is also slow.

The SQL view also does not keep line breaks and formatting I do in my queries, so it’s very difficult to read.

I would prefer a simple text editor where I can turn off syntax highlighting than waiting a huge amount of times every time I edit a SQL. It’s barely usable currently.

I have experienced this problem on Linux ver 5.1.6.2 and also on windows (don’t remember but a similar version).

Can you help me?

Best regards,
Sylvain

[Ratslinger]

Hello,

First, you are using a older version of LO. This appears to be a distro version. If on Ubuntu based distro, consider upgrading to a PPA version. See → LO PPA Fresh.

Next you should check for any outstanding bugs → Bugzilla. Haven’t checked for this but I seem to recall at least something on formatting. If none found on your subject, file one & post reference here for others to see.

As for your question, my experience shows size is not necessarily the reason for slow opening. It is also the content of the SQL. I have queries with many WHERE conditions which take 5 times longer to display than a query without these conditions but is 10 times longer in code.

A couple of things you can try. The SQL will display faster and retain line formatting if you turn on Run SQL command directly (toolbar icon). Understand this will dis-allow the use of parameters in your SQL.

Also, if you are not using an embedded database, you can consider using an external SQL editor. Personal preference is SQL Workbench/J. With that you can connect (JDBC) to your DB and test/format your SQL. When satisfied, you can copy & paste from there into Base. I especially like this product since it includes a data pumper which allows copying data from one DB to another.

[SylvainDubois]

Hello,

Thank you very much for your useful answer! I will try installing a new version with PPA. I also already use other tools to manage my database, but it was convenient to use Base for my normal users who just want to use query results.