Apache OpenOffice (AOO) Bugzilla – Issue 121595
scalc hangs when using lots of file links and all files exist
Last modified: 2017-05-20 09:57:34 UTC
I have a 5 sheet spreadsheet, about 280 rows in 3 of the sheets, columns a to cb wide, 630Kb if I look at the disk storage required. One sheet has 15*3*280 links to other spreadsheets that have about 100 lines each in them with about 16 columns of data in each of the linked sheet. The links only reference 3 rows in each of the 280 linked files. When I try to refresh all these links scalc hangs at its memory usage gets around 1000k. Clearly the amount of linked data is only 280*15 cells * 3 cells. About 13k cells time how every many bytes to read and store this data Clearly seems like it should be less than what it takes. Again thats 280 link file names. If I look at the memory usage if I load the spreadsheet and DONT up the links the size is 146,000k (soffice.bin) Then I answer the update all links and the size grows to over 1000,000k and then recovers back down to 193,000k This take about 5 min. Then it seems to hang. Have to ues task manager and kill it. I can select a cell but can not type into it. I can select small subsets of links to update cells and then save but it would be nice to be able to update all since I dont always know which linked files have changes. Any advice would be helpful. I am at 3.4.0 and should update. All the Links are of the form: =OFFSET('file:///C:/Users/Dell Inspiron 580/Videos/AndrewBrown/AB50R.xlsx'#$Sheet1.$C$1;AA2;0)
Tried cutting number of links by 50% (deleted rows) and also went to 3.4.1. Size of memory usage when process all the links (140 rows) soffice.bin ->500,000k. seems to work fine. 1 min to update links. Exit and office bin only drops to 438,000k So I kill soffice.bin and retry with 280 row version. Runs for 5min, mem usages gets up to 1,100,000k and then slowly decreases to 200,000 and then get a Microsoft Visual C++ runtime library error window appears and says to terminate has been fielded and hangs. Had to kill process. This is a non confidential set of data (link files are 8Mbytes total) but must be installed in a specific path for the links. c:\Users\Dell Inspiron 580\Videos So the only solution (workaround) is two split this into 2 files resulting in 50% of the links in each. Bummer.
Tried deleting the 1st 140 rows of links. Remember deleting the last 140 rows worked. (binary search for a bad link with rows 140 to 210). This time memory usage grows to 1,340,000 and hangs. So something going on with the last 140 rows. I do Recover and delete contents of 70 more rows leaving 70 out of 280. (210 to 280) This works fine. So try leaving rows 140 to 210. Works fine. So its not a bad link but the number of links or some combination since deleting the last 140 rows works but the deleting the 1st 140 did not. ???
I noticed in the spreadsheet (only row 70 to 149) it loaded but there were 3 bad rows and even a message at the bottom saying invalid ref or link. So I did find 3 links that did not have an existing files. I created the 3 files and the bad rows have now have good reference values. BUT loading 280 rows still fails. Reminder: loading with the 1 to 140 rows of links or with rows from 140 to 280 both work and both show reference numbers in all cells.
Created attachment 80116 [details] file that contains sheet PHraw with 280 rows of many links. hangs This file is the ods file that is failing (hangs) to run it requires the linked files to be in folders in a specific path. The sheet with the links is PHraw. columns V to CB C:\Users\Dell Inspiron 580\Videos Will attach zip file for these files. 8M
Created attachment 80117 [details] zip file of some of the spreadsheets that will be linked These files must go in the path C:\Users\Dell Inspiron 580\Videos\ The 8M limit for attachments means I will provide zips at the level below videos
Created attachment 80118 [details] more link files C:\Users\Dell Inspiron 580\Videos
Created attachment 80119 [details] more link files
Created attachment 80120 [details] more link files
Created attachment 80121 [details] ej more linked
Created attachment 80122 [details] tv more linked
Created attachment 80123 [details] tk more linked
Created attachment 80124 [details] sc more linked
Created attachment 80125 [details] rr more linked
Created attachment 80126 [details] rk more linked
Created attachment 80127 [details] mm more linked
Created attachment 80128 [details] jm more linked
Created attachment 80129 [details] ec last of more linked
Created attachment 80130 [details] delete half of the rows. 1 to 140 ok
Created attachment 80131 [details] delete half of the rows 140 to 280. ok
(In reply to comment #0) > I have a 5 sheet spreadsheet, about 280 rows in 3 of the sheets, columns a > to cb wide, 630Kb if I look at the disk storage required. > One sheet has 15*3*280 links to other spreadsheets that have about 100 lines > each > in them with about 16 columns of data in each of the linked sheet. > The links only reference 3 rows in each of the 280 linked files. > When I try to refresh all these links scalc hangs at its memory usage > gets around 1000k. Clearly the amount of linked data is only 280*15 cells * > 3 cells. About 13k cells time how every many bytes to read and store this > data > Clearly seems like it should be less than what it takes. > Again thats 280 link file names. > If I look at the memory usage if I load the spreadsheet and DONT up the links > the size is 146,000k (soffice.bin) Then I answer the update all links > and the size grows to over 1000,000k and then recovers back down to 193,000k > This take about 5 min. Then it seems to hang. Have to ues task manager and > kill it. I can select a cell but can not type into it. I can select small > subsets of links to update cells and then save but it would be nice to be > able to update all since I dont always know which linked files have changes. > Any advice would be helpful. I am at 3.4.0 and should update. > > All the Links are of the form: > =OFFSET('file:///C:/Users/Dell Inspiron > 580/Videos/AndrewBrown/AB50R.xlsx'#$Sheet1.$C$1;AA2;0) Jan 10 2013: I just tried loading this spreadsheet with xlx. Version from 2002. No problem. It did stall on several files with the same name but in different paths. MissingVideo.xlsx. But was able to finish without a crash after I skipped the file names that were the same.
Created attachment 80138 [details] file that contains sheet PHraw with 280 rows of many links. hangs
Created attachment 80139 [details] zip file of 30% linked spreadsheets
Created attachment 80140 [details] zip file 2 of 30% linked spreadsheets
Created attachment 80141 [details] zip file 3 of 30% linked spreadsheets
(In reply to comment #0) UPDATE Jan 21 2013: I copied the data to a 2nd computer, windows 7, that has 2x the memory on a suggestion from a person on the scalc forum. 3.3 failed (crashed) and asked for a error report and I submitted it. The I updated that computer to 3.4.1 loops. Its been running over 1 hr. soffice.bin is holding at 1.5G, total memory used via task manager is 3G. After 2hr in a loop, these number dropped by 15%.
(In reply to comment #25) > (In reply to comment #0) > UPDATE Jan 21 2013: I copied the data to a 2nd computer, windows 7, that > has 2x the memory on a suggestion from a person on the scalc forum. 3.3 > failed (crashed) and asked for a error report and I submitted it. The I > updated that computer to 3.4.1 loops. Its been running over 1 hr. > soffice.bin is holding at 1.5G, total memory used via task manager is 3G. > After 2hr in a loop, these number dropped by 15%. Still no solution but have another observation Feb 16. I added 280 more file links. The update of 50% at a time fails now. Even only do 30% at a time fails now and then. (intermittent) My process is to select 30% of the links for update, wait 5 min, save and exit. Then re open the saved file, update the next 30% of the links, save and exit. The last time it fails was on the last 30%, the links updated but when I saved it hung. I killed the process, recovery showed a version that was only 60% linked so before I did the last 30% I use a program called SpeedUpMyPC and did a free deep memory. See attached image (deep ram recovery) of how much memory it released. From 70% used (after killed scalc and open office bin) down to 30% after SpeedUpMyPC finished. After this clean up I did the last 30% updated, and I was able to save.... Clearly you guys have a bug here.
Created attachment 80291 [details] memory free to allow scalc to finish links
Created attachment 80295 [details] task manager and runtime error window at time of fail.
Can not reproduce. AOO410m1(Build:9750) - Rev. 1552994 Rev.1552994 Win 7