How difficult and how much time should it take to export a DB to a flat file?

Question

How difficult of a process is it to export a database to a flat-file? Should it take seconds, minutes, hours, or days to complete an export?

I have no knowledge of the database structure, beyond the following:

• It has approximately 50 items of data on each of about 500k people.
• The data is relatively simple. No video/images/audio/files or even huge chunks of text. It's all names, numbers, and dates.
• The DB is professionally designed and administered. It is modern software run on modern hardware. The people running this are professionals, not newbies/hobbyists/amateurs. This isn't an ancient system being kept alive with tape and gum.

The full story: I have made a freedom of information request to a governmental organization that regulates a profession. They don't want me to have the data.

The organization is required to keep a directory of all of the licensees which includes information on their identity, education, qualifications and licensing status, and disciplinary records. Current and former names, license numbers, license classes, date of licensure, when and where they received their training, and notations for any disciplinary issues.

The law also requires that this directory be made publicly available, and also specified that the agency may charge reasonable fees to provide copies to anybody who requests it. The agency is claiming that the reasonable fees would amount to something like $25k. Would one expect it to take more than a few minutes to generate a CSV with 50 or so columns and 500k rows?

Answer 1

How difficult of a process is it to export a database to a flat-file? Should it take seconds, minutes, hours, or days to complete an export?

Any of the above depending on a number of different variables involved:

1. The physical size of the data
2. The number of tables being exported
3. The number of rows in each of those tables
4. The number of columns in each of those tables
5. The data types / sizes being consumed by each of those columns
6. The hardware of the machine where the database lives (especially the Disk and Memory)
7. The hardware of the machine where the CSV file is being saved to (particularly the Disk)
8. The bandwidth of the network involved between those two machines
9. The type of database system being used (e.g. a RDBMS like SQL Server vs a NoSQL flavored database like MongoDB)
10. The tool being used to do the export (native feature of said database system or in-house developed software)
11. The complexity of the export (e.g. all tables are exported into the same single CSV file, or a separate file per table. Considerations for limitations of the consumers, such as Excel which can only read CSV files with up to ~1 million rows of data, etc)
12. How busy the database / machine of the database server is when the export is initiated
13. Other factors I'm sure I've forgotten...

An exact answer can't be given based on the information you've provided, but let's play out a scenario, with some assumptions, for a rough estimate anyway. You've provided the following starting points:

It has approximately 50 items of data on each of about 500k people.

The data is relatively simple. No video/images/audio/files or even huge chunks of text. It's all names, numbers, and dates.

Let's assume you're using the RDBMS SQL Server, with a pretty typical SSD that can read ~500 MB/s. Let's also assume we're talking about a single table here. Let's distribute the table's columns such that 2/5ths of them are the names type which we'll use VARCHAR(50) for, 2/5ths of the columns are the numbers type which we'll use INT for, and the remaining 1/5th will be the dates type, which we'll use DATETIME for. We'll also make the assumption that the names columns are fully being used, to make the math easier (this is a nominal assumption in our overall rough math).

So adding up the 50 columns in our table for a single row leads to (20 columns * 100 bytes for VARCHAR) + (20 columns * 4 bytes for INT) + (10 columns * 8 bytes for DATETIME) = 2,160 bytes total = ~2 KB. If we multiply that by the 500,000 people (assuming 1 row per person) that gives us roughly 1,000,000 KB = 1 GB of data to export.

So for a single table with ~1 GB of data, to load off of an SSD that gets ~500 MB/s, should only take a few seconds to read the data off Disk. Then there'll be other bottlenecks. E.g. if the network bandwidth is 1 Gigabit = 125 MB/s that's another 8 seconds or so, to transfer the data between the machine of the database and the destination for the CSV. Then writing the data back to a file itself will be dependent on the Disk speeds of the destination, etc.

All in, with everything being perfect, for the amount of data in our example scenario above, it should take somewhere less than 1 minute. In a realistic world, with a busy database server, the other variables coming more into play, and / or with more data, you could be looking at something that takes a few minutes, or longer.