Linq2SQL How to improve paging perfomance

У меня есть код, который с помощью библиотеки Linq2Rest и еще ряда условии осуществляет доступ к достаточно большой таблице данных. При этом доступ должен осуществляться с возможностью пэиджинга в стиле Take() и Skip(). 

К сожалению Linq2Sql при генерации запроса использует для организации Take\Skip механизм ROW_NUMBER что приводит к вот такому вот запросу:

SELECT * FROM ( SELECT *   FROM (

        SELECT ROW_NUMBER () OVER ( ORDER BY [t0] . [ID]) AS [ROW_NUMBER] , [t0] .*

        FROM [TABLE] AS [t0]

        ) AS [t1]

    WHERE [t1] .[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1

    ) AS [t2]

ORDER BY [t2]. [ROW_NUMBER]

Использование ROW_NUMBER приводит к существенному ухудшению производительности в зависимости от количества данных и номера страницы. В моем случае все было еще отягощено тем, что выборка осуществлялась по достаточно сложному SQL VIEW. 

Как я уже упоминал, условия фильтрации и выборки задавались в виде параметров URL в формате ODATA и разбирались c помощью Linq2Rest.

В общем виде код выглядит так:

         IEnumerable  GetData(NameValueCollection filterQuery, Expression <Func bool >> whereExp)

            where  T: class

        {

            using ( var db = new MyDB())

            {

                var table = db.GetTable();

                var filtered = table.Where(whereExp);

                var par = new ParameterParser ();

                var modelFilter = par.Parse(filterQuery);

                return modelFilter.Filter(filtered).Cast().ToList();

            }

        }

И поддерживает как фильтрацию так и сортировку через стандартные механизмы ODATA.

После ряда экспериментов с пэиджингом был найден вариант, который работал за весьма адекватное время и не зависел от номера страницы.

Вот такой SQL запрос:

DECLARE @temp table( [RN] int identity ( 1 ,1 ), [ID] int)

INSERT INTO @temp ( [ID])

    SELECT ID FROM [TABLE] AS [t0]

        ORDER BY   [t0]. [TIME] DESC

SELECT [t0] .* FROM [TABLE] AS [t0] WHERE ID in ( SELECT ID from @temp WHERE RN BETWEEN 68001 and 680100 )

ORDER BY [t0]. [TIME] DESC

  

Время запроса сократилось с 48 секунд до 3, что стало для меня победой.

Но оказалось что подсунуть этот запрос вместо того, что генерит Linq2Sql не просто.

Для начала я отключил генерацию Take и Skip в Linq2Rest, и тем самым получил от Linq2Sql полностью рабочий запрос со всеми условиями и сортировками, но без пэиджинга.

После этого по готовому выражению можно получить подготовленный Linq2SQL запрос и его параметры с помощью GetCommand. Изменить его и передать на выполнение ExecuteQuery.

Хитрость в том. что выражение полученное GetCommand не будет работать если его просто передать ExecuteQuery в случае если поля вашего объекта типа T имеют названия отличные от колонок базы. ExecuteQuery ожидает, что поля таблицы будут соответствовать тем именам, которые заданы атрибутом Column, а GetComand создает запрос вида

SELECT  [t0] .[MESSAGE_ID] AS [MessageId] FROM ...

В результате поля результирующего объекта просто, без всяких ошибок, будут иметь пустые значения.

При процессинге преобразовал запрос вот так:

         private string ModifySqlQuery( string sql, int skip, int top, string tablename)

        {

            var newSqlQuery = new StringBuilder ();

            newSqlQuery.AppendLine( "DECLARE @temp table([RN] int identity (1,1 ), [ID] int)");

            newSqlQuery.AppendLine( "INSERT INTO @temp ([ID])" );

            newSqlQuery.AppendFormat( "    SELECT ID FROM [ {0} ] AS [t0] ", tablename);

            var whereTerminator = string.Format( "FROM [ {0}] AS [t0]" , tablename);

            var wherePos = sql.IndexOf(whereTerminator, System.StringComparison .InvariantCultureIgnoreCase) + whereTerminator.Length;

            var orderPos = sql.IndexOf( "ORDER BY" , wherePos, System.StringComparison .InvariantCultureIgnoreCase);

            newSqlQuery.AppendLine(sql.Substring(wherePos));

            newSqlQuery.AppendFormat( "SELECT [t0].* FROM [ {0}] AS [t0] " +

                                     "WHERE ID in (SELECT ID from @temp WHERE RN BETWEEN {1} and {2} )", tablename, skip, skip + top);

            newSqlQuery.AppendLine();

            if (orderPos != -1)

                newSqlQuery.AppendLine(sql.Substring(orderPos));

            return newSqlQuery.ToString();

        }

Итого результирующий код получился таким:

         IEnumerable  GetData(NameValueCollection filterQuery, Expression <Func bool >> whereExp)

            where  T: class

        {

            using ( var db = new MyDB())

            {

                var table = db.GetTable();

                // remove original skip and top from expression

                int skip;

                if (! int.TryParse(filterQuery.Get( "$skip" ), out skip)) skip = 0;

                int top;

                if (! int.TryParse(filterQuery.Get( "$top" ), out top)) top = 100;

                filterQuery.Remove( "$skip" );

                filterQuery.Remove( "$top" );

                //Filter and sort all data

                var par = new ParameterParser ();

                var modelFilter = par.Parse(filterQuery);

                var filtred = modelFilter.Filter(table.Where(whereExp)).Cast();

                //Get SQL query

                var cmd = db.GetCommand(filtred);               

                var sql = cmd.CommandText;

                var sql_parms = ( from DbParameter param in cmd.Parameters selectparam.Value).ToArray();

                //Convert query

                var newSqlQuery = ModifySqlQuery(sql, skip, top, "TABLE" );

                var newfiltred = db.ExecuteQuery(newSqlQuery, sql_parms);

                return newfiltred.ToList();

            }

        }

Enjoy.