@ronny :
In your blog post, you mention that you only tested 3.6.x, 4.0.x and apparently 4.1. Those versions are very old, in fact obsolete with regard to currently supported production versions of LibreOffice that are available on the download page.

It might well be that the problems with primary key retrieval you mention have been resolved in these later versions.

Additionally, if I have understood correctly, all of your tests were carried out by dragging the query resultset into a Calc sheet - can you confirm this ?

I have a remote non-SSH connection to a local LAN mysql instance (not MariaDB) to which I connect from a Mac, and although I don't have as many records, I don't see a noticeable performance hit when scrolling through a resultset of all records of a table containg 75 columns by 4400 tuples. Note that each tuple is identified by an AUTO_INCREMENT NOT NULL PRIMARY KEY definition, guaranteeing uniqueness. I also use the native mysql connector.

I will set this to NEEDINFO to await your tests with the latest production releases of LO. Please set back to UNCONFIRMED afterwards.

Adding Lionel, Julien to CC

I suppose one must always ask the question how much extra latency gets added through the use of a SSH tunnel to connect to the mysql instance. How do you set this up ? - port forwarding ? or direct SSH connection ?

(In reply to Alex Thurgood from comment #1)
> @ronny :
> In your blog post, you mention that you only tested 3.6.x, 4.0.x and
> apparently 4.1. Those versions are very old, in fact obsolete with regard to
> currently supported production versions of LibreOffice that are available on
> the download page.
> 
> It might well be that the problems with primary key retrieval you mention
> have been resolved in these later versions.
> 
> Additionally, if I have understood correctly, all of your tests were carried
> out by dragging the query resultset into a Calc sheet - can you confirm this
> ?
> 
> I have a remote non-SSH connection to a local LAN mysql instance (not
> MariaDB) to which I connect from a Mac, and although I don't have as many
> records, I don't see a noticeable performance hit when scrolling through a
> resultset of all records of a table containg 75 columns by 4400 tuples. Note
> that each tuple is identified by an AUTO_INCREMENT NOT NULL PRIMARY KEY
> definition, guaranteeing uniqueness. I also use the native mysql connector.
> 
> I will set this to NEEDINFO to await your tests with the latest production
> releases of LO. Please set back to UNCONFIRMED afterwards.
> 
> Adding Lionel, Julien to CC

Thx for reply! The blog post was not published by me, just found it on the web. As filled in the bug form, I tested on 4.4.2.2 (latest stable).

I tested the issue in data grid controls in form and just by looking tables in the table view, i. e., simple select queries (I’m using German localization, hopefully the terms a right).

I have created a simple table (MySQL 5.5):

CREATE TABLE `artists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;


As you can see, I created a AUTO_INCREMENT NOT NULL PRIMARY KEY. The funny thing is, changing `id` to UNIQUE only does not raise the issue. Btw. I filled the table with 200 rows only.

Do you think it could be helpful to test LO Base on a Linux on a virtual box?

Best regards, Ronny

(In reply to Alex Thurgood from comment #2)
> I suppose one must always ask the question how much extra latency gets added
> through the use of a SSH tunnel to connect to the mysql instance. How do you
> set this up ? - port forwarding ? or direct SSH connection ?

I did 2 tests: over SSH tunnel and direct (non SSL) connection. There is no noticeable difference. Moreover I tested with MySQL Workbench and Access 2013 over SSH tunnel – both work very fast. Tested with different connectors (see first comment).

The server has a 1 Gbit/s bandwidth. I (the client) have 50 Mbit/s VDSL bandwidth. Ping is < 35 ms. I have checked the system resource no noticeable high workload on CPU, RAM, SSD and ethernet on both server and client.

The MySQL log shows many selects while scrolling the data grid table:

588 Execute	SELECT * FROM "prosodia"."titles" WHERE "prosodia"."titles"."id" = 76
588 Execute	SELECT * FROM "prosodia"."titles" WHERE "prosodia"."titles"."id" = 77
588 Execute	SELECT * FROM "prosodia"."titles" WHERE "prosodia"."titles"."id" = 78
…


If I leave the primary key `id`, I get one select in the log only:

150422 22:37:44	  588 Prepare	SELECT "name" FROM "prosodia"."titles" AS "titles" WHERE ( 0 = 1 )
		  588 Close stmt	
		  588 Prepare	SELECT "name" FROM "prosodia"."titles" AS "titles" WHERE ( 0 = 1 )
		  588 Close stmt	
		  588 Prepare	SELECT "name" FROM "prosodia"."titles" AS "titles"
		  588 Execute	SELECT "name" FROM "prosodia"."titles" AS "titles"

(scrolling the data grid up and down like crazy; works fast)

Have tested the following:
LO 4.4.3.1 directly from LO, not OpenSUSE, OpenSUSE 13.2 64bit rpm Linux, MariaDB, direct connector from http://extensions.libreoffice.org/extension-center/mysql-native-connector,
InnoDB-table with primary-key, auto increment, 10 columns, 11125 rows.

Opened the table in table-view. Could scroll to the end immediately by the button "Last". Could also scroll by the scrollbar without any problem.
Then created a form with a tablecontrol. Same behavior here. No problem to set the cursor to last row immediately.

So I couldn't reproduce any performance-problem here.

So we now have tests on Mac OSX (mine) and Suse 13.2 64bit that don't show any noticeable slowdown with the respective mysql connector and autoincrement not null primary keys. My Mac tests were carried out on MyISAM tables, and Robert's Suse Linux tests against InnoDB tables.

Perhaps this is a windows only problem ?

I just tried this with a tunnelled, port forwarded connection to my remote mysql instance on the local lan using  :

ssh -fNg -L 3310:127.0.0.1:3306 user@myhost

and then set up a new ODB file that connects to 127.0.0.1 port 3310

Tested on LO4422

Navigating through a test table with the definition you gave and populating it with a random string for 255 tuples didn't show any slowdown when navigating with the cursor or the mouse.

Thanks for your tests. Have you looked the queries shown in mysql.log? (config: general_log_file = /var/log/mysql/mysql.log; general_log = 1). Did you get many entries like 588 Execute SELECT * FROM "prosodia"."titles" WHERE "prosodia"."titles"."id" = 1 if you scroll the tables? LO seems to query each primary key (in my case id) separately.

Can LO log the queries that it sends to MySQL?

(In reply to ronny from comment #8)
> Thanks for your tests. Have you looked the queries shown in mysql.log?
> (config: general_log_file = /var/log/mysql/mysql.log; general_log = 1). Did
> you get many entries like 588 Execute SELECT * FROM "prosodia"."titles"
> WHERE "prosodia"."titles"."id" = 1 if you scroll the tables? LO seems to
> query each primary key (in my case id) separately.
> 
> Can LO log the queries that it sends to MySQL?

I don't find a log-file at this place for the query-logs. So I started phpMyAdmin.
Switching from row 1 to row 11.125 gets 117 selects. Seems the cache is filled with about 100 rows for each select.
Then I tried to move by the mouse. There I got 4180 selects and moved only from row 1 to 3664 ... The traffic is very high this time. Seems I could not reproduce the performance-bug, because I'm the only user of the database. Don't know why there are 4180 selects for only 3664 rows.

(In reply to robert from comment #9)
> I don't find a log-file at this place for the query-logs. So I started
> phpMyAdmin.
> Switching from row 1 to row 11.125 gets 117 selects. Seems the cache is
> filled with about 100 rows for each select.
> Then I tried to move by the mouse. There I got 4180 selects and moved only
> from row 1 to 3664 ... The traffic is very high this time. Seems I could not
> reproduce the performance-bug, because I'm the only user of the database.
> Don't know why there are 4180 selects for only 3664 rows.

Normally, general_log_file = /var/log/mysql/mysql.log; general_log = 1 is not enabled. You may have to enabled first.

I think your are on the right way: There are just too many single row selects produced by LO …

Have had time to figure out how to start the logging. Simple by typing this SQL-command as admin:
SET GLOBAL general_log = 'ON';
Notice: All what I have tested I tested with MariaDB and no other client connecting to MariaDB.

I notice
1) for every row a new query is started like this one:
SELECT * FROM "libretest"."Test" WHERE "libretest"."Test"."ID" = 11099
2) setting the table to last row only the last shown rows where queried
3) scrolling up from the last row it shows queries for the upper rows, but I notice also queries for the last rows (as noticed in (2)) every time I stop scrolling up. Don't know why the last rows are queried again and again.
Looking at the table scrolling will hang a little bit with (3).

I could send a query from Base through Tools → SQL - it is only one query for all data I'm looking for:
SELECT * FROM "libretest"."Test" WHERE "libretest"."Test"."ID" < 100
shows all data in Tools - SQL and doesn't need more queries than only one.

Don't know why the GUI is designed this way. If there are many queries from other clients it must be a performance-problem for MySQL.
I will set this bug to new. Changed also the system to all, because I have tested with OpenSUSE. LO-Version here is 4.3.7.2

Bug is still the same with LO 7.3.0.3

There are many queries if scrolling through a table with primary keys. Every key-value will be logged as a separate query.
If the primary key will be changed to UNIQUE or the whole table should be shown as view there won't be executed many queries while scrolling.

The tested performance with primary key on
http://linux-blog.anracom.com/2013/11/22/mariadb-mysql-libreoffice-4-x-konnektoren-performance-problem-wegen-primary-keys/
will give a difference from 4:40 minutes in LibreOffice with Calc and table in the database browser to 8 seconds in MSExcel.
If there isn't a primary key the Calc will load the tables in database browser as fast as MSExel did. All this is tested 2013, but it shows the problem and the real behavior with many queries for every row hasn't been changed in LO.

One hint:
There will be executed 2 queries every time (cache will need new content?) when scrolling up:
220204 17:34:10	   237 Execute	SELECT * FROM "Db"."Table" WHERE "Db"."Table"."ID" = 496
		   237 Execute	SELECT * FROM "Db"."Table" WHERE "Db"."Table"."ID" = 497
220204 17:34:11	   237 Execute	SELECT * FROM "Db"."Table" WHERE "Db"."Table"."ID" = 541
		   237 Execute	SELECT * FROM "Db"."Table" WHERE "Db"."Table"."ID" = 542

But when scrolling down it looks like this:
237 Execute	SELECT * FROM "Db"."Table" WHERE "Db"."Table"."ID" = 497
237 Execute	SELECT * FROM "Db"."Table" WHERE "Db"."Table"."ID" = 498
237 Execute	SELECT * FROM "Db"."Table" WHERE "Db"."Table"."ID" = 499
237 Execute	SELECT * FROM "Db"."Table" WHERE "Db"."Table"."ID" = 500
237 Execute	SELECT * FROM "Db"."Table" WHERE "Db"."Table"."ID" = 501

every row will be selected …

Dear ronny,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug