У меня есть код, который с помощью библиотеки 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
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();
}
}
После ряда экспериментов с пэиджингом был найден вариант, который работал за весьма адекватное время и не зависел от номера страницы.
Вот такой 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.