I meant to post this a while ago... after asking Frans to make the method virtual!

This code below is the best I could come up with given the restrictions on the ManglePageSelectDQ API and uses the new SQL Server 2005 RowNumber() funtion which eliminates the need for a temp table when using paging.

One issue with the code below is the fact that the new SQL Server 2005 paging pattern REQUIRES an order by clause. If one is not supplied the code below sorts the columns in the order they were supplied. Since most of the time the PK comes first in a given query, this will work. If your queries do not include the primary key first then you MUST supply an ISortExpression.

The other issue which is a major bottleneck is that currenly there is no easy way to separate the part of the query which identifies the set of rows for the page from the row data on that page.

Currently the generated query now looks like:

WITH __PageQuery__ AS (SELECT ROW_NUMBER() OVER (ORDER BY ...) AS __RowNumber__, <fields>
FROM <TableA>
INNER JOIN <TableB>
WHERE <Conditions>)
SELECT TOP <pageSize> <fields>
FROM __PageQuery__
WHERE __RowNumber__ BETWEEN @__rownoStart AND @__rownoEnd

But the following query (which I can't seem to automate in ManglePageSelectDQ) performs about 30x faster which is HUGH difference for large page numbers.

WITH __PageQuery__ AS (SELECT ROW_NUMBER() OVER (ORDER BY ...) AS __RowNumber__, < TableA ID fields ONLY>
FROM <TableA>
WHERE <Conditions>)
SELECT TOP <pageSize> <fields>
FROM __PageQuery__ 
INNER JOIN <TableB>
WHERE __RowNumber__ BETWEEN @__rownoStart AND @__rownoEnd

Obviously this example is one where the page rows are identified by the rows in TableA while the bulk of the data is found in TableB. But the idea is to minimise the data required while determining which rows are to be part of the page. The SQL Server optimizer does not always get this kind of situation right when the number of rows is large.

To use the code below simply implement your own DataAccessAdapter as follows:

    public class DataAccessAdapterEx : DataAccessAdapter
    {
        protected override DynamicQueryEngineBase CreateDynamicQueryEngine()
        {
            return new DynamicQueryEngineEx();
        }
    }

And create the following class...

public class DynamicQueryEngineEx : DynamicQueryEngine
{
    protected override void ManglePageSelectDQ(ref IRetrievalQuery selectQuery, IEntityFieldCore[] selectList, IFieldPersistenceInfo[] persistenceInfo, int pageNumber, int pageSize)
    {
        int selectColumnsStartIndex;
        if (selectQuery.Command.CommandText.StartsWith("SELECT DISTINCT"))
            selectColumnsStartIndex = 16;
        else
            selectColumnsStartIndex = 7;
    
        string queryText = selectQuery.Command.CommandText.Substring(selectColumnsStartIndex);

        if (pageNumber == 1)
        {
            // special case optimization, simply do a TOP pagesize query
            // mangle query so TOP is inserted. TOP isn't in the query at the moment.
            selectQuery.Command.CommandText = String.Format("SELECT DISTINCT TOP {0} {1} ", pageSize, queryText);
        }
        else
        {
            SqlParameter startNoParameter = new SqlParameter("@__rownoStart", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, string.Empty, DataRowVersion.Default, ((pageNumber - 1) * pageSize) + 1);
            SqlParameter endNoParameter = new SqlParameter("@__rownoEnd", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, string.Empty, DataRowVersion.Default, (pageNumber * pageSize));
            selectQuery.Parameters.Add(startNoParameter);
            selectQuery.Parameters.Add(endNoParameter);

            int orderByIndex = queryText.IndexOf("ORDER BY ");
            int fromIndex = queryText.IndexOf(" FROM ");

            string selectColumns = queryText.Substring(0, fromIndex);
            string orderByColumns;
            string unOrderedQueryText;
            if (orderByIndex == -1)
            {
                orderByColumns = "ORDER BY " + selectColumns;
                unOrderedQueryText = queryText;
            }
            else
            {
                orderByColumns = queryText.Substring(orderByIndex);
                unOrderedQueryText = queryText.Substring(0, orderByIndex);
            }               

            StringBuilder sb = new StringBuilder(1024);

            sb.AppendFormat("WITH __PageQuery__ AS (SELECT ROW_NUMBER() OVER ({0}) AS __RowNumber__, {1}) ", orderByColumns, unOrderedQueryText);
            sb.AppendFormat("SELECT TOP {0} ", pageSize);
            
            int numberOfFields = selectList.Length;
            int numberOfFieldsMinus1 = numberOfFields - 1;
            for(int i=0; i < numberOfFields;i++)
            {
                sb.Append(selectList[i].Alias);
                if (i < (numberOfFieldsMinus1))
                {
                    sb.Append(",");
                }                   
            }

            sb.AppendFormat(" FROM __PageQuery__ WHERE __RowNumber__ BETWEEN {0} AND {1}", startNoParameter.ParameterName, endNoParameter.ParameterName);
            
            // set new command text
            selectQuery.Command.CommandText = sb.ToString();
        }
    }
}

NOTE: This posting is provided "AS IS" with no warranties.

Cool

Thanks for this code, Marcus!!