You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by dpront <ge...@xmarter.com> on 2011/06/17 06:42:56 UTC
taking too long to create xlsm
Hi Guys,
I have a simple code that reads and writes xlsm file. But the problem is,
the program is taking too long to create the output xlsm file (average time
is 8minutes). By the way, I have substituted the
poi-ooxml-schemas-3.6-20091214.jar into ooxml-schemas-1.1.jar because that
jar has more class files than the poi-ooxml-schemas-3.6-2009121.jar.
The input xlsm file got a size of 1.4MB, has 9 worksheets on it and on
every worksheet has an image of around 1x1 inch. On every worksheet also,
there are some column that has drop down box that consists of legal inputs.
My code is here:
http://pastebin.com/mbXJsHf3
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4497635.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Yegor Kozlov <ye...@dinom.ru>.
http://poi.apache.org/howtobuild.html
Yegor
On Mon, Jun 27, 2011 at 12:03 PM, dpront <ge...@xmarter.com> wrote:
> Hi,
>
> I think the problem is occurring because of the Macro, based on my latest
> screenshot, the ensureRowOrdering method has been called by almost 60,000 +
> times even though the records that are on that excel file is just about 10
> rows.
>
> So, I'm trying to modify the XSSFSheet and recreate the jar but as of now
> I still can't manage to do it. I have downloaded the source code of the
> Apache POI and try to create a jar file out of it using eclipse and is
> currently facing with lots of errors. I already read the Apache POI - How
> To Build and I already have Apache Ant and has executed the "ant -f
> fetch.xml -Ddest=system" and it downloaded some jar files and I got stuck at
> that part.
>
> Any help or enlightenment with regards to the modification of the jar
> files are highly appreciated.
>
> Thanks.
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4527405.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hi,
I think the problem is occurring because of the Macro, based on my latest
screenshot, the ensureRowOrdering method has been called by almost 60,000 +
times even though the records that are on that excel file is just about 10
rows.
So, I'm trying to modify the XSSFSheet and recreate the jar but as of now
I still can't manage to do it. I have downloaded the source code of the
Apache POI and try to create a jar file out of it using eclipse and is
currently facing with lots of errors. I already read the Apache POI - How
To Build and I already have Apache Ant and has executed the "ant -f
fetch.xml -Ddest=system" and it downloaded some jar files and I got stuck at
that part.
Any help or enlightenment with regards to the modification of the jar
files are highly appreciated.
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4527405.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hi John,
I may have found the major cause of this issue (on my side), it is because
of the excel file that I am modifying and is "somewhat" handled by a VBA on
it. So the issue on my side is, Apache POI loops to all rows (up to 65536)
so the overall process is taking too long, I think VBA is the one that makes
the POI loops up to 65k+ times though I am not very sure if it's really the
VBA code that causing this issue, this is just a speculation.
I suggest for you to download a profiler for your IDE (for example
eclipse) for you to be able to trace what exact method the "bottleneck" is
occurring.
About the link, yes it will not work anymore because it's meant to last a
month after I posted it.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p5623033.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by John <po...@gmail.com>.
> And I think that my problem with the "writing" section is already
> half-fixed (can't say 100% because I haven't fully tested it yet because I'm
> trying to solve another issue).
I am having similar issues the write() method taking too long.
>
> The reason behind this is because I'm not doing a batch record
> insert/update or delete. The java code always run for every single insert,
> update or delete.
>
> And here is my java code:
>
> http://privatepaste.com/700b8c338e
>
I tried having a
look at your code http://privatepaste.com/700b8c338 but the link doesn't work ??
Thanks,
john.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hi!
Thanks for all the help and suggestions. I have managed to modify the
code and now I am already producing my excel file faster than before. This
is the result of my latest profiling (filtered one)
http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=ExecutionStatistics.jpg
And I think that my problem with the "writing" section is already
half-fixed (can't say 100% because I haven't fully tested it yet because I'm
trying to solve another issue).
The another issue is, when you checked the screenshot, the creation or
reading of the document is longer also. And to support my previous
screenshot, I have created another profile screenshot that is using a simple
filter.
http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=CallTree-1.jpg
The question is, can I still make it a little bit faster? by the way,
without using a profiler, the code finishes for about 13 seconds. What I
wanted to achieve is to make it finish with 5 seconds or less.
The reason behind this is because I'm not doing a batch record
insert/update or delete. The java code always run for every single insert,
update or delete.
And here is my java code:
http://privatepaste.com/700b8c338e
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4532369.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Yegor Kozlov <ye...@dinom.ru>.
It is very likely that you are facing the same problem as described in
this thread:
http://markmail.org/message/3o7o4t7laesacvwl
On low level, POI uses XmlBeans to manipulate the SpreadsheetML
format. Each row is represented by a CTRow bean and the collection of
rows is held by CTSheetData.
When POI saves a worksheet, it ensures that rows are sorted in
ascending order. If rows are not ordered (inserted in random order or
shuffled by shiftRows) then the expensive stuff happens: rows are
detached from the container, sorted and re-attached. If the number or
rows is large it is a damn expensive operation.
Try to optimize your code to write rows in ascending order.
If you think that the problem has different roots, please post an
*isolated* program that demonstrates the performance issue. It should
not rely any jdbc or external libs, just plain Java and POI.
If it requires a template file then attach that file too.
Yegor
On Fri, Jun 17, 2011 at 8:42 AM, dpront <ge...@xmarter.com> wrote:
> Hi Guys,
>
> I have a simple code that reads and writes xlsm file. But the problem is,
> the program is taking too long to create the output xlsm file (average time
> is 8minutes). By the way, I have substituted the
> poi-ooxml-schemas-3.6-20091214.jar into ooxml-schemas-1.1.jar because that
> jar has more class files than the poi-ooxml-schemas-3.6-2009121.jar.
>
> The input xlsm file got a size of 1.4MB, has 9 worksheets on it and on
> every worksheet has an image of around 1x1 inch. On every worksheet also,
> there are some column that has drop down box that consists of legal inputs.
>
> My code is here:
> http://pastebin.com/mbXJsHf3
>
> Thanks.
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4497635.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Thanks for the reply.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4498301.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Nick Burch <ni...@alfresco.com>.
On Thu, 16 Jun 2011, dpront wrote:
> I have a simple code that reads and writes xlsm file. But the problem is,
> the program is taking too long to create the output xlsm file (average time
> is 8minutes).
I'd suggest you run a profiler and see where the slowdown is
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Dave Fisher <da...@comcast.net>.
On Jun 24, 2011, at 10:06 PM, dpront wrote:
> Hello!
>
> Q:Have you looked to see if you are filling physical memory? In other
> words is your OS paging heavily?
>
> A: Nope, everytime I ran the profiling setup, it's always at 80% and
> not reaching it's peak. However, my CPU Usage stays at 100% until the
> profiling is terminated.
>
> Q: How much RAM? What are your JVM heap settings?
>
> A: I have 2gb of RAM, and the xmx setting on my eclipse configuration
> is set to 384m (I'm not sure if I answered you correctly with this one).
Try increasing your xmx setting to larger values - try 512M and see if that gives you a speedup.
>
> Q: are you writing to a local or remote disk? If local is it nearly full?
>
> A: Local writing, what do you mean by "nearly full" ?
How much free space do you have on that local disk? If it is less than 5% then you could be having trouble writing. You disk is likely to be fragmented and writing can be slowed due to that.
Regards,
Dave
> Q: What OS? What Java Virtual Machine (JVM) version?
>
> A: I'm running on a Windows-7 Environment and has a java version of
> "1.6.0_22"
>
>
> Thanks.
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4523030.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hello!
Q:Have you looked to see if you are filling physical memory? In other
words is your OS paging heavily?
A: Nope, everytime I ran the profiling setup, it's always at 80% and
not reaching it's peak. However, my CPU Usage stays at 100% until the
profiling is terminated.
Q: How much RAM? What are your JVM heap settings?
A: I have 2gb of RAM, and the xmx setting on my eclipse configuration
is set to 384m (I'm not sure if I answered you correctly with this one).
Q: are you writing to a local or remote disk? If local is it nearly full?
A: Local writing, what do you mean by "nearly full" ?
Q: What OS? What Java Virtual Machine (JVM) version?
A: I'm running on a Windows-7 Environment and has a java version of
"1.6.0_22"
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4523030.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Dave Fisher <da...@comcast.net>.
I am going to make a couple of guesses and ask some questions.
Have you looked to see if you are filling physical memory? In other words is your OS paging heavily?
It could help if you describe the memory profile of your computer. How much RAM? What are your JVM heap settings?
Also, are you writing to a local or remote disk? A remote disk can be very slow. If local is it nearly full?
Perhaps some system details would help as well. What OS? What Java Virtual Machine (JVM) version?
Regards,
Dave
On Jun 24, 2011, at 8:09 PM, dpront wrote:
> Hi,
>
> After a day of playing with the Profiling, I think I have managed to
> profile the write method now.
>
> Please see the screenshots below:
>
>
> http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=screenshot1.jpg
>
>
> http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=screenshot2.jpg
>
>
> http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=write.jpg
>
> Thanks.
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4522879.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hi,
After a day of playing with the Profiling, I think I have managed to
profile the write method now.
Please see the screenshots below:
http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=screenshot1.jpg
http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=screenshot2.jpg
http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=write.jpg
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4522879.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hi,
Can you check the snapshot below if my configuration is correct. Because
even though I use that kind of configuration, I can't directly profile the
write method from the Workbook class.
Configuration snapshot:
http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=ProfilingConfiguration.jpg
Result:
http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=ProfileResultwithusermodel.jpg
Or did I just missed something important?
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4519943.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Nick Burch <ni...@alfresco.com>.
On Thu, 23 Jun 2011, dpront wrote:
> Thank you for trying to help me but I'm sorry because I can't provide
> that. :(
In that case, can you please profile into the write method, and see what
in there is taking the time?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hello,
Thank you for trying to help me but I'm sorry because I can't provide
that. :(
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4516759.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Sergey Vladimirov <vl...@gmail.com>.
Hi,
Could you provide an example file which takes a lot of time to
process? (without sensitive data, of course)
--
Best regards,
Sergey Vladimirov
On Thu, Jun 23, 2011 at 5:30 AM, dpront <ge...@xmarter.com> wrote:
> I have both extracted the shiftRow() and the write() method to verify where
> the performance bottleneck is occuring and I found out that it's really on
> the write() method.
>
> Here is the result of the latest profile:
>
> http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=Profileresult-1.jpg
>
> and this is the updated code (took out unnecessary methods for the
> experiment):
>
> http://privatepaste.com/7fea727c89
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hi,
I have both extracted the shiftRow() and the write() method to verify where
the performance bottleneck is occuring and I found out that it's really on
the write() method.
Here is the result of the latest profile:
http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=Profileresult-1.jpg
and this is the updated code (took out unnecessary methods for the
experiment):
http://privatepaste.com/7fea727c89
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4516055.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Sergey Vladimirov <vl...@gmail.com>.
Hi,
1) Try to extract the part around "wb.write(fileOut);" to another
method, so profiler can show if exactly write() method is perfomance
bottlecheck.
2) Personally i think the biggest problem is a lot of calls to
shiftRow() operation. Can profiler show the time spent for this method
execution? If it's the case, then try to replace deleting rows with
coping "good" rows to new sheet and replacing old one with new.
Best regards,
Sergey.
On Wed, Jun 22, 2011 at 1:55 PM, dpront <ge...@xmarter.com> wrote:
> Hi,
>
> I have already used the poi-3.8 but it still yield the same result.
>
> This is the profiling result of my code:
>
> http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=Profileresult.jpg
>
> And here is my latest code so far:
> http://privatepaste.com/2167f7bc2c
>
> Thanks.
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4513516.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Sergey Vladimirov
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hi,
I have already used the poi-3.8 but it still yield the same result.
This is the profiling result of my code:
http://s760.photobucket.com/albums/xx242/gerardagda/?action=view¤t=Profileresult.jpg
And here is my latest code so far:
http://privatepaste.com/2167f7bc2c
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4513516.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Yegor Kozlov <ye...@dinom.ru>.
Performance of xssfworkbook.write was improved in POI-3.7
Details can be found here:
http://markmail.org/message/3o7o4t7laesacvwl
Upgrade your system to POI-3.7 or to the latest 3.8-beta3.
Yegor
On Fri, Jun 17, 2011 at 8:42 AM, dpront <ge...@xmarter.com> wrote:
> Hi Guys,
>
> I have a simple code that reads and writes xlsm file. But the problem is,
> the program is taking too long to create the output xlsm file (average time
> is 8minutes). By the way, I have substituted the
> poi-ooxml-schemas-3.6-20091214.jar into ooxml-schemas-1.1.jar because that
> jar has more class files than the poi-ooxml-schemas-3.6-2009121.jar.
>
> The input xlsm file got a size of 1.4MB, has 9 worksheets on it and on
> every worksheet has an image of around 1x1 inch. On every worksheet also,
> there are some column that has drop down box that consists of legal inputs.
>
> My code is here:
> http://pastebin.com/mbXJsHf3
>
> Thanks.
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4497635.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by Nick Burch <ni...@alfresco.com>.
On Mon, 20 Jun 2011, dpront wrote:
> I have managed to determine where the slowdown is occurring, and it's on
> the Workbook "write" method (took 400+ seconds for completion), and I can't
> find a solution for that because I think that this write method is the one
> responsible for creating the output file.
Hmm, that's odd. Can you profile into the write method and figure out what
bit is taking the time? I've not heard of that kind of slowdown in it
before
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: taking too long to create xlsm
Posted by dpront <ge...@xmarter.com>.
Hi,
I have managed to determine where the slowdown is occurring, and it's on
the Workbook "write" method (took 400+ seconds for completion), and I can't
find a solution for that because I think that this write method is the one
responsible for creating the output file. Any other solutions/suggestions?
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/taking-too-long-to-create-xlsm-tp4497635p4508756.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org