Shape creation slow

Started by daihashi, August 27, 2014, 12:37:07 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

daihashi

So I've done further testing, and while dropmany appears to make things faster in both Visio 2010 and Visio 2013... it seems that SetFormulas improves performance for Visio 2010 but not for Visio 2013.

Has anyone else noticed this with Visio 2013? It's pretty frustrating to spend 2-3 hours rewriting a section of code only to find out that there is no performance improvement when there should be.

daihashi

I should note that I am already using the following at the start of my routine, and re-enabling them at the end. So I doubt that this is related to screen updating or recalculations.

Application.ScreenUpdating = False
Application.DeferRecalc = True
Application.LiveDynamics = False
Application.AutoLayout = False
Application.UndoEnabled = False

daihashi

update to others who are also looking to improve their code execution time:

For the initial shape that I posted about, I went ahead and just pre-built the shape. The execution time was just far too long for me to try to improve upon.

For the shape that I posted about yesterday; which is a pair of switches that is made up of 16-17 different shapes, I've managed to improve the execution time here quite a bit.

First I took the 3 seperate "dropmany" and "setformula" sections in the main subroutine and consolidated them into a single drop/set. I then further consolidated 3-4 more called functions into the same dropMany and setFormulas that I have in the main sub routine and it has improved performance by 50%. Yesterday when I ran the code it took ~4 seconds to complete (3 seconds if I removed the progress bar). Today the code executes in about 2.13 seconds (~1.56 with the progress bar disabled).

This is on my home workstation; I estimate that on the corporate laptop... which doesn't have an SSD drive, and is bogged down by Mcafee and a bunch of other stuff, that it will take a bit over 4 seconds to execute. This particular sub drops ~17 shapes, and sets 224 formulas (not including formulas for the pagesheet). Ideally I'd like to get this down to about 0.5 seconds of execution time, but I am probably asking too much of VBA considering how many formulas I'm setting.

I'll keep tweaking things as time permits, but this will have to do for now.


daihashi

Another minor update:

It turns out I was wrong. SetFormulas is running blazing fast, and the slowdown is occuring with dropMany; which takes ~ 1.55-1.6 seconds. The other significant loss of time comes from calling other functions that do not utilize the setformulas method; which I will be fixing very soon.

I've managed to shave an additional .3 seconds off of the execution time from yesterday by simply merging a couple of If Then statements that were repetitive. I also stopped using string evaluations, and instead have opted to use either Len or Ascw when applicable. For example:

Previously I was using a statement like this... the value retrieved will either be "Available" or "Unavailable"; and I am only interested in values that return as "Available":
If pos = "Available" then

I changed it to the following; which saved some time:
If Len(pos) = 9 then

Similarly; I got rid of as many Trim, Replace, and Left functions as possible and instead replaced them with AscW. In all these cases, the 1st character unicode value is enough to continue with the logic in my code. I also created some variables that would retrieve certain results once, and then used the variable throughout the sub when needed:

Previously:
If Replace(ActivePage.PageSheet.cells("PageWidth").ResultStr(""), " in.", "") = 8.5 Then

Changed to:
Dim pwidth As integer
pwidth = AscW(ActivePage.PageSheet.Cells("PageWidth").ResultStr(""))
If pwidth = 56 Then


In areas where conditional string evaluation was necessary, I replaced certain functions such as Replace,Trim,Left, etc.  with Replace$,Trim$ and Left$.  This also gave some improvement.

The execution time has been reduced from 2.13 seconds (yesterday's results), to 1.84 seconds. Once I finish figuring out the most efficient method then I will post up a list of Do's and Don'ts for code efficiency. I hope I can get this reduced even further without having to result to reduce the number of unique shapes/increase the number of geometry sections. Unfortunately I need these to remain seperate for text/formatting purposes.

daihashi

#19
wow... I further improved the execution time by another 0.1 seconds by simply moving the section of code for my progress bar up by 3 lines; very interesting.

daihashi

I received further input from an additional 6 co-workers today. Coincidentally 3 of them were using Visio 2010, and another 3 were using Visio 2013... and the people with Visio 2010 experienced very fast build times for the shape that was taking 19-20 seconds to build. The people with Visio 2013 experienced the same slowness that I originally posted about.

So it seems that Visio 2010 is actually must faster than Visio 2013 when it comes to using DropMany and SetFormulas.

I already resolved this issue a few days ago by simply creating a pre-built master, and dropping that onto the page instead.... but I found it very interesting that Visio 2010 is much better than Visio 2013 when it comes to handling DropMany/SetFormulas.

daihashi

I've gone ahead and reported this to Microsoft. The slowness, for anyone making heavy use of dropmany, is simply unbearable. There are lots of valid reasons and use cases to use drop many; I know I can simply resolve this by creating pre-built masters... but the fact that I do not have this problem with Visio 2010, and that I can replicate this on other computers using Visio2010 and Visio 2013, indicates to me that this is a problem that Microsoft really needs to address.

Hopefully they will take my comment seriously, and not take nearly 2 years to release a hotfix... like what they did for a different issue on Microsoft's forums.

daihashi

#22
it seems that the delay in visio 2013 is coming before the Enter Scope 1869 event fires. So the slowness seems to be just a moment before DropMany even starts. I tried catching the enterscope event and executing a doevents, but this doesn't help since it seems to be the moment my code hits the following line.. but somehow still before it even begins doing the dropmany:

intprocessed = ActiveWindow.Page.DropMany(cells1, xyarray, IDarray)

how could I go about making this line executing an event, so that I can catch the event upon hitting that line and try executing a doevents there?

daihashi

wow.. out of curiousity, I tried to see if just performing a regular drop per shape would be any better. It wasn't faster, but to my amazement it is the exact same amount of time as using dropMany. Or I suppose more accurately, dropmany takes as long as simply using drop. hmmm

daihashi

I figured it out; well, I only kind of figured out the problem.

I created a new document to do some further testing in a "clean" visio file. I then started moving some of the sub routines I wanted to test into it, and also started moving the necessary master files. When I went to move the master files it gives me an error# 318.

Error 318 seems to indicate that the master is trying to reference some documentsheet cells in the user defined cell section. I turned on event monitor to capture the events that were triggered upon trying to drop one of these masters, and it is indeed trying to reference a cell that is in my original documents documentsheet. What is bizarre however is that I searched in every shapesheet cell of the master, and there is not a single reference to those documentsheet cells.

In anycase; I decided to go ahead and generate the user-cells that it was complaining about, and it then would let me add the masters. I then ran my sub-routine; which normally takes between 2-8 seconds, and it only took 0.15 seconds.

So the problem seems to be with the original document. I'm not entirely sure what is corrupt about it, but something inside the original document is causing dropmany to perform very slowly. What is frustrating is that the problem does not exist with visio 2010. I suspect that the reason why 2010 performs well is because I originally created the document in Visio 2010. I just wish I knew what specifically was different so I don't have to recreate my original document.

daihashi

#25
I narrowed it down even further. It seems to be related to the large amount of shapedata that I have in the documentsheet. I've been able to reduce this dramatically by only having a handful of cells to place my format data in, and then have the remaining format cells reference those. Again, visio 2010 has no issue with the same amount of data... so it's peculiar that Visio 2013 has problems with it.

Prior to doing this the drop time on my home workstation was 2 seconds (~7-8 seconds on my corporate laptop), without a shape data section in the documentsheet the drop time is .15 seconds, and the drop time with the optimized document sheet is now .45 seconds.

daihashi

I was able to test the "fresh" document on my corporate laptop today, and the total execution time is 2.10 - 2.17 seconds. This is significantly better, although I would still like to get it closer to about 1 second total time.

I wonder why Visio 2013 struggles, while Visio 2010 has no issue handling the same amount of information in the document sheet without the need for me to modify or optimize anything.

wapperdude

It's obvious, V2013 is an upgrade and an improvement over V2010.   :o

Wapperdude
Firm believer that V2007 was the best version.
Visio 2019 Pro

Paul Herber

Electronic and Electrical engineering, business and software stencils for Visio -

https://www.paulherber.co.uk/

daihashi

I've never experienced Visio 2007, but I have no doubt that you are probably right. The most frustrating part of making these heavily automated templates are the users; or the corporate images available.. depending on how you look at it.

Some people refuse to move off of Visio 2007 (understandably), others refuse to get off of 2010, and others still have decided to upgrade to 2013. To make matters worse the 2013 users can be either 32 bit or 64bit. So my templates have to account for all these variations.

I'm not fond of corporate application control through the use of SMS pushes, but this is one case where I wish it was being done. Coding for 1 version of Visio would make this much easier.

In any case, I'm just glad I was able to deduce where the problem was occurring. I'll make another post in a few days that is written more like an article; outlining all the optimizations I discovered that were useful for Visio 2013, as well as the things that Visio 2013 doesn't seem to handle well... and workaround suggestions.