• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Data Storage Ideas

I am developing a portable CMS (Well...no so much a CMS, but more of a site rendering engine), and I find myself with a conundrum in terms of storing the data.

Right now the app is up and working at http://m.realtormanagement.com  (no, there is no design yet, it was just a test to see how fast the render engine took)
and am currently using XML .Config files as storage for the sites "Settings", "Page Templates", and "Page Contents".

I figured XML because I want to be able to upload this app to any windows server in the world and have it work out of the box.   While XML seems to be pretty dang fast, I am concerned that on bigger sites (mostly those with hundreds of content pages), it may slow the rendering down due to the sheer number of "records" in the PageContent.config file, when it first loads.

So...  my question becomes this, what should I use as an embeddable source for storing (and potentially editting) the data.

I've read SQL CE is an embedable solution, as well as SQLLite, (although I do want to stay away from sqllite), but will SQL CE give the performance I would expect it should being SQL and not XML for data.

I do understand that there may be file locking issues with the XML, but I believe I can circumvent that with clever caching :)...  yes this is all done in .Net 4
0
kevp75
Asked:
kevp75
  • 9
  • 7
5 Solutions
 
khairilCommented:
Hi,

I understand when need to choose one over another, as I am in your position before.

If you want performance and small footprint then you should go for SQLite, bu SQLCE have more advantange when incorporating with .net development.

However, there some few guys creating the wrapper for SQLite that works well with Visual Studio, the best is from http://sqlite.phxsoftware.com (it is free too)

Combine smart caching with more powerful database backend is much more better then the caching alone. My friendly suggestion is for you to try out SQLite.... before you decide to hate it :). You should know some limitation of SQLite as well then, http://www.sqlite.org/limits.html

But, if you are planning to go futher with bigger sites. My suggestion then use Enterprise Edition of SQL Server.  We are runing DotNetNuke using SQL Express and Sharepoint using Enterprise SQL Server. Both of this using SQL server to store pages, even files into the database. Of course you'll need more horse power to store everything into database, especially LOB, but the best part is you only need to backup one database file only.

Just my 2 cent.
0
 
kevp75Author Commented:
Well, I just found that SQL CE is .1s slower than loading from XML...

It's not that I hate SQLLite, but I have had problems with it before, so rather than run into those hassles again...

Also, SQL Enterprise is out of the question, though I'd love to be able to do it, my requirements are against have a second object to install.

I need this to be available to any Windows Web Server, and Windows Hosting Service.  I understand most windows server hosting accounts include a SQL database, however, I need to be sure I am not singleing out the ones that do not, and do not wish to host with a company that offers it but at an extra cost...

hence why I though SQL CE, or XML...

right now, in terms of performance with only 5 pages, XML is winning.

XML Data Storage for 5 pages:
.238s Load Time

SQL CE Storgae for 5 pages:
.363s Load Time
0
 
kevp75Author Commented:
Ok...

So, using a SQL 2008 DB:
.566s Load Time  (first hit)
.266s Load Time (every hit after...)

So far folks, it seems like XML is winning...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
khairilCommented:
Yup, it seems like that because xml is file based and do not have overhead of database.  MS SQL have a lot of fine tuning you can do that can burst out performance.

You have proved XML works best for you, but in real environment, it will be a lot of users accessing your site, which might turn the table around. If possible you try stress test your site. There a lot of tools you can use to stress test you site. My favourite tool is VS 2010, but you can have a look on Selenium (http://seleniumhq.org) or Webserver Stress Tool (ttp://www.paessler.com/webstress)

Just my 2 cent.
0
 
kevp75Author Commented:
how can I stress test in VS 2010?  that's what I am using for development now
0
 
khairilCommented:

You can navigate here, http://www.microsoft.com/visualstudio/en-us/try/test-professional-2010-tour

You need to have premium or ultimate or test edition of VS2010.

You might also like this (3 series articles), it is the step by step starting point, http://www.dotnetfunda.com/articles/article901-web-performance-test-using-visual-studio-part-i-.aspx
0
 
kevp75Author Commented:
I only have vs2010 pro
0
 
khairilCommented:
unfortunately it need the premium/ultimate/test edition.

you can download trial version from the microsoft site, else you can also try using other stress tools available over the net.
0
 
kevp75Author Commented:
this discussion has really gotten off the beaten path here.

the question is what is better and why...
0
 
khairilCommented:
Hi,

As I understand you are using XML to construct pages, which might includes XPATH and all other XML programming than just instead of plain data.

My personal experience long before, having data on plain XML did showed have some performance in small data set with no DBMS overhead. But when things get bigger I found it slows downed by size and it is hard for me to maintain plain files compare to single database. Of course the database not just the data file alone in my case - it comes with in memory, fetch foward, backup and replication as additional to hardware advantages

If you can ensure that your page will not grow big (big enough to make XML parser crawl), then you might consider to stay this way but it you consider for easy maintenance including backup and fail over and other DB features- RDBMS is the way to go instead.

If you ask me, taking into account .238s of XML compares to .363s of SQL CE and .266s of MS SQL - I will choose either SQL CE or MS SQL, it is just fraction of second that I willing to sacrifice for ease of maintenance. But if the site is important and mission critical for sure I will go for MS SQL.
0
 
kevp75Author Commented:
I agree.

I have just completed testing on a couple seperate sites.

What I found was on the site(s) that had less than 10 pages (irregardless of how much content was in it), it slowed down considerably with XML, while it stayed roughly the same with MS SQL Server. (SQL actually showed a slight increase in speed, ~.01ms)

Now, the SQL database is far more optimized and optimizable than XML is, for instance, I can use a stored procedure to call a query from a properly indexed view, and save ~.01ms

What I am finding now is, while I may want and decide to stay with MS SQL for this, I may find that it is too limiting...  for example, a windows host may not include a SQL database with their smallest hosting package.  In this instance, it may or may not be advisable for me as a developer and web development company to explain why they need to spend an extra $ a month for the better package, over the basic package due to the CMS...    hence why I was thinking XML would be the goto...

Now, in the same respects, I found that SQL CE performed worse than both in all instances...  yes, even worse than XML with a lot of data in it.

So, am I up a creak without the proverbial paddle?!?

Is there another free (or open source) embeddable solution that will give me the performance gains of MS SQL Server, whilst still being an embedable solution?

I have searched around, and the one that came to mind was FireBird.   I have tried VistaDB, and SQLLite, and both were significantly worse off than SQL CE were...
0
 
khairilCommented:
I cannot suggest you more than SQL CE in this case.

I'll have Postgre guys coming to my office next week, they claim that they can deliver performance, but I yet to try it

About hosting, there are a few that host Windows at very cheap price. I have subscribed to Webhostingworld.net for my .Net website with MS SQL and other one on PHP and MySQL. Frankly speaking it really hard to manage MS SQL databases. Most hosting companies usually used 3rd party/open source application to manage the MS SQL database like ASP.NET Enterprise Manager (AMM). For me, AMM nearly useless, under performance and hard to manage, it is far behind myPHPAdmin for MySQL, even further if you compare to MS SQL Enterprise Manager.... and you cannot manage your hosted MS SQL using MS SQL Manager from your computer. Futher more, you have no control over hardware usage of you MS SQL (unless you can go for server placement and limited control with VPS), which will hinder you to fine tune MS SQL beyond stored procedures.

If you can go for server placement, VPS hosting or get hosting company that offer remarkable management tools for MS SQL then choose that one. However, for most of these hosting company, you need to pay them thick bucks. That is my personal experience - hope you will find it usefull.
0
 
khairilCommented:
Additional,

Firebird also good choice, I have used it twice with my VB6 apps. No benchmarking done.

If you consider of using "Views" and "Stored Procedures" than Firebird have it, not in SQL CE. If you consider full data compatibility with enterprise MS SQL and full support from Visual Studio then SQL CE is the way to go.
0
 
kevp75Author Commented:
thanks.    I do already have a VPS (which I have complete control over...  i'm a shareholder in the company ;-)), so for my own clients I am not worried.

For other purposes (there may be instances where I can sell this product off to other developers/designers), I will need to highly consider an embedded solution.

I will be testing out Firebird over the next few days (possible) week.  and will come back with my findings...

thanks for the discussion thus far!   =)
0
 
khairilCommented:
Haaa... lucky you, I do wish I had VPS too, but currently it is hard for ROI.

Anyway nice talking to you too. Good luck then :)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now