используя временную таблицу с NHibernate

Можете ли вы заменить функцию видом? Это представление может объединять 3 таблицы, отображать их с помощью NHibernate и легко выгружать / сортировать / фильтровать.

Я написал сообщение в блоге, в котором показано, как именно это можно сделать здесь: разбиение на страницы для запросов с высокой производительностью с использованием временных таблиц nhibernate и sql server

Я использую табличную переменную внутри запроса SQL Hibernate с использованием диалекта SQL 2012, как показано ниже. В моем сценарии я не мог заставить конкретный параметризованный запрос с разбивкой на страницы работать менее чем за 17 секунд, как бы я ни построил фильтр. При использовании табличной переменной время захвата страницы N с размером страницы 1000 привело к ответу менее секунды.

Вся магия происходит в SQL ниже,

1. создает табличную переменную - declare @temporderstatus table
2. выбирает строки в табличной переменной - insert into @temporderstatus фильтруется по sql в строке sqlfilter
3. выбирает данные из переменной таблицы для возврата в NHibernate, упорядоченный таким образом, чтобы разбивка на страницы возвращала предсказуемый набор результатов - select OrderNum, Customer... from @temporderstatus ORDER BY StatusCodeChangedDate, OrderNum
4. использует функции разбиения по страницам NHibernate для вставки необходимых операторов ROW / OFFSET с использованием диалекта SQL Server 2012 - SetFirstResult((pagination.Page - 1) * pagination.PageSize).SetMaxResults( pagination.PageSize )

В отличие от временной таблицы, табличная переменная очищается после себя, поэтому она идеально подходит для сценариев NHibernate, где у вас есть веб-сервер, обслуживающий запросы с разбивкой на страницы.

Вот мой код ...

            var session = _sessionManager.GetStatelessSession();
            StringBuilder sqlfilter = new StringBuilder(
                @"FROM Orders o join OrderType ot ON o.OrderType = ot.OrderType where o.StatusDate between :fromDate and :toDate" );
            var mainQuery = session.CreateSQLQuery(
                $@"declare @temporderstatus table (OrderNum int not null, CustomerID int,  OrderType varchar(16), Status varchar(3), StatusCodeChangedDate datetime, OrderDate datetime, DeliveryDate datetime)
      insert into @temporderstatus
      SELECT o.OrderNum, o.CustomerID, ot.Description AS OrderType, o.StatusCode AS Status, o.StatusCodeChangedDate, o.OrderDate, o.DeliveryDate 
      {sqlfilter}
      select OrderNum, CustomerID, OrderType, Status, StatusCodeChangedDate, OrderDate, DeliveryDate 
      from @temporderstatus  
      ORDER BY StatusCodeChangedDate, OrderNum 
      ");

       //construct the count query
       var totalCountQuery = session.CreateSQLQuery($"SELECT COUNT(1) OCount {sqlfilter} ");
            totalCountQuery.AddScalar("OCount", NHibernateUtil.Int32);
            totalCountQuery.SetParameter("fromDate", criteria.fromDate);
            totalCountQuery.SetParameter("toDate", criteria.toDate);
            var totalCountResults = totalCountQuery.UniqueResult<int>();
            pagination.TotalResultCount = totalCountResults;
            if (pagination.TotalResultCount == 0)
            {
                //no results so don't waste time doing another query
                return new List<OrderDataDto>();
            }

       //finish constructing the main query
            mainQuery.AddEntity(typeof(OrderDataDto));
            mainQuery.SetParameter("fromDate", criteria.fromDate);
            mainQuery.SetParameter("toDate", criteria.toDate);
            var mainQueryResults = mainQuery
                .SetFirstResult((pagination.Page - 1)*pagination.PageSize).SetMaxResults(pagination.PageSize);

            var results = mainQueryResults.List<OrderDataDto>();
            return results;