четверг, 10 октября 2013 г.

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 + 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 ,), [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. 

Комментариев нет: