Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Interview Question: Stack Overflow THAT

DZone's Guide to

Interview Question: Stack Overflow THAT

Ayende Rahien poses a question to readers: Given some data from Stack Overflow, how does one sort that connected data into a GzipStream while keeping performance solid?

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

We are doing perf testing right now, and we are looking into real world datasets to play around with. Luckily for us, Stack Overflow has regular data dumps of size significant enough to be useful for our experiments.

The file that I’m currently looking it is the Posts.xml file, which is about 45GB in size, and looks roughly like this (lot of stuff removed to make the point).

image

Since Stack Overflow is using relational databases, their output is also relational. You can see that each element is a single row, and you have the ParentId in row #7 that points back to row #4.

Basically, row #4 is the question, and row #7 is one of the answers.

What I want is to take all of this data and move it into a more document-like format. In other words, what I want is to have all the answers for a question contained within the question, something like this:

image

The fun part here is that this is a pretty big file, and we are writing the output into a GzipStream, so we don’t really have the option of saving/modifying midway through. Once we have written something out to the GzipStream, it cannot be changed.

So we need to find a way in which we can group all the answers under their questions, but at the same time, the file size is big (much bigger than the memory I have available) so we can’t just keep it all in memory and write it out in the end.

How would you solve this issue? My attempt is currently sitting at roughly 10GB of RAM used after processing about 30GB of XML, but I have to admit that I have thrown it together rather quickly, since I just needed the data and a quick and dirty solution is just fine here.

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:
database ,stack overflow ,performance testing ,data

Published at DZone with permission of Oren Eini, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}