Created attachment 192756 [details] Bug report (example) I use Calc to retrieve data from my ERP. I have connected via ODBC in Base, then dragged the table to Calc. Whenever I want to refresh the data in Calc, the data gets imported normally, but just after, if I click any cell there's a spinning wheel for 15-30sec, and when I try to save the data, the program spins forever, like 5-10 minutes before I give up. This happens EVERYTIME. I'm on Mac mainly, but the same problem happens also on Linux and Windows. My spreadsheet has mostly two tables which are 5000-10000 rows, and five queries in total.
Can't reproduce in: Version: 24.2.1.2 (X86_64) / LibreOffice Community Build ID: db4def46b0453cc22e2d0305797cf981b68ef5ac CPU threads: 8; OS: Linux 6.5; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: CL threaded Any chance you could provide more precise steps with a sample database and sample ODS?
Hi, I'm still giving a try to Libreoffice, and I realize the slow saving may have nothing to do with the database. I am working on the same file without importing from the DB, and even a very little change (few dozen cells) takes like one minute. I have deleted a sheet, trying to make my document lighter, and now the program has eaten all the memory from the computer and also the RAM. Since I only update the DB from time to time, there are hundreds or thousands of lines added each time, I think it's enough to KO the program and my computer. I don't know if it counts as a bug, but it makes it just impossible to save the file. It's still spinning now.
Created attachment 193104 [details] CPU overload
Created attachment 193105 [details] Memory overload
Sorry my comment wasn't clear, it's saving that takes a long time, working on the file is actually acceptable by LO standards: Hi, I'm still giving a try to Libreoffice, and I realize the slow saving may have nothing to do with the database. I am working on the same file without importing from the DB, and even a very little change (few dozen cells) takes like one minute to save. I have deleted a sheet, trying to make my document lighter, and now the program has eaten all the memory from the computer and also the RAM, and LO is stuck in the process of saving. Since I only update the DB from time to time, there are hundreds or thousands of lines added each time, I think it's enough to KO the program and my computer when I try to save. I don't know if it counts as a bug, but it makes it just impossible to save the file. It's still spinning now. Is there a way to narrow down to what can be stuck during the saving process?
Hi, I'm still working on the same file, actually if you let the computer for about half and hour for 10000 rows, or 15 minutes for 5000 rows, it will really save the file... If you can tell me how I can get a log of the process I can share it, there must be a bottleneck somewhere
Hi, I've been working on my file again and I found the problem. I had imported the data from Excel, and as the way to define range in libreoffice differs from Excel, I had a lot of formulas with SUMPRODUCT on whole ranges like B:B because otherwise my calculations would bring a #REF error when refreshing the SQL. It's only today that I found out I can make named ranges for individual columns, and it doesn't mess up calculations when I import the data from my database. Once I replaced the formulas to named ranges of columns instead of full column, everything went back to an acceptable speed, including saving and calculations. Sorry for being a beginner and not knowing well enough how things work on Libreoffice or not finding out quick enough. It is quite a learning curve from Excel, but worth it :)
Thanks for reporting back, and glad you found a solution! If the slowness resulted from a realistic use-case (i.e. moving a project from MS Office to LibreOffice), it might still be worth it reporting the issue with minimal sample files that demonstrate the problem, in a new report, if you are so inclined.
Thanks for the advice. I use a lot of SUMPRODUCT functions because they are faster than VLOOKUP, and paired with the reference to tables in Excel it's a convenient and clear way to work on sheets. In LibreOffice I don't think you can refer to tables the same way, when I was doing it in the beginning, my tables kept being erased, that's why I moved to whole columns references and it started to be so slow. I get your idea, I'll try to reproduce when I have time and file a bug or feature suggestion. Many thanks for having following up, now that it works the sync with LO is faster than with Excel :D