Orckestra C1 - what is the tipping point to decide to use SQL?

Question

I have built and deployed Orckestra C1 sites and really loved the framework. The last site was kind of a hybrid of C1 XML for the content, and some SQL DB calls to get client data on some select pages. This worked perfectly. Site is fast and easy to maintain.

Now, I'm looking to use C1 to replace an old Asp built site, but it has a different set of data problems. Imagine a widget site with 1000 widgets where each item may have a relation to 20-30 other data points (joined tables). Along with the data, each widget might have 20-30 related images. Which could results in 20,000 to 30,000 images on the server. On a daily bases the site might get 600 unique visitors/day. There will be ~20 core pages, but some of those core pages will be dynamic content to display some variation of the widgets dynamically. From an admin side, yes they will be doing CRUD operations and other maintenance I would like to drive this through the C1 console or a custom admin UI.

My question: What is the tipping point to run the SQL migration tool to move from XML to SQL for Orckestra C1? We are trying to see if we really need to pay for SQL on Azure to estimate the monthly cost of running this site.

What are the best practices (case studies) that people have experience with C1 to make sure their site doesn't get bogged down with XML data files or images? Memory usage, disk IO, or CPU? Any bench marks published on scaled out C1 XML data files?

Thank you.

Answer 1

A thumb rule is that sites that has no

• significant amount continuous writes to datatypes or
• datastore/xml-files which are written to fairly often doesn't exceed 20-30 mb

can run on xml based datastore for as long as you want.

To rationalize these arguments one has to look at how xml datastore actually work in practice

• to query a datatype, the whole xml-file must be loaded in memory
• to add/delete/update a datatype instance, the whole file must be written again on disk
• you can't index particular fields to make certain queries you execute often on huge datasets run faster. Think of tablescan, just in memory.

So, the first point means that as long as you have enough memory you're fine. I've experienced 50% decrease in memory consumption by moving from xml to sql, so if memory is more expensive that a sql server, then switch.

The second point means that if you're doing any kind of tracking of visitors and behaviour to your datastore you'll have constant disk activity due to the whole xml-file being rewritten all the time. This can be overcomed by relying on dedicated services like KeenIO and similar to do the tracking instead of relying on C1 datatypes.

The third and last point touches on the same subject as the second; large datasets doesn't scale well on xml-datastore. If its a datatype which isn't updated often this can be overcomed by implementing own dedicated caches and lookup tables - think CQRS.

So to sum it up

• You can come along way implementing simple CQRS patterns over huge datasets which don't change often but are queried all the time. This can be stored in- memory or using something like Redis.
• Refactor parts that are constantly updated, like logging and tracking, into dedicated services which doesn't rely on C1 datatypes.
• With these small adjustments you can continue running on xml-store for a very long time, even for huge sites with a lot of content.