topic Re: Excelxp tagset and speed of processing in ODS and Base Reporting

Excelxp tagset and speed of processing

deleted_user — Wed, 18 Jul 2007 22:52:23 GMT

There were a couple of threads a while back that brought up a speed issue when using excelxp. For example, I am writing a spreadsheet that ends up being some 40 M in size and takes an awful long time to run.

There were a couple of suggestions, some of which I tried and worked, and some that didn't work (probably me).

As I was meditating on the task monitor tick KBs away, it occurred to me that there might be a connection between the file I was writing out and the buffer/lrecl size.

For what I am doing, I have simply taken the default filesize - I specified a path and a filename in the ODS statement. I should also say that I'm writing some 30 or 40 columns in about 20 worksheets using a BY statement.

Oh, and I guess in the interest of full disclosure, I am running on Windoze XP.

Would changing either the LRECL or a buffer (or any other parameter) help speed up the process? Is there a relation that might play a role here?

Re: Excelxp tagset and speed of processing

Cynthia_sas — Thu, 19 Jul 2007 04:02:11 GMT

Hi,
My take on it is that ASCII text files can be HUGE. There is a lot of XML associated with each cell and each row in your Excel file. I don't think that LRECL will net you very much improvement -- but you can always test by putting a big LRECL on your FILENAME statement -- I think LRECL will work for you on Windows, now.

But you're creating an operating system (Windows) file -- so it was my memory (from mainframe days) that changing BUFNO or BUFSIZE only had an effect on reading/writing/processing SAS datasets -- not on overall IO, but I could be wrong about that.

Tech Support would be where I would go for help, if I had to tune the performance of my code.

cynthia

Re: Excelxp tagset and speed of processing

OS2Rules — Thu, 19 Jul 2007 15:18:16 GMT

Hi:

We also had a similar problem - when the writing to a small Excel spreadsheet there was no obvious problem, but writing to a large spreadsheet it took (what seemed) forever.

We reported this to SAS and they provided a 'test' program that wrote a spreadsheet with 6000 rows and 50 columns. This took over 2 hours to run when we tried it.

In order to fix the problem we updated our software to current level of 9.1.3 and the problems went away - the same spreadsheet takes between 1 and 2 minutes. We used updated install CD's for one system and the patch (hotfix?) that SAS provides as a download on a different machine - both worked fine.

Re: Excelxp tagset and speed of processing

deleted_user — Thu, 19 Jul 2007 18:30:42 GMT

Hooray, my browser works, my browser works.

Tried to reply to Cynthia earlier and oh, boy, thought I was in serious trouble this time - I was about ready to blame SAS on this one.

OK. Guess it's time to try tech support, although I'm up to date on patches and versions and such.

Difference between OS2Rules (I agree) and my stuff is that I'm writing 12 months, 4 quarters, and YTD each one of which has about 1200 rows and 20 columns. I don't know that that would materially impact things - text is text. The data set is only some 25,000 observations - not what I think of when I think of big.

On the buffers stuff, I was thinking that at one time MS file system was extraordinarily wasteful of space and that there was a time when I would have to set buffer sizes so as to minimize paging in a SAS dataset. I didn't know if something like that was in play here as well.

Thanks to both of you. Onward to Tech Support.