Libreoffice base slow search with small database and fast machine

English
, , , , ,
#1

I am trying to do a search in a libreoffice database from a form using the search facility (magnifying glass icon)
Searching all fields takes around 7 seconds which is a very long time considering the size of the file and the speed of the machine.

I have already tried upping the memory settings to use for libre office to 1024MB and Memory per object to 60MB but no change.

I have tried editing the database to have small field sizes but it makes no difference to file size or search speed. I have done saves and reboots between tests.

Java Runtime Environment for java-1.8.0.112-sun

Libre Office version 5.2.2.2
Build ID: 8f96e87c890bf8fa77463cd4b640a2312823f3ad
CPU Threads: 8; OS Version: Linux 4.8; UI Render: default;
Locale: en-GB (en_GB.UTF-8); Calc: group

Test file size 270kb 18 columns by 2113 records.

PCLinuxOS x64 KDE fully updated (works very fast)

Intel Core i7-4770K (8 cores) @ 3.50GHz, 256GB Evo SSD (system), 2TB HDD (data), 32GB RAM, GTX780

Any help much appreciated.

Base: Adding multiple pull down record selectors to a table editor form
#2

Pls, can you attach a sample file for test?, remember to delete any private/personal information in it.

#3

Pls, can you attach a sample file for test?, remember to delete any private/personal information on it.

#4

Hi, thanks for your reply. The database I have is a customer database so sadly I can’t make it public.

#5

This same database is also running slow on a Windows 10 machine (hardware about 1/2 the speed of mine) and searches take twice as long. I have searched the internet but cannot find an “Example” database of a resonable size (a few thousand records) that I could use to test.

#6

Just ran some test on my system - Xubuntu 16.04; AMD 4 core 3.2; 4 gb memory & mediocre HD. Using LO v5.2.0.4 and a table with 1100 records with 9 fields (largest field 150 characters). Used the search as you mentioned.

First search found item in record #867 in less than a second. Used fictitious search to go thru all records & fields - less than 2 seconds. Turned on Use OpenGL for all rendering (on restart) under menu Tools->Options_>LibreOffice->View. Ran same tests and results were six to seven times slower (one test taking over 10 seconds).

Check if this setting is on and if so try again with it off.

Edit:

Original test was run using MySQL DB. Have now run the same tests with the same table using both HSQL embedded and split DB’s. The results were always the same. The search was much slower with OpenGL turned on.

The same search in MySQL Workbench produced almost instantaneous results.

Edit 11/07/2016:

For anyone interested, I found an 8000+ record table. Too large to upload even the specific sample. Get it from TDF here. At bottom of base handbook, Zip file of databases used in Base Handbook 5.0. Unzipped it is Example_Search_and_Filter.odb file. Has variety of search methods including “manifying glass” method in searchtable form.

#7

That’s weird. OpenGL has nothing to with database search, yet it makes it many times slower.

#8

Can you attach a file to test ? I have a fairly powerful computer for running OpenGL so I’m interested in seeing how it changes in my case.

#9

No. The only files of size I cannot share.

#10

Hi ratslinger, thanks for your reply. Opengl was off, I tried turning it on but it made no difference at all to the times.

#11

I think Magnifying glass icon is inherently a very poor way to search a database. Extremely poor really. Many good fast text editors might do a better job.

Huge databases, a million times larger than yours, can be searched very quickly when appropriately indexed and then searched using those indexes. Yes it might take some time to index the data in the first place. But the searching is done with b-trees or the like, not brute force text searches.

While LibreOffice is pretty good on the tools to hold and manage the data, what I think it sorely lacks are GUI tools to search the data, at least I have gone all the way through this excellent set of almost 100 Base tutorials and haven’t found what I’m looking for so far.

But that being said, the first principle of: starting with good indexes and using them is still where we must start.

At a minimum: You should be able to index your fields appropriately, and then use SQL to search. You might need to use SQL (not the GUI part of LibreOffice) to tell MySQL what to index and how. Later you can figure out how to do the same thing in GUI land with BASIC as I am still learning here myself. …But first make sure that you can search at speed using simple SQL (or hand edited GUI queries). In other words, it’s not LibreOffice that is slow, it’s the way you’re asking LibreOffice to do things that makes it slow.

Hope this helps a little. My moto is break the problem in half.

#12

Indexes aren’t the only thing. The Database Server’s settings matter even more so, like MySQL. I moved to a smaller computer and used smaller settings than previously, and the performance is alot slower since I can’t give much RAM to it.

#13

Yes, databases do require RAM. (I remember running DBase on a 64k machine and how it had to hit the drive so hard.) But this guy is on a machine with lots as far as I can tell.

#14

Indeed, 32GB is pretty nice. Give the DbS 12 of that assuming you’re using a single db engine for everyone, not counting possible tables defaulting to inferior engine to make sure it -will- boot, and it should be fast if the rest of the settings are good too.

#15

Hi, many thanks to EasyTrieve and everyone else for their replies and comments. In the end we have gone for just using the spreadsheet for doing this. Searches are instantaneous, not so nice to work on individual records with but with around just 2000 entries and 1 label print out a year and the odd update it is just not worth the effort and sadly I have been reading about lots of crashes with the Database part of. OO. Cheers everyone.

#16

I have reported a similar problem (LibreOffice base is slow in sql statements). The problem appear with the Kubuntu 16.04 → 16.10 update. That update also change LibreOffice form 5.0.6 to 5.2. The problem seems to affect SQL operation only. GUI, Java and Basic seems not affected. The same odb file was (and is yet) very fast on Libre 5.0 and horribly slow on 5.2.

I have try libre 5.2 with Java 1.7 (the one on Libre 5.0) without signifiant success. Java don’t seem implied.

The problem exist also on Ubuntu 16.10 with Unity interface.

#17

Oops… Kubuntu 16.04 was using Libre Office 5.1.4.2 and not 5.0.6 (Kubuntu 15.10). But fact is that Libre 5.1 and 5.0 was running base sql fast. The problem appear with 5.2.2 and 5.2.3 (I don’t know for previous 5.2)

#18

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.