Tuesday, September 18, 2007

Dynamic ORDER BY Clauses for ASP.NET Server-Side Paging with Stored Procedures

My "compulsive obsession" to bring SQL Server 2005 stored procedures to the user-experience equivalent of dynamic T-SQL queries with a GridView bound to a LinqDataSource—regardless of the development effort required—continues.

When hydrating LINQ to SQL entities, I finally got server-side (incremental) paging to work properly with a LinqDataSource, bound DataGrid view and a stored procedure derived from a sample query. (See the introduction and "Attempts to Fix the Server-Side Paging Problems" sections of Problems Using Stored Procedures for LINQ to SQL Data Retrieval from 9/8/2007, updated 9/17/2007.) Here's a screen capture of the Orders test harness displaying 10 of the 122 Northwind Orders from customers in the USA:

Click capture to display full-size image in another window.

It was obvious that ad-hoc sorting wasn't behaving as expected for properties other than OrderID. For example, the preceding screen capture shows a descending sort on the Customer.CompanyName property. The sort's scope is the 10 rows displayed; the GridView sorts the rows. The same is true for ad-hoc sorts on all other properties except OrderID.

A quick review of a typical dynamic T-SQL query for page 2 with the simplest case, an ascending sort on the Freight property, showed the syntax required to increase the scope of the sort to all entities in the set:

exec sp_executesql N'SELECT TOP 10 [t2].[OrderID], [t2].[CustomerID],
    [t2].[EmployeeID], [t2].[OrderDate], [t2].[RequiredDate],
    [t2].[ShippedDate], [t2].[ShipVia], [t2].[Freight], [t2].[ShipName],
    [t2].[ShipAddress], [t2].[ShipCity], [t2].[ShipRegion],
    [t2].[ShipPostalCode], [t2].[ShipCountry]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Freight], [t0].[OrderID]
        DESC
) AS [ROW_NUMBER], [t0].[OrderID], [t0].[CustomerID],
        [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate],
        [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName],
        [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], 
        [t0].[ShipPostalCode], [t0].[ShipCountry]
    FROM [dbo].[Orders] AS [t0]
    LEFT OUTER JOIN [dbo].[Customers] AS [t1]
        ON [t1].[CustomerID] = [t0].[CustomerID]
    WHERE [t1].[Country] = @p0
    ) AS [t2]
WHERE [t2].[ROW_NUMBER] > @p1
ORDER BY [t2].[Freight], [t2].[OrderID] DESC',N'@p0 nvarchar(3),
    @p1 int',@p0=N'USA',@p1=10

The red ORDER BY expression for the ROW_NUMBER() function shows the syntax for creating the rowset from which page 2 (or greater) is obtained. Notice that both Freight and OrderID fields are included in the sort expression. The green expression is a holdover from the page 1 code and isn't required by the preceding example.

It's a better approach to use an explicit query than run sp_executesql to conditionally execute string-based queries from within the stored proc because of possible problems with caching the query execution plan and the brittleness of unchecked query syntax. 

Garth Wells' demonstrated how to write CAST expressions to customize ORDER BY expressions in his 2001 Dynamic ORDER BY article; a fix by "Wildthing" in the comments thread accommodates different datatypes. Here's the stored proc code to handle various single-parameter sorts:

CREATE PROCEDURE dbo.usp_GetOrdersByCountryPagedAndSorted(
    @Country nvarchar(15) = 'USA',
    @MaximumRows int = 20,
    @StartRowIndex int = 0,
    @SortCol nvarchar(128) = 'OrderID',
    @SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID,
    t2.OrderDate,     t2.RequiredDate, t2.ShippedDate, t2.ShipVia,
    t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
    t2.ShipPostalCode, t2.ShipCountry
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY
        CASE -- Numeric types ascending
            WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC'
                THEN t0.OrderID
            WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC'
                THEN t0.OrderID
            WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC'
                THEN t0.OrderID   
            WHEN @SortCol = 'Freight' AND @SortDir = 'ASC'
                THEN t0.Freight
            WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC'
                THEN t0.EmployeeID
            WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC'
                THEN t0.ShipVia
        END ASC,
        CASE -- Numeric types descending
            WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC'
               THEN t0.OrderID
            WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC'
               THEN t0.OrderID
            WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC'
               THEN t0.OrderID   
            WHEN @SortCol = 'Freight' AND @SortDir = 'DESC'
               THEN t0.Freight
        END DESC,
        CASE -- Character types ascending
            WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC'
                THEN t0.CustomerID
            WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC'
                THEN t0.ShipName
            WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC'
                THEN t0.CustomerID
        END ASC,
        CASE -- Character types descending
            WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC'
                THEN t0.CustomerID
            WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC'
                THEN t0.ShipName
            WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC'
                THEN t0.CustomerID
        END DESC,
        CASE -- Date types ascending
            WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC'
                THEN t0.OrderDate
            WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC'
                THEN t0.RequiredDate
            WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC'
                THEN t0.ShippedDate
        END ASC,
        CASE -- Date types ascending
            WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC'
                THEN t0.OrderDate
            WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC'
                THEN t0.RequiredDate
            WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC'
                THEN t0.ShippedDate
        END DESC
        ) AS ROW_NUMBER, t0.OrderID,
        t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate,
        t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress,
        t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
    FROM dbo.Orders AS t0
    LEFT OUTER JOIN dbo.Customers AS t1 ON t1.CustomerID = t0.CustomerID
    WHERE Country = @Country
    ) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
SET NOCOUNT OFF

Here's a screen capture of a later version of the test harness that sorts all columns with the preceding stored procedure. The ORDER BY clause is CustomerID DESC, OrderID DESC with many instances of ASC, ASC, ASC, ... DESC, DESC, DESC, which the query parser appears to ignore.

Click capture to display full-size image in another window.

The basic CAST approach gives the same sort sequence for CustomerID vs. Customer.CompanyName, but different sequences for EmployeeID vs. Employee.LastName and ShipVia vs. Shipper.CompanyName. Here's the modified stored proc code (red) to sort the entire data set on EntityRef values:

ALTER PROCEDURE dbo.usp_GetOrdersByCountryPagedAndSortedExt(
    @Country nvarchar(15) = 'USA',
    @MaximumRows int = 20,
    @StartRowIndex int = 0,
    @SortCol nvarchar(128) = 'OrderID',
    @SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID,
    t2.OrderDate,     t2.RequiredDate, t2.ShippedDate, t2.ShipVia,
    t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
    t2.ShipPostalCode, t2.ShipCountry
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY
        CASE -- Numeric types ascending
            WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC'
                THEN t0.OrderID
            WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC'
                THEN t0.OrderID
            WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC'
                THEN t0.OrderID   
            WHEN @SortCol = 'Freight' AND @SortDir = 'ASC'
                THEN t0.Freight
        END ASC,
        CASE -- Numeric types descending
            WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC'
               THEN t0.OrderID
            WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC'
               THEN t0.OrderID
            WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC'
               THEN t0.OrderID   
            WHEN @SortCol = 'Freight' AND @SortDir = 'DESC'
               THEN t0.Freight
        END DESC,
        CASE -- Character types ascending
            WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC'
                THEN t0.CustomerID
            WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC'
                THEN t0.ShipName
            WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC'
                THEN Customer.CompanyName
            WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC'
                THEN Employee.LastName
            WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC'
                THEN Shipper.CompanyName
        END ASC,
        CASE -- Character types descending
            WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC'
                THEN t0.CustomerID
            WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC'
                THEN t0.ShipName
            WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC'
                THEN Customer.CompanyName
            WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'DESC'
                THEN Employee.LastName
            WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'DESC'
                THEN Shipper.CompanyName
        END DESC,
        CASE -- Date types ascending
            WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC'
                THEN t0.OrderDate
            WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC'
                THEN t0.RequiredDate
            WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC'
                THEN t0.ShippedDate
        END ASC,
        CASE -- Date types ascending
            WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC'
                THEN t0.OrderDate
            WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC'
                THEN t0.RequiredDate
            WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC'
                THEN t0.ShippedDate
        END DESC
        ) AS ROW_NUMBER, t0.OrderID,
        t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate,
        t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress,
        t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
    FROM dbo.Orders AS t0
    LEFT OUTER JOIN dbo.Customers AS Customer
        ON Customer.CustomerID = t0.CustomerID
    LEFT OUTER JOIN dbo.Employees AS Employee
        ON Employee.EmployeeID = t0.EmployeeID
    LEFT OUTER JOIN dbo.Shippers AS Shipper
        ON Shipper.ShipperID = t0.ShipVia
    WHERE Customer.Country = @Country
    ) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
SET NOCOUNT OFF

Here's the test harness with the Employee column sorted ascending and an Ext. check box to select the extended sort capability:

Click capture to display full-size image in another window.

The preceding code doesn't attempt to sort multiple columns. It might be tempting to write a stored proc for two or three successive sort sequences with EXEC or sp_executesql and dynamic T-SQL. However, LINQ to SQL won't codegen function calls for stored procs that execute dynamic SQL. Also considering the security and query plan issues, it probably makes more sense to pass two or three pairs of @SortCol/@SortDir values and stack a set of CASE statements for each.

Update 9/19/2007: Minor edits and typos corrected .

5 comments:

Anonymous said...

Thanks for sharing the information.

However, I hope I never see such bloated SPROCs in my team - ever :-)

In my oppinion, this is a clear example of how *not* to use SPROCs with LINQ to SQL.

--rj said...

Anonymous,

How would you propose to provide ad-hoc sorts with server-side paging for ADO.NET?

Enable dynamic SQL with SELECT privileges for users?

--rj

Anonymous said...

Hey --rj,

This is a open for debate I guess, I like the solution, but I have to agree with Anonymous 1 on this one. In your instance users need Select priviliges anyway, to create a dynamic piece of SQL in a varchar(max) variable and then execute it will reduce compilation time and execution time heavily. I'm assuming you are not typing this piece of SP out anyway (that would just be insane if you have a really big enterprise like table with 50+ columns that all need to be ordered in a grid). There are two reason people try and stay away from dynamic SQL. One is in sql 6.5 and 7 SQL did not cache compiled dynamic queries (only SP's). This has long since changed and thus you no longer need to write SP's unless your architecture heavily relies on them (then for maintenance reasons only). The other reason is that it is contained in strings and you can make a syntax mistake that way that is hard to pickup. In your example the case statements are also strings so the same mistakes could be made, so this is also not valid. So for SQL I recommend building dynamic SQL. Overall I recomment using LINQ 2 SQL to automatically generate the optimal queries you need as linq will figure out in code what data to return and sort on and dynamically generate exaclty that query.

--rj said...

@Anon,

My experience is that LINQ to SQL queries become quite suboptimal as they increase in complexity. This is particularly the case with queries that involve associated entities in many:one and one:many relationships.

Microsoft is promoting Entity Framework to the exclusion of LINQ to SQL, so it's not likely that it will receive further updates.

--rj

johnny said...

Does anybody know why Microsoft hasn't come up with an eval() function in SQL server? It's a common method that almost every language has. This shouldn't be a problem in first place and yet it is. They should come up with a cleaner/painless way for us to do this before they come up with another version.