Error with Providers MSSQL

Jan 5, 2008 at 2:12 AM
Inside ~/Providers/MSSQLProvider/Posts.cs you guys do not sort the Post list when you generate it.
/// <summary>
/// Retrieves all posts from the data store
/// </summary>
/// <returns>List of Posts</returns>
public override List<Post> FillPosts()
{
List<Post> posts = new List<Post>();

//for each entry found, cause a data load to occur - done this way so the reader is shut first!
foreach (Guid guid in this.GetGuids("SELECT PostID FROM be_Posts"))
posts.Add(Post.Load(guid));

posts.Sort();
return posts;
}

Calling posts.Sort() here sorts it Ascending by guid (and treats guid like a string).

Instead sorting by DateCreated Desc is more optimal (since that is the order that you would want it to appear on the front page).
/// <summary>
/// Retrieves all posts from the data store
/// </summary>
/// <returns>List of Posts</returns>
public override List<Post> FillPosts()
{
List<Post> posts = new List<Post>();

//for each entry found, cause a data load to occur - done this way so the reader is shut first!
foreach (Guid guid in this.GetGuids("SELECT PostID FROM be_Posts order by DateCreated Desc"))
posts.Add(Post.Load(guid));

// posts.Sort();
return posts;
}
Coordinator
Jan 5, 2008 at 3:27 AM
Edited Jan 5, 2008 at 11:47 PM
Actually, the Post.Sort() sorts by DateCreated and then by Title since the Post.CompareTo() has been overloaded to implement this sort order.

You are both right and wrong about the "DateCreated Desc" being "more optimal". Yes, if SQL returns it in the correct sort order, then the BE engine does not have to also sort it also. However, the cost is the same - you either sort in BE or SQL. Note, FillPosts() is only called once.

And actually, the FillPosts() method is horribly slow and inefficient for other reasons. Getting one post at a time has a lot of overhead (especially over a network). A better implementation would return all the fields in one SQL query to the server. However, we are thinking of a much, much better business layer <-> data provider layer interactions, which would only pull the data that's necessary from the database (or cache). And not keep the entire BE database in memory (at least for SQL providers). This is important when a single BE instance starts to supports multiple blogs. The XML provider would probably stay in memory (since XML is not a querable database). Again (as I mentioned in another post on the subject), we are waiting to see which direction the official blogengine team is headed. We don't want to get carried here (especially since this is an issue independent of our Photo Gallery extensions).
Jan 5, 2008 at 10:14 PM
Ya you are right, my bad. I was being hasty when I was looking at why the sort order was ascending and not descending for the front page. Would it make more sense to reverse the order for the date / time comparisson? because realistically when you sort the list of posts you want it in reverse order for the date time (ie; the newest to oldest).

Maybe something like this inside Utils.cs:
public static int CompareOrderAndTitle(int orderA, DateTime dateA, string stringA, int orderB, DateTime dateB, string stringB)
{
//-ve = a < b, 0 = a = b, +ve = a > b
//if orders match then check date, then the string
if (orderA == orderB)
{
if (DateTime.Compare(dateA,dateB) == 0)
{
return string.Compare(stringA, stringB);
}
else
{
return DateTime.Compare(dateA, dateB) * -1;
}
}
else
{
return orderA - orderB;
}

}

Just asking.
Coordinator
Jan 5, 2008 at 11:57 PM
Thanks; this was a bug. It'll be fixed shortly.