ExcelXP Runs Excessively Long

[OS2Rules]

Happy New Year To All.

I have been having a problem with the ExcelXP tagset running for excessively long periods for large data files. I’ve reported this to SAS, and they are working diligently on it, but I was wondering if anyone else has the same problem.

When running with small data volumes, the tagset seems to run rather efficiently, but when I run with a file of a few thousand records, the output takes a very long time. A sample set with 6000 records takes over 1 and ½ hours (elapsed) to run. I was thinking that my system was a Timex Sinclair z80, but it is actually a Pentium 3.

Anyone have a similar problem? Any recommendations – are there system options that can improve the performance of the output?

[Eric_SAS]

If you are using style attribute over rides in your job that can cause a lot of over head with the excel tagset. But I am just guessing. The tagset is slow due to the amount of processing necessary to create the XML. There are things that you can do in the job that can harm or help performance.

[OS2Rules]

I was at a recent presentation by Vince DelGobbo where he described that various methods of changing the style attributes and how they affect the performance of the output. Unfortunately, I can't find a copy of this - only the original presentation that he did to SUGI.

To the best of my knowledge, I am making my style attribute changes the "best" way in my PROC REPORT.

Thing is - using the test module that was provided by SAS (thanks Chevell), there are no style changes at all and the process still takes 1.5 hours to print approx. 6000 records, so the problem is either in the Tagset, or my PC.

[Eric_SAS]

The trick with the styles is to not use attribute over rides in your proc.

instead of doing something like this in print, report or tabulate,

var foo / style=[foreground=red];

define a style that does that.

style datared from data/
foreground=red
;

Then in the proc,

var foo /style=datared;

That will keep the styles section of the XML from growing too much.
Style processing is expensive, and in the end the file size is so big that
it can take a while for excel to load. Resaving it after it is loaded will
trim it back down.

Also, I talked to Chevell in tech support, apparently this is a special case that they are having trouble replicating. The tests mentioned here run in less than 2 minutes, so it may be a platform / installation issue that is causing this incredible slowness.

[deleted_user]

I concur with the speed issue.

I'm creating a spreadsheet of monthly, quarterly and ytd financial data and depending on data will run upwards of three or four hours.

I write this to network file servers and they may contribute to the problem, although writing to the local drive didn't seem to alleviate things much.

One of these days, I'm going to try running things on our sun server and see if that makes a difference.

FYI, the size of my file is on the order of 30 - 40 MB.

[OS2Rules]

Eric:

The test program that Chevell gave me has no attribute changes in it, so that can't be the problem.

I have also run this code on 2 very different systems (one being a P4 2.6 with 1G Ram), and I have the same problem on both. (The P4 system actually took longer to run the test, but it was heavily multi-tasking.)

For each - the CPU gets pinned at 100% for the duration and the resulting Excel file is about 25M bytes.