We are running Umbraco 4.5.2 with SQL 2008 and .Net 3.5 under IIS 7.5. In a nutshell our architecture is to store content separate from "pages" (e.g. promotions or galleries, and then the pages point to which of those content elements they want to render via certain datatypes, etc.) and then we have a C# middle layer that takes that content and maps it to domain objects which our front-end code programs against. Therefore, our mapper layer does a lot of getProperty() calls, which I recognize can be slower. But we end up with a page object that has a promotion property on it which has a title property, etc.
We've used this approach successfully on many sites over the past year. However, our current project stores a bunch of listing content (e.g. hotels, stores, restaurants, etc.) -- about 700 of these records in total. For several weeks this worked fine but just yesterday the database started to get extremely slow. In running a trace on a very simple page that doesn't even handle these listing records, the profiler reported 325,381 rows and some of the queries reported over 2,000,000 reads.
I've looked through the forums and added some indexing but to no avail. Are there any other tips on how to speed this up? The database isn't particularly huge (about 60mb) and the generated umbraco.config is only 1.5mb. I can, and will, add caching of this content but it's still going to be excrutiatingly slow on inital read unless I can tune this a bit more.
Do you store your content in umbraco as well? Or do you use custom tables for that? Cause I don't see the need for using getProperty() when all your data is stored within umbraco? Mind sharing some more info? And why are you mapping umbraco content to domain objects? Did you have a look at linq2umbraco for that?
Without going into an in-depth description of our entire framework, or justification of our architecture, we have projects built on three different CMS products in our company. Therefore, we strive to make our front-end code CMS-agnostic. For example, if I have a user control that has a jquery carousel of promotional content, that ascx programs against a Promotion domain object, not xslt transformations of the umbraco.config file. I can plug that control into any of our projects and it always expects a Promotion object regardless of where that data comes from.
I am not using any custom tables. Everything is out of the box umbraco.
I looked into linq2umbraco but it didn't seem robust/mature enough for what we were looking for. Our objects are not always a 1:1 mapping of our document types and there's a fair amount of complexity/inheritance/etc., so we have a middle layer that retrieves the Umbraco Node object and maps it to our object. The code looks like this (very simplistic example):
var item = new Node(id);
var r = new Restaurant { Id = item.Id, Name = item.GetProperty("name").Value, SystemName = item.Name, ShortDescription = item.GetProperty("shortDescription").Value, Address1 = item.GetProperty("address1").Value, Address2 = item.GetProperty("address2").Value, City = item.GetProperty("city").Value, State = item.GetProperty("state").Value, Zip = item.GetProperty("zip").Value, Phone = item.GetProperty("phone").Value };
Front-end code can then look like this:
litRestaurantName.Text = r.Name;
And it has no clue that its data came from Umbraco.
I've always elected to use the Node object and call GetProperty because I'm a C# guy and find it much more straight-forward than calling GetXmlNodeById and traversing an XPathNodeIterator. However, if that's our only option to prevent multiple database calls (in the above example it would be 8 or 10?), I'll have to refactor our mapping layer to work this way.
But what I was hoping was for someone to suggest Umbraco database indexing best practices like Sitecore or others have?
Ok, understand a bit more on your setup, which seems perfectly valid.. so no bragging on that subject.
Looking at your code, I can see you're using the nodeFactory, so this won't do any calls against the database, only against the cached xml, so now I'm wondering as to where does sql calls come from?
Gotcha - I thought I had read somewhere that using GetProperty() would perform a database call each time but now I see that it should be running off of the cached XML data which is ideal for what we're doing. I will continue to debug and figure out where these database calls are coming from (again, we haven't customized the database at all).
Just to confirm, for umbraco 4.5.2 we should be using the umbraco.presentation.nodeFactory namespace when retrieving our Node objects, correct?
Yes correct, the Node class lives in the umbraco.presentation.nodeFactory namespace. In fact, there's no other Node class in any other umbraco assembly.
Good luck on the debugging and do not hesitate to ask questions if you still need help.
Any solutions to this, we also created a solution thats pretty heavy on using the nodefactory, and it (or something else) does indeed generate LOADS of database calls (recorded by the sql profiler)
One thing I found in my debugging was that
GetProperty("something").Value does not seem to hit the db but
retrieving Media objects makes multiple database calls. We added two
custom fields to the Image document type and now every time we retrieve
an image, and the values of those custom fields, it is making many
database calls to retrieve the definition of the field itself, its
version, etc, and ultimately its value. Since we were chaining objects
together, which all contained several image properties, etc., we were
hitting the database over and over. I modified some of that mapping
layer to only get certain fields for certain conditions and not do the
"deep load" which ultimately prevents this database traffic.
However, there are definitely other rogue database calls being made, I just haven't identified where they're coming from yet.
Bit of a delayed reply here, but I have only just found this thread and and I have also taken a smilar approach to you in terms of architecture and have been using this approach for a while, however I am also getting frustratingly long load times, but only with connections to a remote database. If I run everything locally inc databse it works like a dream. As some time has passed I wonder if you had made any further headway on this issue?
Database extremely slow
Hello,
We are running Umbraco 4.5.2 with SQL 2008 and .Net 3.5 under IIS 7.5. In a nutshell our architecture is to store content separate from "pages" (e.g. promotions or galleries, and then the pages point to which of those content elements they want to render via certain datatypes, etc.) and then we have a C# middle layer that takes that content and maps it to domain objects which our front-end code programs against. Therefore, our mapper layer does a lot of getProperty() calls, which I recognize can be slower. But we end up with a page object that has a promotion property on it which has a title property, etc.
We've used this approach successfully on many sites over the past year. However, our current project stores a bunch of listing content (e.g. hotels, stores, restaurants, etc.) -- about 700 of these records in total. For several weeks this worked fine but just yesterday the database started to get extremely slow. In running a trace on a very simple page that doesn't even handle these listing records, the profiler reported 325,381 rows and some of the queries reported over 2,000,000 reads.
I've looked through the forums and added some indexing but to no avail. Are there any other tips on how to speed this up? The database isn't particularly huge (about 60mb) and the generated umbraco.config is only 1.5mb. I can, and will, add caching of this content but it's still going to be excrutiatingly slow on inital read unless I can tune this a bit more.
Thoughts?
Do you store your content in umbraco as well? Or do you use custom tables for that? Cause I don't see the need for using getProperty() when all your data is stored within umbraco? Mind sharing some more info? And why are you mapping umbraco content to domain objects? Did you have a look at linq2umbraco for that?
Cheers,
/Dirk
Without going into an in-depth description of our entire framework, or justification of our architecture, we have projects built on three different CMS products in our company. Therefore, we strive to make our front-end code CMS-agnostic. For example, if I have a user control that has a jquery carousel of promotional content, that ascx programs against a Promotion domain object, not xslt transformations of the umbraco.config file. I can plug that control into any of our projects and it always expects a Promotion object regardless of where that data comes from.
I am not using any custom tables. Everything is out of the box umbraco.
I looked into linq2umbraco but it didn't seem robust/mature enough for what we were looking for. Our objects are not always a 1:1 mapping of our document types and there's a fair amount of complexity/inheritance/etc., so we have a middle layer that retrieves the Umbraco Node object and maps it to our object. The code looks like this (very simplistic example):
Front-end code can then look like this:
And it has no clue that its data came from Umbraco.
I've always elected to use the Node object and call GetProperty because I'm a C# guy and find it much more straight-forward than calling GetXmlNodeById and traversing an XPathNodeIterator. However, if that's our only option to prevent multiple database calls (in the above example it would be 8 or 10?), I'll have to refactor our mapping layer to work this way.
But what I was hoping was for someone to suggest Umbraco database indexing best practices like Sitecore or others have?
Ok, understand a bit more on your setup, which seems perfectly valid.. so no bragging on that subject.
Looking at your code, I can see you're using the nodeFactory, so this won't do any calls against the database, only against the cached xml, so now I'm wondering as to where does sql calls come from?
/Dirk
Gotcha - I thought I had read somewhere that using GetProperty() would perform a database call each time but now I see that it should be running off of the cached XML data which is ideal for what we're doing. I will continue to debug and figure out where these database calls are coming from (again, we haven't customized the database at all).
Just to confirm, for umbraco 4.5.2 we should be using the umbraco.presentation.nodeFactory namespace when retrieving our Node objects, correct?
thanks again,
Matt
Yes correct, the Node class lives in the umbraco.presentation.nodeFactory namespace. In fact, there's no other Node class in any other umbraco assembly.
Good luck on the debugging and do not hesitate to ask questions if you still need help.
Cheers,
/Dirk
Any solutions to this, we also created a solution thats pretty heavy on using the nodefactory, and it (or something else) does indeed generate LOADS of database calls (recorded by the sql profiler)
One thing I found in my debugging was that GetProperty("something").Value does not seem to hit the db but retrieving Media objects makes multiple database calls. We added two custom fields to the Image document type and now every time we retrieve an image, and the values of those custom fields, it is making many database calls to retrieve the definition of the field itself, its version, etc, and ultimately its value. Since we were chaining objects together, which all contained several image properties, etc., we were hitting the database over and over. I modified some of that mapping layer to only get certain fields for certain conditions and not do the "deep load" which ultimately prevents this database traffic.
However, there are definitely other rogue database calls being made, I just haven't identified where they're coming from yet.
Hi Matt
Bit of a delayed reply here, but I have only just found this thread and and I have also taken a smilar approach to you in terms of architecture and have been using this approach for a while, however I am also getting frustratingly long load times, but only with connections to a remote database. If I run everything locally inc databse it works like a dream. As some time has passed I wonder if you had made any further headway on this issue?
Cheers
Kev
is working on a reply...