Have Code, Will Travel

November 20, 2009

Dynamic Search Objects Part 5—Sorting

Filed under: Dynamic SQL — Phil Sandler @ 2:23 pm
Tags:

In my last post, we added paging back to our original requirements, leaving only sorting left to implement.  So since we are covering that topic in today’s post, we can re-add it, which gets us back to all the functionality we had initially envisioned (with some minor clarifications to the original version):

Show all orders, filtering by an optional list of customers [using Company Name] and an optional list of employees who took the order and within an optional date range.  By default, the orders should be sorted by date [Ascending], then customer name [Ascending], but can be sorted by any single or pair [or any number] of columns in ascending or descending order.  The results should show a page of x records at a time, where x can be selected by the client.  The client should also be able to request and receive a specific page of data by entering the desired page number.

Like paging, sorting is built right in to the LLBLGen Dynamic List functionality.  So writing the lines of code that actually generate the ORDER BY clause is pretty straightforward.  However, figuring out how to fit a dynamically selected sort order into our framework presents a few design problems that we need to address.

Sorting is a PITA

As noted in Part 1, Dynamic SQL at least allows us to avoid one sorting challenge—using (or generating) a SQL CASE statement and casting the result to a common type.  But whether you are using static SQL (a stored procedure) or Dynamic SQL (as we are in this article), a common problem remains: providing the consumer the ability to effectively express the intended sort order without exposing the inner workings of the query.

Our query (and any query) returns a fixed set fields in the result set.  The client needs the ability to communicate to our service (via the DTO) the fields that should participate in the sort order, the rank of each field (first sort by fieldX, then by fieldY), and the operator to use for each (ASC or DESC).  So how do we express this in the DTO?  Remember that while the query service does (and must) have knowledge of the the DTO, the DTO has no knowledge of the query service, so there is no guarantee that the DTO’s fields match with the fields in the service. 

There are a number of ways we can handle this, but none of them are perfect.  Most of the time we end up simply managing sort fields by ordinal number.  So in our query, if a client wants to order the data by company name ascending, then order date descending, the code that sets values on the DTO will need to indicate that we want “field 3 ascending” then “field 1 descending” (3 and 1 are the ordinal numbers that represent the positions of company name and order date, respectively).  This convention needs to be well understood by the developer who is writing the code to add these values to the DTO, as it can get confusing when, for example, a grid in the UI puts fields in a different order than the way they come back in the result set.

Wait, Can’t We Just Sort on the Client Side?

Deferring sorting to the client side (e.g. jQuery, Dataset/Dataview sorting) may look tempting.  And it actually is a viable option for paging, sorting and filtering, if (and only if):

  1. the set of data is small, and
  2. there is little concern about performance and scalability

From my experience, the systems that meet this kind of criteria are few and far between.  Note that it’s not possible to mix and match sorting/paging in the application/database—if you ask the database for an unsorted 100 rows of data, then sort those rows on the application side, you’re likely not going to get the set of data you were expecting.  This may seem obvious, but I’ve seen the mistake made enough times that I wanted to at least mention it.

It is much more efficient to sort and filter in the database for a number of reasons.  For one thing, database engines are highly optimized for doing exactly the kind of work that sorting and filtering entail; this is what their underlying code was designed to do.  But even barring that—even assuming you could write code that could match the kind of sorting, filtering and paging performance of a mature database engine—it’s still preferable to do this work in the database, for the simple reason that it’s more efficient to limit the amount of data that is transferred from the database to the application server. 

So for example, if you have 10,000 rows before filtering, and your query pares that number down to 75, filtering on the client results in the data from all 10,000 rows being passed over the wire (and into your app server’s memory), where filtering on the database side would result in only the filtered 75 rows being moved between database and application.  This can make a huge impact on performance and scalability.

Sorting is a PITA 2: Revenge of the RDBMS

One last note about using sorting and paging in conjunction.  A query that implements paging can have odd results if the ORDER BY clause does not include a field that represents an empirical sequence in the data; sort order is not guaranteed beyond what is explicitly specified in the ORDER BY clause in most (maybe all) database engines.   An example: if you have 100 orders that all occurred on the exact same date, and you ask for the first page of this data sorted by this date, then ask for the second page of data sorted the same way, it is entirely possible that you will get some of the data duplicated across both pages.  So depending on the query and the distribution of data that is “sortable,” it can be a good practice to always include a unique field (like a primary key) as the final field in a sort clause if you are implementing paging.

Have I mentioned that sorting is a PITA?

Our Approach

Now that I’ve explained my views on Life, the Universe, and Sorting in 5000 words or less, let’s get to the ~20 lines of code that need to be added to implement sorting in our framework.

Let’s create an enum to represent sort direction (ascending or descending).  LLBLGen Pro actually provides a similar type out of the box, but for the purpose of this article, I don’t want knowledge of the LLBLGen framework exposed above the service layer (to be clear, this is not a restriction that I normally enforce):

public enum SortDirection
{
    Ascending,
    Descending
}

Next we need a class to represent the combination of (ordinal) field and the sort direction:

public class SortField
{
    private readonly int _fieldPosition;
    private readonly SortDirection _direction;

    public SortField(int fieldPosition, SortDirection op)
    {
        _fieldPosition = fieldPosition;
        _direction = op;
    }

    public SortDirection Operator
    {
        get { return _direction; }
    }

    public int FieldPosition
    {
        get { return _fieldPosition; }
    }
}

Since we will provide the ability to sort by multiple fields, we need a list of our SortField class on our DTO.  Since sorting will likely be supported on every implementation, we’ll add it to the base class.  I’m only showing code that changed in the base class here—grab the source code if you want to see it in context:

private readonly List<SortField> _sortFields = new List<SortField>();

public void AddSortField(SortField sort)
{
    _sortFields.Add(sort);
}

public List<SortField> SortFields
{
    get { return _sortFields; }
}

Our Dynamic List wrapper needs to change in a few ways.  We need to create a private method to transform our list of sort fields into a sort expression:

private SortExpression CreateSortExpression(List<SortField> sortFields)
{
    if (sortFields == null || sortFields.Count == 0) return null;

    var existingFields = new HashSet<int>();

    var sort = new SortExpression();
    foreach(SortField field in sortFields)
    {
        //this prevents the same field from being added twice
        if (existingFields.Contains(field.FieldPosition)) continue;
        
        existingFields.Add(field.FieldPosition);

        SortOperator op = field.Operator == SortDirection.Ascending ? SortOperator.Ascending : SortOperator.Descending;
        var sortClause = new SortClause(_fields[field.FieldPosition], null, op);
        sort.Add(sortClause);
    }

    return sort;
}

Now our main execute method needs to change to accept the list of sort fields, hand it to our new new method, and use the resulting sort expression in our database call:

public SearchQueryResultV2 ExecuteQuery(int pageNumber, int pageSize, List<SortField> sortFields)
{
    SortExpression sort = CreateSortExpression(sortFields);
    int dbCount;
    var dt = new DataTable();
    using (var adapter = new DataAccessAdapter())
    {
        //fetch the data table
        adapter.FetchTypedList(_fields, dt, _bucket, 0, sort, false, _groupBy, pageNumber, pageSize);
        //perform an additional query to get the total count of rows that matched
        dbCount = adapter.GetDbCount(_fields, _bucket, _groupBy, false);
    }

    return new SearchQueryResultV2(dt, dbCount, pageNumber, pageSize);
}

Finally, we need to implement the default sort order from our requirements if no order was specified, and call the new version of the ExecuteQuery method.  Note that we also add the PK to the sort order to ensure that our sequence is empirically maintained between pages of data (see above):

//handle default sort
if (param.SortFields.Count == 0)
{
    param.AddSortField(new SortField(1, SortDirection.Ascending));
    param.AddSortField(new SortField(3, SortDirection.Ascending));
}

//add PK to the end of the sort clause.
param.AddSortField(new SortField(0, SortDirection.Ascending));

return listManager.ExecuteQuery(param.PageNumber, param.PageSize, param.SortFields);

That should do it for sorting, and that should be all you need to get up and running with a single implementation of a Dynamic Search Object.  In my next post (which I promise will be brief!), I’ll talk a little bit about creating a clean service interface for situations when you have many implementations.

Source Code for Part 5

Shout it

About these ads

1 Comment »

  1. Hi! I personally like all your articles regarding llblgen. I hope you can give more examples in using llblgen in winforms in the future especially now that it’s on version 3.0. More power and goodluck!

    Comment by Aurel Cortez — December 28, 2010 @ 8:40 pm | Reply


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 262 other followers

%d bloggers like this: