Stream (.NET) handling best-practices

Question

The question is entitled with the word "Stream" because the question below is a concrete example of a more generic doubt I have about Streams:

I have a problem that accepts two solutions and I want to know the best one:

1. I download a file, save it to disk (2 min), read it and write the contents to the DB (+ 2 min).
2. I download a file and write the contents directly to the DB (3 min).

If the write to DB fails I'll have to download again in the second case, but not in the first case.

Which is best? Which would you use?

Answer 1

There's no reason step 2 has to take two minutes twice. While you download the file, you can stream it through variables in memory on the way to the database.

Unless you have a compelling reason to keep a file-system copy of the file, I would go with #2 in most cases.

Answer 2

I would assume that if the write to the database fails due to something in the contents of the file, it will always fail no matter how many times I try to write the same contents to the database. In this case, the only solution is to (fix and) re-download the file anyway. If the write to the database is failing because of something in the database, you've got bigger problems than whether you need to download the file again.

Go with Option #2.

Answer 3

I don't understand the qualifiers you've added regarding the times or having to download the file twice, but, if the system is strapped for memory, caching your download to the disk and then sending it to the DB may really be your only option (assuming your data provider can accept a stream).

EDIT: in the original post the author describes writing directly to the database as a two-stage process, which I assume to be 1. Download file into a variable, 2. Stream variable contents to DB. If he's streaming directly into the DB in option 2, then I agree that's a better way to go.

Answer 4

I would go with option two. There shouldn't be failures very often, and when there are you can just re-download. If for some reason you need to have that local copy on the file system then don't download, save, read, and send to database... just download and send to database at the same time you're saving to the file system.

Answer 5

To detail Jekke's reply:

Depending on the file system creates many occasions for failure (you must create a valid file name, make sure the file system isn't full, make sure the file can be opened and written to by you but not by anyone else, what about concurrent use, etcetera).

The only benefit of writing to file I can think of is that you'll know the download completed successfully prior to doing anything with the database. If you can hold the contents in memory, do that instead. If you can't and really insist on not going to the database in case of an interrrupted download, at least use .NET's built-in support to help you with the tricky bits (e.g. IsolatedStorageFileStream).

Answer 6

Unless the increased latency is really killing you, I'd usually go for Option 1 unless there's a good reason you don't want the data on the file system (e.g. concerns about security, capacity, ...).

Or maybe Option 3 as suggested by Max Schmeling, save to the filesystem at the same time as writing to the database.

Disk space is cheap, and it's often useful to have a backup of downloaded data (e.g. to test changes to your database writing code, as evidence of the contents of data downloaded, ...).

Answer 7

I'd choose option 3. Save it to disk and store the URI in the database. I've never been a fan of storing files in a database.

Answer 8

I'd go for a so far not yet mentioned (except in comments perhaps) option mentioned in the subject matter of my blog post about blobstreams: set up a processing pipeline of streams that take care of downloading and interpreting the file you need. Then use code to read interpreted records from this compound stream and do the needed inserts/updates in your database inside one transaction (per file/per record, as per your functional requirements).

This kind of scenario is where Stream based classes excel. It would mean that you never have the entire file anywhere on disk or in memory at the same time while processing. As you mentioned downloading the file takes minutes, it could be big. Can your system take the intermediate storage of the full file (maybe more than once: memory and on disk)? Even if multiple files get processed concurrently?

Also, if you would find out in practice that the chain is not reliable enough for you and you would like to be able to temporarily store the downloaded file to disk and indeed want to then repeat processing of it without having to download it again, this is easy. All that is needed is an extra Stream in the pipeline that would check if the file needed is already in your "already downloaded files" cache (in some folder, in isolated storage, whatever) and return the bytes in that instead of actually looping a downloading Stream into your processing pipeline.