Performance of an XML file format for spreadsheets

re: Performance of an XML file format for spreadsheets

Adam — Thu, 26 Oct 2006 13:49:48 GMT

IMHO, optimisation of what is intended to be a long-lived format, in order to suit current parsers and CPUs is a bad trade-off.

If people want the next generation of file formats to last at least 20 years (not unreasonable, I've heard requests for a 100-year format), then lets consider where we'll be in 10 years time.

Lets see, if Moore's Law holds, CPUs will have 2^7 times as many transistors, or be roughly 100 times more powerful than they are now. Not only that, but with a 2 year release cycle, you could completely rewrite your parser *5* times in order to squeeze every last instruction out of the encoding and decoding processes.

And that's only half-way through a short lifecycle.

If you're sacrificing simplicity, consistency, ease of (re)implementation, etc... in the standard, which is almost guaranteed to outlast any given implementation of it, to make the current (reference?) implementation quicker, I personally think that's a bad trade-off.

Are MS really thinking 20+ years down the line when they're considering some of the decisions here?

re: Performance of an XML file format for spreadsheets

Francis — Thu, 26 Oct 2006 15:56:13 GMT

Hitting the limits is not as hard as you think. I hit the old 65k row limit. I have also hit the new 1 million row limit.

Many data archives, e.g. the renowned General Social Survey and World Values Surveys, have hundreds, if not thousands, of columns, and hundreds of thousands, if not millions of rows. And the U.S. census, even a 1% extract, would still overwhelm Excel 2007 with 3 million rows.

Incidentally, have you tried opening the part 4 of the OpenXML spec in Word B2TR? It takes about 10 minutes on my computer, and that is not including post-opening repagination.

re: Performance of an XML file format for spreadsheets

hAl — Thu, 26 Oct 2006 16:17:51 GMT

I think the 1 million row limit is too low myself.

would have preffered 16 million rows by 1000 columns in such dimensions.

We use excel to manipulate certain large query dumps and that often results in large amounts of rows but very few columns. Upping the limit to a million row is a major advancement for us even so much that we already use some Office 2007 test version for that.

The performance of the 2007 beta version with such large files is mediocre to poor but still faster then OOo 1.x which seems uable to handle certain large loads.

I hope the performance of the final version will improve quite a bit still.

re: Performance of an XML file format for spreadsheets

Doug — Thu, 26 Oct 2006 16:38:14 GMT

It would seem that Francis and hAl are manipulating data on a scale that is better suited to a database.

re: Performance of an XML file format for spreadsheets

Ben Langhinrichs — Thu, 26 Oct 2006 17:32:24 GMT

Brian -

I tend to think the whole debate about tag size (your #2) if overrated, both by Rob Weir and you. It seems unlikely to make a huge difference in speed, but if it does, I hardly think that the "human-readable" ODF tags are wildly preferable. Let's face it, anybody manipulating XML is going to have to look up names and such anyway, so I see every reason to keep things short and simple. This is definitely an area where Open XML seems better to me, although I do think even Open XML loses some of the advantage by nesting elements more deeply than I would like. Still, I would guess that I think that because I have not seen more complex examples where it is important.

The issues in your #3 and #4 seem the more important and relevant. Rob even states in his post that because of Microsoft's limitations on beta benchmarks, he can't use a real life example, but this is a case where his performance evaluation clearly doesn't take into account the very important logic of on-demand parsing and use. It makes complete sense to split things up into more smaller files, and this is an area where I think ODF could be improved. If the breaking into smaller files is done logically, and I don't know the details of how this is done in Open XML, this could make a huge difference in larger, complex documents. If I join the ODF TC, which I am considering, I would definitely advocate for looking at how Open XML implements both #3 and #4 to see how ODF could be improved. These are the sorts of areas where I hope both ODF and Open XML TCs look at the other spec and see what can be learned. Not just copied, but learned.

- Ben

re: Performance of an XML file format for spreadsheets

sinleeh — Thu, 26 Oct 2006 17:41:20 GMT

Dear Brian,

Is there a theoretical limit on how many cells SpreadsheetML can handle, or are the limits simply the results of hardware limitation?

re: Performance of an XML file format for spreadsheets

hAl — Thu, 26 Oct 2006 21:05:05 GMT

@Doug

We export data from the database because it is easier to manipulate in excel than with the database tools we have access to. Also it is easy to combine data from other databases or sorted files into the spreadsheet afterwards.

re: Performance of an XML file format for spreadsheets

Francis — Thu, 26 Oct 2006 23:10:37 GMT

Doug: much of this information is flat, and a spreadsheet is essentially a flat-file database. The data sources I mentioned has no relationships (or objects), so a relational (or object-oriented) database offers no advantages. And databases usually do not offer the sort of functions that a good spreadsheet does.

Besides, easy-to-use desktop databases that handle this volume of data are few and far between. (Access' 2007 unchanged 256 column limit means it is even more limited than Excel.)

re: Performance of an XML file format for spreadsheets

Dean Harding — Fri, 27 Oct 2006 02:28:21 GMT

Adam: That's an insane argument! Don't develop performant file formats because in 7 YEARS computers will be fast enough to handle a slower one? In the meantime, nobody is going to use the slow one!

re: Performance of an XML file format for spreadsheets

biff — Fri, 27 Oct 2006 03:19:34 GMT

Adam, Dean: it's even worse than that - there are plenty of 7 year old computers around and those people buy today will stay around for years and years, so designing a format that is dog slow right now is not an option.

There are people who would suffer degraded performance for their ideology, for the rest of us there are better formats :)

re: Performance of an XML file format for spreadsheets

Dennis E. Hamilton — Sat, 28 Oct 2006 18:45:39 GMT

And if you get the performance now, 7 years from now it will simply be astounding!

re: Performance of an XML file format for spreadsheets

assman — Wed, 01 Nov 2006 10:10:09 GMT

Why can't you get the best of both worlds? Why not have both a format with long names and a corresponding version with short ones. In fact I don't understand why you even need a textual representation of xml. Couldn't you have a binary version of xml which could be automatically viewed as a textual representation through the action of a simple one to one translator. All you need is some standard binary serialization format for XML. I think W3C is doing some work in this area:

http://www.w3.org/XML/Binary/

You would then have a zip container which when unzipped gives you a serialized binary which when unserialized gives you pure xml.

I guess the problem with the solution is that there is no standardized way of doing binary serialization of xml and therefore serializing it yourself makes your format less open. However I think serialization could be done in an extremely simple, transparent and easy way which would be very easy to duplicate.

re: Performance of an XML file format for spreadsheets

Brutus — Thu, 02 Nov 2006 07:25:00 GMT

@Ben

I don't think "human readable" tags are worth the speed hit because humans are generally not going to be mucking with XML themselves via eye-balling. 99.999999999% of the time computer programs will be manipulating the XML, not human beings.

re: Performance of an XML file format for spreadsheets

BrianJones — Thu, 02 Nov 2006 17:37:23 GMT

I think it's also worth looking at the XML itself and deciding what you think is human readable.

HTML, which most folks would say is human readable does the following for a table:

SpreadsheetML does this:

ODF does this:

<table:table>
<table:table-row>
   <table:table-cell>
     <text:p>A1</text:p>
   </table:table-cell>
   <table:table-cell>
     <text:p>B1</text:p>
   </table:table-cell>
</table:table-row>
</table:table>

So, while OpenXML does use terse tag names, it's just like HTML where you just look up the meaning and from then on it's pretty straightforward.

-Brian

Going offline for a week

Brian Jones: Open XML Formats — Sat, 21 Jul 2007 02:22:25 GMT

I'll be offline for the next week or so. Sorry if I don't answer your e-mails or comments during that

Going offline for a week

Noticias externas — Sat, 21 Jul 2007 03:04:53 GMT

I'll be offline for the next week or so. Sorry if I don't answer your e-mails or comments during

Harmonization: Finding the Differences

Brian Jones: Open XML Formats — Fri, 01 Feb 2008 03:08:27 GMT

There have been some discussions over the past several years about the harmonization of Open XML with

Harmonization: Finding the Differences

Noticias externas — Fri, 01 Feb 2008 03:46:26 GMT

There have been some discussions over the past several years about the harmonization of Open XML with

More thoughts on harmonization

Doug Mahugh — Fri, 01 Feb 2008 09:55:54 GMT

Brian Jones has a post this afternoon on the concept of harmonization of document formats, and in particular

More thoughts on harmonization

Noticias externas — Fri, 01 Feb 2008 10:54:23 GMT

Brian Jones has a post this afternoon on the concept of harmonization of document formats, and in particular

Brian Jones: Open XML Formats : Performance of an XML file format for spreadsheets

Dating — Sun, 01 Jun 2008 17:09:32 GMT

I've blogged in the past about some of the things we did with the SpreadsheetML format to help improve the performance of opening and saving those files. While a file format most likely won't affect the performance of an application once the file is loaded

Brian Jones: Open XML Formats : Performance of an XML file format for spreadsheets

Weddings — Fri, 06 Jun 2008 10:56:54 GMT