I am allowing users to upload a relatively large .csv file (about 1MB) - it will contain thousands of records that each represent one of my business objects. I want the users to upload the file and then be displayed paged results (after I serverside parse it, add validation messages, etc). They can review/edit the results page by page until they are satisfied. At this point, they'd click a Save button and all of the data would be saved to my database. Alternatively, they could click a Cancel button - this would release all of the uploaded data and none of it would touch my database.

I would prefer to not use "staging" tables in my database. And by that I mean store the uploaded data in my db when it is uploaded and subsequently delete it if the user chooses to cancel the whole operation. Also, my situation is such that I would have to delete it from the "staging" table even if the user chose to save the data (working with legacy systems).

My initial reasoning for NOT using staging tables is primary the following: this feature could only be a small subset of my needs. I could realistically have several extremely similar features of my site that could necessitate the need for "staging" tables. I would prefer not to have my database littered (for lack of a better word) with such tables.

I would also prefer to avoid saving the file to disk as that type of solution is IMHO not as scalable as storing the file within the database.

Also, it should be noted that all data validation and parsing must (in my circumstances) be done serverside.

My question is this: In order to prevent seemingly excessive/unnecessary "staging" tables in my database, is it a bad practice to store ALL of the uploaded file's data (i.e in a byte array or base64 encoded string) in a property on my view model? The intent being to keep this large-ish value on my view model while the user pages through and reviews/edits it.

Although I think this question is fairly technology independent, if it helps, I am using ASP.Net MVC/Web API and an MSSQL database.

  • 1
    How many concurrent users are you expecting? Multiply that by 1+ MB - will the server be OK with that? These days it's probably not as big an issue anymore, but I still had to ask. – Dan Pichelman Feb 4 '15 at 0:36
  • @DanPichelman Good question. This could be considered a relatively rarely used feature of my site. While I don't have exact numbers to give you, could we assume that my server infrastructure is scalable and that it wouldn't be an issue? – rwisch45 Feb 4 '15 at 1:16
  • Can't you save the file to disk and read the csv file a few rows at a time, parse, display results without loading the hole thing into memory? – JeffO Feb 4 '15 at 1:29
  • 2
    I don't know why you think saving to disk would be less scalable than anything else. Actually, you have to store the thing somewhere before saving the objects into your DB, but maybe not on the server. IMHO you should clarify this: is it necessary during your review/edit phase to upload the file to the server first? Or can the parsing / validation be done completely on the client without transferring the whole file to the server before? This depends on how your validation looks like and which data it needs. – Doc Brown Feb 4 '15 at 1:47
  • 2
    Implement them both and measure the differences; you are asking a system performance question which cannot be predicted, only measured. If you find implementing two methods too onerous, then pick the simplest one and implement that. You are wishing to buy yourself a headache for purposes of premature optimization. This question cannot be answered. – msw Feb 4 '15 at 5:29

Storing files in memory doesn't seem such a bad idea if there are only a few of them. 1Mb of data isn't very much on modern servers so it's all down to the level of simultaneous users you have and what happens when you 'run out' of memory.

In my experience I try to reduce the memory footprint size of each web service request because the server is usually a better judge of caching and allocating memory between processes and it's sometimes difficult to judge or even test how an application scales when the number of users increase and the memory pool is reducing rapidly as a result. If you go down this route then I'd recommend very careful load testing if memory is even slightly under stress.

Also, if you keep the pre-checked file in memory only then debugging/auditing the upload/edit/save cycle becomes harder because you have no record of what the file looked like before it was reviewed and saved so if you have any issue with the editing code long term (or a user even disputes what your editing code does) you can't trace back and look at the code's effect, even for a short period of hours or days. In memory processing needs more effort with debug/audit tools creation.

Alternatively, two possible suggestions:

  1. Have you considered running a second database instance with staging/temporary working tables in that database only? This would keep your main database 'clean' and (possibly expensive) deletion and post-deletion optimisation functions off your main database too. You could also store a 'purge' time on the table so a scheduled job then deletes old tables in case of a no-show in terms of a user never clicking the 'save' button. (They'd be prompted to re-upload their file if they left the gap between upload and save beyond a sensible threshold).

  2. If you're not expecting too many concurrent users of this functionality then you could also consider using an in memory cache (such as memcached) to store the file whilst it is being reviewed etc. A 1Gb memcached would store up to a thousand of your files simultaneously so unless you were under very heavy load, this would save you having to prompt for a re-upload from the user because the cache had flushed out your file. Note that this solution doesn't necessary preclude a good audit trail, if you choose to audit then you can turn on a simple script that reads memcached entries and writes them to disk for later audit without affecting live code.

| improve this answer | |
  • 1. I agree that offloading to a secondary data store could work well, and would simplify maintenance on the primary database. – CodexArcanum Feb 4 '15 at 16:37
  • 2. Would using a memcached store be much advantageous over just using a host cache (like Session or Cache in IIS+ASPNET)? They're both going to be stored in RAM right? – CodexArcanum Feb 4 '15 at 16:39

For simplicity, let you have 3 files upload.aspx preview.aspx and store.aspx

You could place your upload button and all HTML things in upload.aspx and make a post request to preview.aspx with the uploaded contents. The file, preview.aspx, would simply parse the users' file and output it as a value of HTML form and would present a submit/Cancel button for the HTML form. When the form is submitted it would send all the data to store.aspx which would check if the submit button was clicked and would do all the server side tasks (validation & all) and would store it to your db.

| improve this answer | |
  • 2
    This (ASP.Net web forms specific) answer does not even come close to addressing my original concerns, and at the same time it also seemingly disregards the primary focus of my original question. – rwisch45 Feb 4 '15 at 5:02
  • Actually when you do not want to store the data on your server but you need to process it on the server it's about the only way to continuously send it around. You might make it a bit more fluent by not using just traditional requests but implement ajax like features but that's about the limit. Other options need a server side storage of the data somewhere. And that generally (outside of memory databases - oops - memory storage) is about the only option. I suspect your requests are not well understood / well grounded in this case. A simple database solution with a cronjob to clean should work. – Luc Franken Feb 4 '15 at 6:16
  • Well, you said ` Although I think this question is fairly technology independen` . Also, you didn't mention anywhere that you need it not to be related to web forms. – Hritik Feb 4 '15 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.