Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am re-writing a section of my site and am trying to decide how much of a rewrite this will be. At the moment I have a web service feed that generates an xml once per day. I then use this xml file on my website to generate the general structure. I am trying to decide if this information should be located in the database or stay in the xml file.

  1. The file can range from 4mb - 12mb.
  2. The files depth can go on and on so I have to recurse to find the data I want.
  3. I use the .NET serializer classes and store the serialized file in a global variable to avoid re-serializing it each time the page is loaded.

My reasons for thinking a database would be better are:

  1. I would know exactly where I am in the file by using an internal ID so I wouldn't have to recurse the file to get information.
  2. I wouldn't have to load / serialize the XML and could just use my already open database connections.
  3. Searching for the data in the file would be quicker(?) as I would just perform an SQL query rather than re-cursing the file.

Has anyone got any ideas which is better and which option uses more resources on the server or be quicker?

EDIT: The file is read every time the web page is loaded (although only serialized once). It isn't written to by standard users (only by an admin task that runs in the middle of the night).

This is my initial investigation before mocking up.

share|improve this question
    
why don't you try creating a mock-up of what the database would be? it seems like you've already talked yourself into using a db, so it may be worth it. :) –  jberger Apr 4 '12 at 15:52
    
Maybe I am missing something but how often do you have to read from this XML file? That would be a loooot of parsing on a file that size if it's per hit. –  Rig Apr 4 '12 at 15:54
    
Please see my edits. –  webnoob Apr 4 '12 at 16:02
add comment

5 Answers

up vote 3 down vote accepted

If I understand correctly, your data changes once exactly per day. To be honest, it doesn't matter if you have it as XML, or it store it row-by-row in a DB; what would make a big difference is if you cached it in your web application.

Basically, you should read in the file at most once per day (this can happen on-demand, just check if your cached version has expired), and store its contents in a structure optimised for quickly fetching information. This will prevent your pages wasting CPU time parsing the file over and over again, as well as saving the garbage collector a lot of work.

Technically, a 10 MB XML file should take up a similar amount of memory as objects (accounting for the lack of bloat of a text-based encoding, but also for the overhead of having Dictionary objects for indexing), which is nothing these days. Changes like these typically result in performance gains of a couple of orders of magnitude.

share|improve this answer
    
This gave me an idea about using an array to store all some small classes with the information in. I then use the ID of the sections in the Xml file as an index in that array. This will allow me to get the information without recursing the whole file as I just call the array index I am currently on. It's hard to explain but I have a good concept from this that will most likely use less resources than the database. –  webnoob Apr 5 '12 at 5:48
    
@webnoob - I think you're on the right track. If you're going to create your own cache (even if it's just a public static variable somewhere), it's a good idea to understand the basics of concurrency, since an ASP.Net environment is multi-threaded by its very nature. Read up on the lock keyword a bit, it's probably the easiest way to achieve thread safety in this instance. –  Daniel B Apr 5 '12 at 6:10
    
Many thanks. Should be able to have the best of both worlds now. The speed with being able to get the data by index and no Sql overhead. –  webnoob Apr 5 '12 at 6:38
add comment

I'm partly echoing what jberger has already said, it seems you've already talked yourself into doing it.

I'd be in favour of moving over to a database, twelve megs is quite a large file. As a series of tables you'd be able to use some of the power of a database to speed up queries, maybe do new things that you've found tedious or laborious before. Again other posters have asked how often are you querying this file. If it's once a day then maybe, just maybe, it might not be worth the effort. However, if it's a complex bit of XML, and 12 MB says it is, then you might find it's time well spent.

I'm not dismissing the idea of using XML files as datastores, I've used them in the past on websites, but then their size and the traffic on those websites mean it was worthwhile. The websites don't get many visitors a month, and the size of the XML files used are measured in KBs.

share|improve this answer
    
I admit, I have talked myself into a DB route but I want to be logical about the decision and make sure its correct. The file is read on every page load of the website. –  webnoob Apr 4 '12 at 16:08
    
I see the point you're making, and it's a good one. Do all the contents of the file need to be read every time a page is loaded? I know the web service isn't used that often (your comment to Maxood). What database were you thinking of using? To create the XML file are you reading from a database? –  Daniel Hollinrake Apr 4 '12 at 16:22
    
The file contents generate the menu and also decide on the content of each section. The XML is created on another server (from a a database) and just sent to me via the webservice. I just save the contents when I get them. Using SQL Server Web Edition 2008 as the rest of the site runs off it. –  webnoob Apr 4 '12 at 16:29
    
You've got three choices. One: leave it as it is. If it's working fine and everyone is happy then leave it alone. Two: change it because people have complained about performance or they want improved functionality which your current set up restricts you from doing. Three: change it because it's an in-house site (I'm guessing) and by doing so it's a great way to improve your skills. Apologies if I've assumed too much here I'm just guessing because of your username! –  Daniel Hollinrake Apr 4 '12 at 16:34
add comment

I've re-read your question again and had another thought. At the moment you are doing all the work on the web-server. If you use a database server then SQL Server caches execution plans. My gut feeling is that by using a database server you'll be using resources more efficiently. With databases you can add indexes onto the tables which can greatly improve performance.

share|improve this answer
add comment

If you talk about cost and resources then consuming a web service is more costly than fetching data from a local or server hosted database.

In your scenario, you also need to keep in view that whether your data is being updated via XML file data or not. If it does then also look into when that happens? By what time period you need to fetch the data from XML(say every 5 mins, 10 mins, 1 hour, 1 year)?

share|improve this answer
    
My XML is updated via a seperate routine at 3am so isn't something I am concerned about. The web service isn't used very often (And only by admin users anyway) so that isn't a factor. The XML file isn't written to. Its only read by general people. –  webnoob Apr 4 '12 at 16:01
add comment

Reading an XML file of that size every time the web page is loaded may become of course a performance bottleneck. Storing the file in a session variable of type XmlDocument (see http://msdn.microsoft.com/en-us/library/ms178581.aspx) reduces this to "one load per user session", which may be a lot faster. Utilizing xpath/xslt query syntax will give you a flexibility comparable to SQL.

Making use of a database as a cache makes only sense if the livetime of your database process is longer than the lifetime of your ASP.NET web page process, so you cannot use an in-memory database like SQL server compact edition. That means, you will have interprocess communication between your web page process and your database process, which is the main performance risk of that solution. Querying a loaded XMLDocument runs completely in-process. So, if it really pays using a database you will only know if you test it, and it is absolutely possible that the database solution will run a lot slower than the cached XML solution. Of course, if your amount of data increases to a size of several hundred MB or more, then there will be some point where a solution which does not load all the available data will be faster.

Disclaimer: I have never programmed an ASP.NET site on my own, so please excuse if I misunderstood something of that architecture.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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