Initial load too long with many artices using DB

Topics: ASP.NET 2.0, Business Logic Layer
Oct 3, 2011 at 2:58 PM

We are migrating several existing blog sites from drupal based blogs.  We have decided to use BlogEngine (v2.0).  It worked fine in inital conversions, but have run into issues with some of our older blogs.  This particular example has 2300 posts (not small, but not crazy), 1000 tags, 87 authors.  On starting the app, it takes almost 15 minutes for the app to load all the posts into the post "cache"- of course, this is not good.   We need to use SQL Server 2008 as our backend.  When initializing, there are 6 fetch calls taking place for each post.  Tried v2.5 and have the same issue.

Are there any theoretical limitations on number of posts?  Has anyone had success implementing a larger blog with SQL Server?



Oct 3, 2011 at 10:51 PM

There is no clear limit on how many posts you can handle as it depends on your server specs, size of the posts, IIS configuration and probably many other factors. But yes, we've got reports about similar behavior with very large number of posts.

Oct 4, 2011 at 12:04 PM

A couple of years ago, I switched a site over to BE (BlogEngine).  It had about 50,000 posts and was using a SQL 2008 DB.  If I recall correctly, the initial load took about 4 minutes.  This is a long time, although your 15 minutes is quite a bit longer than what I was seeing.  The 5 or 6 fetches per post was a contributing factor for the speed.

I modified the code at the time so it would only do 5 or 6 *total* DB queries -- one query for each table data is needed from.  Once the 6 queries ran, I sorted out the data there, but that proved to be much faster.  The 4 minutes went down to about 1 minute -- so a 400% gain.  In your case, if 2300 posts is taking 15 minutes, I'm thinking that if it's taking that much time because of high latency connecting to the DB, then reducing the total number of queries would make a big difference.

I should have probably integrated the modified code into the BE code repository so it would be available for everyone.  I still have it (I think) if you'd like it, but I think it was written for BE 1.5 so probably needs a few updates to get it up to BE 2.0 or BE 2.5.  I believe the code change was in FillPosts() within the DbBlogProvider.  That's the method that is responsible for the initial population of all the posts into cache.