ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Blake Watson
I'm using conditionals in a new spreadsheet and finding it to be slow. I've
tracked down the issue to getConditionalFormattingForCell, and I wonder if
I'm using it right.

As a frame of reference, the code in question takes about 0.4 seconds
without the calls to gCFFC and about 4 seconds with it. There are 784 cells
on the page that need to be rendered, so gCFFC is called that many times.

​Looking at the code, though, it seems like maybe there's a lot going on
there that doesn't need to be repeated 784 times, if ​it were known that a
series of cells on the same sheet were all going to ask the same question.

But am I even using the right class in the right way?
--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Greg Woolsey
Could this be related to bug #61841 - Unnecessary long computation when
evaluating VLOOKUP on all column reference?

I fixed that earlier this month in trunk.  Wasn't a problem with
conditional formatting itself, but rather an optimization of certain
formula constructs, especially ones using full-column references like "B:C"
in range expressions.

Otherwise, the expected use is as defined in the example found in
ConditionalFormats.evaluateRules(Workbook, sheet) [1]

Yes a lot of things are checked for every cell, but most fail fast, and are
necessary for completeness.

If you have a specific example, I'd be interested to take a look,
especially if you already have profiling information on which methods are
taking too long or being called too often.


[1]
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java

On Thu, Dec 21, 2017 at 6:05 PM Blake Watson <[hidden email]> wrote:

> I'm using conditionals in a new spreadsheet and finding it to be slow. I've
> tracked down the issue to getConditionalFormattingForCell, and I wonder if
> I'm using it right.
>
> As a frame of reference, the code in question takes about 0.4 seconds
> without the calls to gCFFC and about 4 seconds with it. There are 784 cells
> on the page that need to be rendered, so gCFFC is called that many times.
>
> ​Looking at the code, though, it seems like maybe there's a lot going on
> there that doesn't need to be repeated 784 times, if ​it were known that a
> series of cells on the same sheet were all going to ask the same question.
>
> But am I even using the right class in the right way?
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742 <(805)%20330-4911>
> [hidden email]
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Blake Watson
>>Could this be related to bug #61841 - Unnecessary long computation when evaluating
VLOOKUP on all column reference?

​A quick removal of the VLOOKUPs in the spreadsheet would seem to indicate
that that is not the issue. (I'm not great at Excel, heh, but a workbook
search for VLOOKUP turns up only two calls, and I took those out with no
appreciable change in the performance.)

That does raise that the issue might be formula-related, though, I hadn't
really thought of that. Although since I can evaluate the whole spreadsheet
in .075 seconds and doing the conditional checks takes 1.5 seconds, I think
that would rule out the formulae being the problem.

I'm trying to isolate the problem but I should probably try this against
trunk. When I go to the distribution page to download it (from the main
downloads page), it's a 404.

https://builds.apache.org/view/P/view/POI/job/POI-DSL-1.6/lastSuccessfulBuild/artifact/build/dist/

===Blake===
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Dominik Stadler
Hi,

Trunk now requires Java 1.8, thus nightly builds are available at
https://builds.apache.org/view/P/view/POI/job/POI-DSL-1.8/lastSuccessfulBuild/artifact/build/dist/
now.

Dominik

On Dec 29, 2017 19:35, "Blake Watson" <[hidden email]> wrote:

>>Could this be related to bug #61841 - Unnecessary long computation when
evaluating
VLOOKUP on all column reference?

​A quick removal of the VLOOKUPs in the spreadsheet would seem to indicate
that that is not the issue. (I'm not great at Excel, heh, but a workbook
search for VLOOKUP turns up only two calls, and I took those out with no
appreciable change in the performance.)

That does raise that the issue might be formula-related, though, I hadn't
really thought of that. Although since I can evaluate the whole spreadsheet
in .075 seconds and doing the conditional checks takes 1.5 seconds, I think
that would rule out the formulae being the problem.

I'm trying to isolate the problem but I should probably try this against
trunk. When I go to the distribution page to download it (from the main
downloads page), it's a 404.

https://builds.apache.org/view/P/view/POI/job/POI-DSL-1.6/
lastSuccessfulBuild/artifact/build/dist/

===Blake===
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Blake Watson
I dropped the JARs into my Maven directories which almost seemed to work,
but I'm getting ClassNotFoundException for org.apache.xmlbeans.XMLObject.

Maybe related to 59268​
​?​ I see the xmlbeans jar in the package but if I swap it out for the
existing xmlbeans-2.6.0.jar that doesn't seem to change anything.
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Greg Woolsey
I can barely function with Maven, so I'm no help on that front,
unfortunately.  I know folks have had various issues and questions about
XMLBeans and classpath issues on the mailing list and StackOverflow,
though, so you may find some answers in those archives.

On Tue, Jan 2, 2018 at 2:29 PM Blake Watson <[hidden email]> wrote:

> I dropped the JARs into my Maven directories which almost seemed to work,
> but I'm getting ClassNotFoundException for org.apache.xmlbeans.XMLObject.
>
> Maybe related to 59268​
> ​?​ I see the xmlbeans jar in the package but if I swap it out for the
> existing xmlbeans-2.6.0.jar that doesn't seem to change anything.
>
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Blake Watson
What's weird is, XMLBeans doesn't appear to be any different in the
downloaded trunk. It's the exact same size as the one I've been using.

On Tue, Jan 2, 2018 at 4:04 PM, Greg Woolsey <[hidden email]> wrote:

> I can barely function with Maven, so I'm no help on that front,
> unfortunately.  I know folks have had various issues and questions about
> XMLBeans and classpath issues on the mailing list and StackOverflow,
> though, so you may find some answers in those archives.
>
> On Tue, Jan 2, 2018 at 2:29 PM Blake Watson <[hidden email]>
> wrote:
>
> > I dropped the JARs into my Maven directories which almost seemed to work,
> > but I'm getting ClassNotFoundException for org.apache.xmlbeans.XMLObject.
> >
> > Maybe related to 59268​
> > ​?​ I see the xmlbeans jar in the package but if I swap it out for the
> > existing xmlbeans-2.6.0.jar that doesn't seem to change anything.
> >
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Blake Watson
The POM for 3.17 doesn't include XMLBeans or Commons-Collections4.

What happened to CELL_TYPE_FORMULA? Is there a list of changes for 4?

On Tue, Jan 2, 2018 at 9:44 PM, Blake Watson <[hidden email]> wrote:

> What's weird is, XMLBeans doesn't appear to be any different in the
> downloaded trunk. It's the exact same size as the one I've been using.
>
> On Tue, Jan 2, 2018 at 4:04 PM, Greg Woolsey <[hidden email]>
> wrote:
>
>> I can barely function with Maven, so I'm no help on that front,
>> unfortunately.  I know folks have had various issues and questions about
>> XMLBeans and classpath issues on the mailing list and StackOverflow,
>> though, so you may find some answers in those archives.
>>
>> On Tue, Jan 2, 2018 at 2:29 PM Blake Watson <[hidden email]>
>> wrote:
>>
>> > I dropped the JARs into my Maven directories which almost seemed to
>> work,
>> > but I'm getting ClassNotFoundException for
>> org.apache.xmlbeans.XMLObject.
>> >
>> > Maybe related to 59268​
>> > ​?​ I see the xmlbeans jar in the package but if I swap it out for the
>> > existing xmlbeans-2.6.0.jar that doesn't seem to change anything.
>> >
>>
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> [hidden email]
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Blake Watson
Never mind. Found it. =)

On Wed, Jan 3, 2018 at 2:34 PM, Blake Watson <[hidden email]> wrote:

> The POM for 3.17 doesn't include XMLBeans or Commons-Collections4.
>
> What happened to CELL_TYPE_FORMULA? Is there a list of changes for 4?
>
> On Tue, Jan 2, 2018 at 9:44 PM, Blake Watson <[hidden email]>
> wrote:
>
>> What's weird is, XMLBeans doesn't appear to be any different in the
>> downloaded trunk. It's the exact same size as the one I've been using.
>>
>> On Tue, Jan 2, 2018 at 4:04 PM, Greg Woolsey <[hidden email]>
>> wrote:
>>
>>> I can barely function with Maven, so I'm no help on that front,
>>> unfortunately.  I know folks have had various issues and questions about
>>> XMLBeans and classpath issues on the mailing list and StackOverflow,
>>> though, so you may find some answers in those archives.
>>>
>>> On Tue, Jan 2, 2018 at 2:29 PM Blake Watson <[hidden email]>
>>> wrote:
>>>
>>> > I dropped the JARs into my Maven directories which almost seemed to
>>> work,
>>> > but I'm getting ClassNotFoundException for
>>> org.apache.xmlbeans.XMLObject.
>>> >
>>> > Maybe related to 59268​
>>> > ​?​ I see the xmlbeans jar in the package but if I swap it out for the
>>> > existing xmlbeans-2.6.0.jar that doesn't seem to change anything.
>>> >
>>>
>>
>>
>>
>> --
>>
>> *Blake Watson*
>>
>> *PNMAC*
>> Application Development Manager
>> 5898 Condor Drive
>> Moorpark, CA 93021
>> (805) 330.4911 x7742
>> [hidden email]
>> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>>
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> [hidden email]
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Blake Watson
OK. *Phew* Got it all working aaaand...it has no impact on the speed. So
I'm going to go back and see if I can't find the issue in the conditional
stuff.

On Wed, Jan 3, 2018 at 2:41 PM, Blake Watson <[hidden email]> wrote:

> Never mind. Found it. =)
>
> On Wed, Jan 3, 2018 at 2:34 PM, Blake Watson <[hidden email]>
> wrote:
>
>> The POM for 3.17 doesn't include XMLBeans or Commons-Collections4.
>>
>> What happened to CELL_TYPE_FORMULA? Is there a list of changes for 4?
>>
>> On Tue, Jan 2, 2018 at 9:44 PM, Blake Watson <[hidden email]>
>> wrote:
>>
>>> What's weird is, XMLBeans doesn't appear to be any different in the
>>> downloaded trunk. It's the exact same size as the one I've been using.
>>>
>>> On Tue, Jan 2, 2018 at 4:04 PM, Greg Woolsey <[hidden email]>
>>> wrote:
>>>
>>>> I can barely function with Maven, so I'm no help on that front,
>>>> unfortunately.  I know folks have had various issues and questions about
>>>> XMLBeans and classpath issues on the mailing list and StackOverflow,
>>>> though, so you may find some answers in those archives.
>>>>
>>>> On Tue, Jan 2, 2018 at 2:29 PM Blake Watson <[hidden email]>
>>>> wrote:
>>>>
>>>> > I dropped the JARs into my Maven directories which almost seemed to
>>>> work,
>>>> > but I'm getting ClassNotFoundException for
>>>> org.apache.xmlbeans.XMLObject.
>>>> >
>>>> > Maybe related to 59268​
>>>> > ​?​ I see the xmlbeans jar in the package but if I swap it out for the
>>>> > existing xmlbeans-2.6.0.jar that doesn't seem to change anything.
>>>> >
>>>>
>>>
>>>
>>>
>>> --
>>>
>>> *Blake Watson*
>>>
>>> *PNMAC*
>>> Application Development Manager
>>> 5898 Condor Drive
>>> Moorpark, CA 93021
>>> (805) 330.4911 x7742
>>> [hidden email]
>>> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>>>
>>
>>
>>
>> --
>>
>> *Blake Watson*
>>
>> *PNMAC*
>> Application Development Manager
>> 5898 Condor Drive
>> Moorpark, CA 93021
>> (805) 330.4911 x7742
>> [hidden email]
>> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>>
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> [hidden email]
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Blake Watson
OK, FWIW, I did this:

1. I get all the rules on the sheet...
2. ...and map the rules to their formatting...
3. ...then call getMatchingCells for each rule...
4. ...and create a map of cells to formatting...
5. ...then merge the maps for all rules...

I ended up with a hash-map of cell=>formatting that is much faster (20x) to
reference for each cell than calling .getConditionalForCell. (That's for
one 800 cell sheet on a smallish spreadsheet of about 70K with three simple
conditional formatting rules.)

If I can Java-ize it, it might be worth putting into a utility unit.

===Blake===
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Greg Woolsey
Interesting!  I'd love to see that as Java to run some comparisons, but I
don't have the time right now to investigate.  If you do it, I'd evaluate
and commit it if appropriate.

On Tue, Jan 23, 2018 at 1:32 PM Blake Watson <[hidden email]> wrote:

> OK, FWIW, I did this:
>
> 1. I get all the rules on the sheet...
> 2. ...and map the rules to their formatting...
> 3. ...then call getMatchingCells for each rule...
> 4. ...and create a map of cells to formatting...
> 5. ...then merge the maps for all rules...
>
> I ended up with a hash-map of cell=>formatting that is much faster (20x) to
> reference for each cell than calling .getConditionalForCell. (That's for
> one 800 cell sheet on a smallish spreadsheet of about 70K with three simple
> conditional formatting rules.)
>
> If I can Java-ize it, it might be worth putting into a utility unit.
>
> ===Blake===
>
Reply | Threaded
Open this post in threaded view
|

Re: ConditionalFormattingEvaluator.getConditionalFormattingForCell: slow?

Blake Watson
Cool! Thanks, Greg!

On Tue, Jan 23, 2018 at 1:59 PM, Greg Woolsey <[hidden email]>
wrote:

> Interesting!  I'd love to see that as Java to run some comparisons, but I
> don't have the time right now to investigate.  If you do it, I'd evaluate
> and commit it if appropriate.
>
> On Tue, Jan 23, 2018 at 1:32 PM Blake Watson <[hidden email]>
> wrote:
>
> > OK, FWIW, I did this:
> >
> > 1. I get all the rules on the sheet...
> > 2. ...and map the rules to their formatting...
> > 3. ...then call getMatchingCells for each rule...
> > 4. ...and create a map of cells to formatting...
> > 5. ...then merge the maps for all rules...
> >
> > I ended up with a hash-map of cell=>formatting that is much faster (20x)
> to
> > reference for each cell than calling .getConditionalForCell. (That's for
> > one 800 cell sheet on a smallish spreadsheet of about 70K with three
> simple
> > conditional formatting rules.)
> >
> > If I can Java-ize it, it might be worth putting into a utility unit.
> >
> > ===Blake===
> >
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>