Problem description: 

Recently, I am working on a Calc extension which is for dynamically showing streaming financial data. I observed a conflict involving dirty cell, recalculation, and xvolatilresult in Calc. From my view, it will be a potential error or at least affect the performance of Calc if many changes are made with the spreadsheet.

I am not very aware of the mechanism of dirty cell / auto recalculation with XVolatileResult. From my observation, I guess that a XVolatileResult object which is associated with a set of arguments can access to a list of registered cells and update all of them if changes happen. If I change a cell value which is part of the arguments, the formulas in the affected (dirty) cells are recalculated automatically. This schema works well with non-volatile result. However, with XVolatileResult, it raises some issues. Briefly, the affected cells are still kept in the original list, and at the same time register a new list associated with a new XVolatileResult object. Both the original and new XVolatileResult objects update the dirty cells when changes happen. These unnecessary and wrong callbacks definitely lower Calc's performance. With heavy streaming data, this can be a big issue.


Steps to reproduce:
I would like to describe the issues with a XVolatileResult example which is listed in the Libreoffice SDK (sdk->examples->DeveloperGuide->Spreadsheet->ExampleAddIn.java). You can also check out the code from this link: http://c-cpp.r3dcode.com/files/LibreOffice/3/4.5.2/sdk/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.java

Firstly, I added some trivial System.out.println() in this java file.
    public void addResultListener(com.sun.star.sheet.XResultListener aListener)
    {
        aListeners.addElement( aListener );
        System.out.println("Adding a Listener. Total is " + aListeners.size());

        // immediately notify of initial value
        aListener.modified( getResult() );
    }

    public void removeResultListener(com.sun.star.sheet.XResultListener aListener)
    {
        aListeners.removeElement( aListener );
        System.out.println("removing a listener. Total is " + aListeners.size());
    }

    public void incrementValue()
    {
        ++nValue;
        com.sun.star.sheet.ResultEvent aEvent = getResult();
        System.out.println("In incrementValue " + aEvent.Value);

        java.util.Enumeration aEnum = aListeners.elements();
        while (aEnum.hasMoreElements())
            ((com.sun.star.sheet.XResultListener)aEnum.nextElement()).modified(
                aEvent);
        System.out.println("Done incrementValue " + aEvent.Value);
    }

class ExampleAddInThread extends Thread
{
    private java.util.Hashtable aCounters;

    public ExampleAddInThread( java.util.Hashtable aResults )
    {
        aCounters = aResults;
    }

    public void run()
    {
        while ( true )
        {
            try
            {
                sleep(500);
            }
            catch( InterruptedException exception )
            {
            }
           
            System.out.println("will call incrementValue");
            // increment all counters
            java.util.Enumeration aEnum = aCounters.elements();
            while (aEnum.hasMoreElements())
                ((ExampleAddInResult)aEnum.nextElement()).incrementValue();
        }
    }
}

public com.sun.star.sheet.XVolatileResult getCounter(String aName)
        {
            System.out.println("calling getCounter with " + aName);
            if ( aResults == null )
            {
                // create the table of results, and start a thread to increment
                // all counters
                aResults = new java.util.Hashtable();
                ExampleAddInThread aThread = new ExampleAddInThread( aResults );
                aThread.start();
            }

            ExampleAddInResult aResult = (ExampleAddInResult) aResults.get(aName);
            if ( aResult == null )
            {
                aResult = new ExampleAddInResult(aName);
                aResults.put( aName, aResult );
            }
            return aResult;
        }

Secondly, I compiled this extension and installed it with LibreOffice. Then, since I was working in Windows XP, I ran from command line console the command: soffice.exe 2>&1 > log.txt . Afterwards, a LibreOffice window was opened and the standard system out was written in log.txt.

Thirdly, I launched a spreadsheet and did the following cases:
(1) inserted "1" in A1 and "=counter(A1)" in A2. Here counter is an addin function provided by ExampleAddIn.java. The corresponding log is
calling getCounter with 1
Adding a Listener. Total is 1
will call incrementValue
In incrementValue 1
calling getCounter with 1
Done incrementValue 1


Everything is good. To avoid confusion, I deleted all the dynamic count numbers in the log file.

(2) changed "1" to "2" in A1. The corresponding log is
calling getCounter with 2
Adding a Listener. Total is 1
will call incrementValue
In incrementValue 2
calling getCounter with 2
Done incrementValue 2
In incrementValue 1
calling getCounter with 2
Done incrementValue 1

This log showes two issues relative to argument "1" (a) should not update the result with argument "1" because "1" is already replaced by "2". (b) when updating the result with argument "1", should not calling getCounter of argument "2" (unnecessary and wrong callback). Soft change (dirty cell and auto recalculation) did not work well. In addition, is it good to remove the XVolatileResult object associated with argument "1" from the hash table since it is not used in the spreadsheet, but still run in the back?

(3) inserted "1" in B1 and "=counter(B1)" in B2. The corresponding log is
calling getCounter with 1
will call incrementValue
In incrementValue 2
calling getCounter with 2
Done incrementValue 2
In incrementValue 1
calling getCounter with 2
calling getCounter with 1
Done incrementValue 1

This log continues to show the issues relative to argument "1":  the second "calling getCounter with 2" should be stopped.

(4) deleted the formula ("=counter(A1)") in A2 . The corresponding log is
will call incrementValue
In incrementValue 2
Done incrementValue 2
In incrementValue 1
calling getCounter with 1
Done incrementValue 1

This log is reasonable now. two callbacks relative to argument '2" were dropped. It showed that hard change works well with XVolatileResult.

(5) inserted the formula ("=counter(A1)") back in A2. The corresponding log is
will call incrementValue
In incrementValue 2
calling getCounter with 2
Done incrementValue 2
In incrementValue 1
calling getCounter with 1
Done incrementValue 1

This log is reasonable too. The callback relative to argument "2" is resumed. Hard change works well with XVolatileResult.

(6) close the spreadsheet window. The corresponding log is
will call incrementValue
In incrementValue 2
removing a listener. Total is 0
removing a listener. Total is 0
Done incrementValue 2
In incrementValue 1
Done incrementValue 1

From the above log, I found that the removal of listeners happened in the middle of counts update. So there is a race condition. 

Current behavior: in a heavy streaming data environment, avoid changing cell values in spreadsheet. 

Expected behavior: improve the dirty cell/recalculation schema to work with XVolatileResult

Platform (if different from the browser): my OS windows XP. It is a all platform issue.
              
Browser: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20100101 Firefox/13.0.1